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:
4) Run it!