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 NOPRINT NEW_VALUE s_table_name SELECT DECODE(INSTR('&&1','.'), 0,USER, /*Default to current user.*/ UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name, DECODE(INSTR('&&1','.'), 0,UPPER('&&1'), /*Only the table name was passed in.*/ UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name FROM dual; SET TERMOUT ON --The following variables receive information about each index DEFINE s_index_owner = ' ' DEFINE s_index_name = ' ' DEFINE s_index_type = ' ' DEFINE s_uniqueness = ' ' DEFINE s_tablespace_name = ' ' --Place new, index-related values into the above substitution variables COLUMN owner NOPRINT NEW_VALUE s_index_owner COLUMN table_name NOPRINT NEW_VALUE s_table_name COLUMN index_name NOPRINT NEW_VALUE s_index_name COLUMN index_type NOPRINT NEW_VALUE s_index_type COLUMN uniqueness NOPRINT NEW_VALUE s_uniqueness COLUMN tablespace_name NOPRINT NEW_VALUE s_tablespace_name --Format the two columns that we'll actually display from the query COLUMN indent FORMAT A19 COLUMN column_name FORMAT A30 --Skip a page for each new index BREAK ON owner ON index_name SKIP PAGE --Information about the index as a whole is printed in --the page title. TTITLE SKIP 1 LEFT 'INDEX ' s_index_owner "." s_index_name - ' ' s_index_type ' ' s_uniqueness SKIP 1 - 'DEFINED ON TABLE ' s_owner_name "." s_table_name SKIP 1 - 'STORED IN TABLESPACE ' s_tablespace_name SKIP 1 - 'CONTAINING COLUMNS: ' --List the columns that make up the index. --The indent column moves the column list over to the --right so that it comes after the 'CONTAINING COLUMNS:' --portion of the header. SELECT ai.owner, ai.index_name, ai.index_type, ai.uniqueness, ai.tablespace_name, ' ' indent, aic.column_name FROM all_indexes ai JOIN all_ind_columns aic ON ai.owner = aic.index_owner AND ai.index_name = aic.index_name WHERE ai.table_owner = '&&s_owner_name' AND ai.table_name = '&&s_table_name' ORDER BY ai.owner, ai.index_name, aic.column_position; --Change all settings back to defaults CLEAR COLUMNS CLEAR BREAKS SET PAGESIZE 14 SET HEADING ON SET NEWPAGE 1 SET FEEDBACK ON UNDEFINE 1

10.9.1 Running the Script

Run the script in Example 10-14 as follows , by passing a table name as a command-line argument:

SQL> @ex10-14 employee Argument 1 - Table name in [owner.]table_name format Describing indexes on table gennick.employee INDEX GENNICK.EMPLOYEE_PK NORMAL UNIQUE DEFINED ON TABLE GENNICK.EMPLOYEE STORED IN TABLESPACE USERS CONTAINING COLUMNS: EMPLOYEE_ID INDEX GENNICK.EMPLOYEE_BY_NAME NORMAL NONUNIQUE DEFINED ON TABLE GENNICK.EMPLOYEE STORED IN TABLESPACE USERS CONTAINING COLUMNS: EMPLOYEE_NAME

As you can see, the script displays information about the two indexes on employee . Both indexes consist of a single column each. The unique index happens to be the primary key index, on the employee_id column. The other index is on employee_name .

10.9.2 When the Parameter Is Omitted

Example 10-14 begins with an interesting bit of script that serves to remind you of what the command-line parameter should be:

--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

This reminder prompt is useful because if you forget to pass the table name as a parameter, SQL*Plus will prompt you when the parameter reference is first encountered in the script. However, parameters are referenced by numerical position, using substitution variable names such as 1 , 2 , etc. SQL*Plus's default prompt, when you omit the command-line argument, will ask you to enter a value for 1 , which doesn't help:

SQL> @ex10-14 Argument 1 - Table name in [owner.]table_name format Enter value for 1: employee Describing indexes on table employee . . .

Although the default prompt isn't helpful in this case, the output from the PROMPT command will serve to remind you of what 1 is supposed to be. This is a handy technique to use in scripts that accept parameters, especially when you plan to run those scripts interactively from the SQL*Plus command line.

10.9.3 Separating Owner and Table Names

The next significant part of Example 10-14 is a set of COLUMN commands along with a SELECT statement that serve to separate a parameter in owner . table_name format into two separate values:

COLUMN owner_name NOPRINT NEW_VALUE s_owner_name COLUMN table_name NOPRINT NEW_VALUE s_table_name SELECT DECODE(INSTR('&&1','.'), 0,USER, /*Default to current user.*/ UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name, DECODE(INSTR('&&1','.'), 0,UPPER('&&1'), /*Only the table name was passed in.*/ UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name FROM dual;

The DECODE function calls are what makes this SELECT flexible enough to deal with whether you choose to specify a username in response to the prompt. The first DECODE function call returns the appropriate owner name. You can interpret the arguments to this DECODE call as follows:

INSTR('&&1','.')

Looks for the character position of the period in the parameter passed to the script. If there is no period, INSTR returns zero. The result of INSTR is argument #1 to the DECODE function.

0,USER

If argument #1 is zero, meaning that no owner name was given, default to the currently logged in user's name.

UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))

Otherwise, return everything up to, but not including, the period as the owner name.

The second DECODE function implements similar logic, but this time to return the table name:

INSTR('&&1','.')

Again, looks for the period in the parameter, returning zero if one is not found.

0,UPPER('&&1'), /*Only the table name was passed in.*/

If argument #1 is zero, return the entire command-line parameter as the table name.

UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))

