RelationalDBDesign RelationalDBDesign


SQL* Plus CLI  «Prev  Next»
Lesson 17 Using SQL to write SQL
Objective Use SQL and SQL*Plus together to automatically generate scripts.

Generating scripts in Oracle

One of the most powerful things a DBA can do with SQL*Plus is to write SQL scripts that generate other SQL scripts and thus allow you to operate on large groups of objects at once. To illustrate, let'ssay you have a number of tables that are interrelated using foreign-key[1] constraints You need to load data into those tables, and you want to temporarily disable all the constraints until you are done. What you need to do is execute this command for each foreign-key constraint:

ALTER TABLE table_name DISABLE CONSTRAINT
constraint_name;

If you had 1,000 tables with several constraints each, you would have a lot of typing to do. Instead, you can make things easy by executing the following commands:

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

Setting the LINESIZE to 130 ensures that long table or constraint names won't cause the output to wrap. Setting the PAGESIZE to zero inhibits the display of any page or column titles. After executing these commands, you will have a file named disable_all_constraints.sql that contains all the ALTER TABLE commands necessary to disable all your foreign-key constraints. Then you just execute that file (using the @ command), and you are have disabled all the constraints. You could have 10,000 constraints to disable, and you could do it all with the nine lines shown here.
You can see how powerful this SQL technique is. You are limited only by your ability to write a WHERE clause that identifies the group of objects you want to affect.

Writing Scripts with a System Editor

Your operating system may have one or more text editors that you can use to write scripts. You can run your operating system's default text editor without leaving the SQL*Plus command-line by entering the EDIT command.
You can use the SQL*Plus DEFINE command to define the variable, _EDITOR, to hold the name of your preferred text editor. For example, to define the editor used by EDIT to be vi, enter the following command:

DEFINE _EDITOR = vi

You can include an editor definition in your user or site profile so that it is always enabled when you start SQL*Plus.
To create a script with a text editor, enter EDIT followed by the name of the file to edit or create, for example:
EDIT SALES

EDIT adds the filename extension. SQL to the name unless you specify the file extension. When you save the script with the text editor, it is saved back into the same file. EDIT lets you create or modify scripts.
You must include a semicolon at the end of each SQL command and a slash (/) on a line by itself after each PL/SQL block in the file. You can include multiple SQL commands and PL/SQL blocks in a script.
[1]foreign-key: A field, or a set of fields, in a table that reference 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 have one or more fields (the foreign-key) that identified the parent order record.