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.

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

EDIT SALES

[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.