Oracle Database Administration: The Essential Refe

SQL from SQL

One of the most powerful features of SQL*Plus is its ability to use SQL statements to create a new set of SQL statements, which in turn yield an interesting or useful result. For example, as the DBA, you might want to drop a set of tables from a particular user’s schema. You could, of course, list all the tables and then issue a DROP TABLE command for each, but it would be much easier to write a SQL script (a collection of SQL*Plus, SQL, and/or PL/SQL statements) to do it. Here is a simple script that will, in turn, generate and run a script to drop all of SCOTT’s tables:

set verify off
set pagesize 0
set termout off
set feedback off
set sqlprompt ''
spool dropem.sql
SELECT 'DROP TABLE',owner||'.'||table_name,';'
FROM dba.tables
WHERE owner='SCOTT'
/
spool off
set verify on
set feedback on
start dropem.sql
exit

When the script listed above is run, it will create an output file called dropem.sql. That file will contain the following:

DROP TABLE SCOTT.BONUS;
DROP TABLE SCOTT.DEPT;
DROP TABLE SCOTT.EMP;
DROP TABLE SCOTT.SALGRADE;

Tip

This technique of generating SQL from SQL only works when the script is placed in a file and run using the START or @ command. The script will not work properly if it is executed interactively from the terminal.

As you can see, the first script generated a set of SQL statements that dropped all of SCOTT’s tables. This script may be made more general, so that all tables belonging to any owner can be dropped by using a SQL*Plus substitution variable, ...