Using SQL and SQL*Plus to Generate Scripts Automatically

One of the most powerful things a DBA can do with SQL*Plus is write a SQL script that generates other SQL scripts, letting you operate on large groups of objects at once instead of typing the same command over and over. Consider a database with a number of tables interrelated by foreign-key^[1] constraints. Before loading data into those tables, you want to temporarily disable every one of those constraints, which normally means running this command once per constraint:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

With a thousand tables and several constraints each, that is a lot of typing. Instead, let SQL generate the commands for you:

SET LINESIZE 130
SET PAGESIZE 0
SPOOL disable_all_constraints.sql
SELECT 'ALTER TABLE ' || table_name
       || ' DISABLE CONSTRAINT ' || constraint_name
       || ';'
FROM   user_constraints
WHERE  CONSTRAINT_TYPE = 'R';
SPOOL OFF

LINESIZE 130 keeps long table or constraint names from wrapping mid-statement. PAGESIZE 0 suppresses page and column titles entirely, so nothing but valid SQL ends up in the output file. The CONSTRAINT_TYPE = 'R' filter is worth understanding precisely: R specifically means referential integrity, the official term for a foreign-key constraint, distinct from the P, U, and C codes used for primary key, unique, and check constraints respectively. Run this, and you have a file named disable_all_constraints.sql containing every ALTER TABLE command needed to disable every foreign-key constraint in the schema. Run that file with the @ command, and every constraint is disabled. Ten thousand constraints would take the same nine lines. You are limited only by how precisely you can write the WHERE clause identifying the objects you actually want to affect.

The example above uses two settings, LINESIZE and PAGESIZE, but a genuinely clean generated script usually needs a fuller set to make sure nothing but valid SQL ends up in the output file:

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET TRIMOUT ON
SET VERIFY OFF
SET TERMOUT OFF

ECHO OFF and TERMOUT OFF keep the generation process quiet; recall that TERMOUT OFF specifically only works when run from a script, exactly as covered in the spooling lesson earlier in this module. FEEDBACK OFF and HEADING OFF strip out row-count messages and column headers that have no business in a file meant to be pure SQL. TRIMSPOOL and TRIMOUT remove trailing whitespace. LINESIZE set very wide, here 32767, a ceiling used elsewhere in SQL*Plus for similarly wide numeric limits, prevents a long generated statement from wrapping onto a second line and breaking the SQL syntax.

Your operating system likely has one or more text editors you can use to write scripts directly. You can run your operating system's default editor without ever leaving the SQL*Plus command line by entering the EDIT command.

You can control which editor EDIT actually launches using the SQL*Plus DEFINE command to set the variable _EDITOR. To make EDIT use vi, for example:

DEFINE _EDITOR = vi

Include this definition in your user or site profile so it is always set the moment you start SQL*Plus, rather than something you type fresh every session.

To create a script with a text editor, enter EDIT followed by the name of the file to create or edit:

EDIT SALES

EDIT appends the .SQL extension automatically unless you specify one yourself, and saving in the editor writes back into that same file. You can include multiple SQL commands and PL/SQL blocks in a single script this way; just remember a semicolon terminates each SQL command, and a slash on its own line follows each PL/SQL block.

The disable-constraints example above builds each generated statement by hand, concatenating literal text with column values. That works well for simple, predictable statements like ALTER TABLE ... DISABLE CONSTRAINT, but it becomes fragile fast for anything more complex, like reproducing a table's full CREATE TABLE statement with every column, constraint, and storage clause intact. For that, prefer Oracle's own DBMS_METADATA.GET_DDL package instead of hand-built strings:

SET LONG 1000000
SET LONGCHUNKSIZE 32767
SET PAGESIZE 0
SET LINESIZE 32767
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON

SPOOL emp_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM dual;
SPOOL OFF

The SET LONG and SET LONGCHUNKSIZE lines are not arbitrary padding here; they are genuinely necessary. GET_DDL returns its result as a LONG datatype, and without sufficiently large LONG and LONGCHUNKSIZE values, SQL*Plus truncates the output before you ever see the full statement. You can loop this across every table in a schema just as easily:

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner)
FROM   all_tables
WHERE  owner = 'HR';

Generated scripts frequently need to embed literal quote characters, for instance a CREATE USER statement with a quoted password, and doubling up single quotes gets unreadable fast in anything more complex. Oracle's alternative quoting mechanism solves this cleanly, letting you pick your own delimiter instead of a quote character:

q'!name LIKE '%DBMS_%'!'
q'<'So,' she said, 'It''s finished.'>'
q'{SELECT * FROM employees WHERE last_name = 'Smith';}'

Whatever character follows the q immediately after the opening quote becomes the delimiter for that literal, so you can choose one that does not appear inside the text you are quoting, avoiding awkward escaping entirely. This is genuinely useful the moment a generated script needs to build a string containing quotes of its own.

Recall from the substitution variables lesson earlier in this module that COLUMN's NEW_VALUE clause can capture a query result directly into a variable. That technique applies just as well here, letting a generator script name its own output file with a timestamp automatically:

COLUMN dcol NEW_VALUE fname NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') dcol FROM dual;
SPOOL gen_&fname..sql

Every time this generator runs, its output lands in a uniquely named file rather than overwriting the same filename each time, useful when you want a record of exactly what a generation run produced and when.

A full generate-then-run cycle can be driven entirely from the command line, outside an interactive SQL*Plus session, using the SILENT option:

sqlplus -S user/password@db @gen_grants.sql

The -S flag, uppercase, suppresses SQL*Plus's own prompts and banner entirely, letting it run invisibly inside a larger automated process, a shell script or a scheduled job, rather than expecting someone to watch it interactively. A generator script can even SPOOL a file that itself contains @ calls to other scripts, letting one generation run chain into several follow-on scripts automatically.

Between hand-built strings for simple, predictable statements, DBMS_METADATA.GET_DDL for faithful object definitions, the alternative quoting mechanism for anything containing literal quotes, and dynamic filenames for traceable output, you now have a genuinely complete toolkit for having SQL write SQL on your behalf, closing out this module's tour of what SQL*Plus can actually do beyond just running one query at a time.

[1]foreign-key: a field, or set of fields, in a table that references a record in another table. Foreign keys are often used in parent-child relationships; in an order-entry system, for example, line-item records would typically carry one or more fields, the foreign key, identifying the parent order record.