Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Scripting the Data Dictionary

You can write scripts to remove some of the burden of writing queries against the data dictionary. Example 10-14 shows one way you might go about writing such a script, by presenting one that lists all the indexes on a table. Don't take in the entire script now. Glance over it to get the gist of how it's put together. Then read the sections that follow; they explain the more significant parts of the script in detail.

Example 10-14. A script to list all indexes on a given table

SET ECHO OFF --DESCRIPTION --Displays information about an index. The index name --is passed as a parameter to this script. --Remind the user of what the first argument should be. --If the user forgot to specify the argument, he/she will --be prompted for it when the first occurrence of &&1 is encountered. PROMPT Argument 1 - Table name in [owner.]table_name format PROMPT Describing indexes on table &&1 SET RECSEP OFF SET NEWPAGE NONE SET VERIFY OFF SET PAGESIZE 9999 SET HEADING OFF SET LINESIZE 80 SET FEEDBACK OFF CLEAR COMPUTES CLEAR COLUMNS CLEAR BREAKS --Turn off terminal output to avoid spurious blank lines --caused by the SELECT that is done only to load the --substitution variables. SET TERMOUT OFF --Dissect the input argument, and get the owner name and --table name into two, separate substitution variables. --The owner name defaults to the current user. DEFINE s_owner_name = ' ' DEFINE s_table_name = ' ' COLUMN owner_name NOPRINT NEW_VALUE s_owner_name COLUMN table_name ...