Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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