Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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