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.

My thanks to K. Vainstein for suggesting this technique of using PROMPT to remind script users of a script's arguments.

 

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.

Starting with a Clean Slate

Many settings affect SQL*Plus's operation. It's difficult to write a script that doesn't have any side effects and also difficult to ensure that a script is immune to some setting that you assume will be at its default. It would be nice if you could push the current state of SQL*Plus to a stack at the beginning of a script, issue a RESET command to place SQL*Plus in a known starting state, and then pop the original state back off the stack before exiting the script. Perhaps someday Oracle will provide this functionality.

Example 10-1 executes many SET and other commands to configure SQL*Plus to display information about the tables owned by a given user. The intent is to make every required setting explicit, i.e., to avoid any implicit reliance on a default setting that might not be in effect. The script attempts to undo all that work at the end, restoring the various settings back to their defaults. These two practices help minimize unwanted interactions between scripts run in the same interactive session.


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net