Using SQLPLUS to generate scripts…

Tired of updating scripts by searching and replacing until your fingers fall off? Have a SQL statement you have to run against every table in your schema?

Next to Regular Expressions, this is one of the most useful tools in my arsenal.

Common uses for this are to write SQL statements that can work from a list of objects in the database:

• Tables from USER_TABLES or DBA_TABLES
• Constraints from USER_CONSTRAINTS or DBA_CONSTRAINTS
• Indexes from USER_INDEXES or DBA_INDEXES
• USER_OBJECTS with an appropriate filter on OBJECT_TYPE (TABLE, PACKAGE, SEQUENCE, etc…)

Here is how to set up SQLPLUS script to generate a spool file that contains a list of generated SQL Statements:

1) Create a SQL scripts called genscript.sql with the following contents:

SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 32000
SET LONG 32000
SET LONGCHUNKSIZE 32000
SET PAGESIZE 0
SET SPACE 0
SET TERMOUT OFF
SET TRIMSPOOL ON
SPOOL c:\spool.sql
SELECT 'SELECT * FROM '||TABLE_NAME||' WHERE ROWNUM = 5;

2) Run the script:

SQLPLUS user/password@instance @genscript.sql

3) View your results:

EDIT c:\spool.sql

4) Run it!

SQL> @spool.sql

– Dave