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.

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.