Otherwise, return the characters following the period as the table name.

The SELECT described in this section is done with TERMOUT off so you aren't bothered by the output from this SELECT against dual when you run the script.

10.9.4 Generating the Index Headings

Example 10-14 describes each index using a two-part structure. The first part, the index header , displays information about each index as a whole. The second part lists the columns in the index. The following are the header and column for employee 's primary key index:

INDEX GENNICK.EMPLOYEE_PK NORMAL UNIQUE DEFINED ON TABLE GENNICK.EMPLOYEE STORED IN TABLESPACE USERS CONTAINING COLUMNS: EMPLOYEE_ID

The reason for this two-part approach is that all the information couldn't possibly fit on a single line. To generate a header for each index, Example 10-14 takes advantage of SQL*Plus's pagination capabilities.

The SELECT statement joins all_indexes with all_ind_columns , and retrieves columns from both views. The following COLUMN commands cause SQL*Plus to continually (for each row retrieved) update a set of substitution variables with information from all_indexes .

COLUMN owner NOPRINT NEW_VALUE s_index_owner COLUMN table_name NOPRINT NEW_VALUE s_table_name COLUMN index_name NOPRINT NEW_VALUE s_index_name COLUMN index_type NOPRINT NEW_VALUE s_index_type COLUMN uniqueness NOPRINT NEW_VALUE s_uniqueness COLUMN tablespace_name NOPRINT NEW_VALUE s_tablespace_name

This information from all_indexes needs to be displayed only once. To that end, Example 10-14s TTITLE command references the substitution variables:

TTITLE SKIP 1 LEFT 'INDEX ' s_index_owner "." s_index_name - ' ' s_index_type ' ' s_uniqueness SKIP 1 - 'DEFINED ON TABLE ' s_owner_name "." s_table_name SKIP 1 - 'STORED IN TABLESPACE ' s_tablespace_name SKIP 1 - 'CONTAINING COLUMNS: '

The following BREAK command generates a page break each time a new index is encountered in the query's result set:

BREAK ON owner ON index_name SKIP PAGE

The result set is sorted by index and within index by column. The column name is the only value that SQL*Plus displays for each row (aside from some spaces for indention). As SQL*Plus lists the column names, every new combination of owner and index name forces a page break. For each new page, the page title prints, displaying the current values of the s_ substitution variables. SET HEADING OFF prevents any column headings from displaying.