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_article.html.

 

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.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



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

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