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

10.10 Using SQL to Write SQL

One of the most interesting and useful things you can do using the
data dictionary is to automate the generation of SQL statements in a
technique often referred to as using SQL to write
SQL . (We looked briefly at this technique in Chapter 9.) The gist of this technique is to use
expressions involving data dictionary columns to build syntactically
valid SQL statements that you can, in turn, execute to perform
various database maintenance tasks.

A common problem that I encountered on one particular project I
worked on was the need to move schemas from one database to another.
Moving an actual schema is easy enough. You can do it by exporting
that schema from your source database and then importing it into your
target database. This is easy to do using Oracle's
export and import utilities. What made this simple task into a
challenge was that I needed to re-create, on the target database, any
public synonyms that referred to objects in the schema I was moving.
Because they were public, these synonyms were, of course, not part of
the schema and were not exported along with all the objects that
were part of the schema.

After scratching my head for a bit, I came up with a script similar
to the one in Example 10-15. This script prompts for a
schema name and queries dba_synonyms for a list of
synonyms referencing objects in that schema. Rather than return a
simple list, the script uses the following expression to return a
list of CREATE PUBLIC SYNONYM commands:

'CREATE PUBLIC SYNONYM '
|| synonym_name
|| ' FOR '
|| table_owner || '.' || table_name
|| ';'

Example 10-15. A script to export public synonyms

SET ECHO OFF
--
--Creates a file of "create synonym" commands for each
--synonym referencing a table
--in the schema specified by the user running this script.
--
SET VERIFY OFF
--so user doesn't see feedback about the number of rows selected.
SET FEEDBACK OFF
--Tell the user what we are going to do, and prompt for
--the necessary values.
PROMPT
PROMPT
PROMPT This script allows you to build a SQL*Plus script file
PROMPT which will recreate all PUBLIC synonyms referencing
PROMPT objects in a specified schema.
PROMPT
PROMPT To abort execution, press ctrl-C.
PROMPT
ACCEPT SynRefsOwner CHAR PROMPT 'Schema >'
ACCEPT SynScriptFileName CHAR PROMPT 'Output File >'
--Build the script file with the requested "create synonym" commands.
--First set session settings so the output looks nice.
SET LINESIZE 132
SET PAGESIZE 0
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
--Spool the output the file requested by the user.
SPOOL &SynScriptFileName
SELECT 'CREATE PUBLIC SYNONYM '
|| synonym_name
|| ' FOR '
|| table_owner || '.' || table_name
|| ';'
FROM dba_synonyms
WHERE table_owner = UPPER('&SynRefsOwner')
AND owner = 'PUBLIC'
UNION
SELECT '--No public synonyms were found referencing the schema '''
|| UPPER('&SynRefsOwner')
|| '''.'
FROM dual
WHERE NOT EXISTS (
SELECT *
FROM dba_synonyms
WHERE table_owner = UPPER('&SynRefsOwner')
AND owner = 'PUBLIC'
);
--Turn spooling off to close the file.
SPOOL OFF
--Reset session settings back to their defaults.
SET VERIFY ON
SET FEEDBACK 6
SET LINESIZE 80
SET TERMOUT ON
SET PAGESIZE 24
SET TRIMSPOOL OFF
SET TRIMOUT ON Rather than display these commands on the screen where they will do
you no good, the script spools them to a file of your choice. You can
take that file to another database, execute using the @ command, and
re-create all the synonyms.

The following example runs the script from Example 10-15 to export public synonyms referring to objects
owned by SYSTEM:

SQL> @ex10-15
This script allows you to build a SQL*Plus script file
which will recreate all PUBLIC synonyms referencing
objects in a specified schema.
To abort execution, press ctrl-C.
Schema >system
Output File >syn.sql
SQL>

And here's the resulting file:

oracle@gennick02:~/sqlplus/ExampleScripts> cat syn.sql
CREATE PUBLIC SYNONYM OL$ FOR SYSTEM.OL$;
CREATE PUBLIC SYNONYM OL$HINTS FOR SYSTEM.OL$HINTS;
CREATE PUBLIC SYNONYM OL$NODES FOR SYSTEM.OL$NODES;
. . .

Most of the SET commands in Example 10-15 are there to
prevent any extraneous information, such as column headings or page
titles, from being written to the spool file. The real work is done
by the SPOOL and SELECT commands. PAGESIZE is set to zero to inhibit
pagination, and LINESIZE is made wide enough to accommodate long
synonym names. SET TRIMSPOOL and SET TRIMOUT prevent trailing spaces
from being written to the lines in the spool files.

For another look at how you can leverage the data dictionary to manage database objects, read Managing Database Objects in Groups at http://gennick.com/rebuild_indexes_articlel.

Using SQL to write SQL is handy. It provides a tremendous amount of
leverage because rather than work with database objects one at a
time, you can manipulate whole classes, or sets, of database objects
at once.