2.2 How do I…Create and use SQL*Plus command files?

Problem

I know I can load a SQL statement into the SQL buffer from the disk using the GET command. I want to run a SQL*Plus command file from SQL*Plus without loading it into the SQL buffer first. This is important because my SQL*Plus command files contain formatting commands and multiple SQL statements. How do I run a SQL command file?

Technique

The START command executes a SQL*Plus command file. The START command can be substituted with the @ symbol, and the commands are interchangeable. When a START command is executed, each statement in the file is executed in order. Errors occurring in the file are displayed, and the file continues to run. SQL*Plus command files can be nested. START commands can be part of SQL*Plus command files to run the contents of other files. The technique of nesting command files can be used to set system variables as the result of queries.

Steps

1. Use CHP2_7.SQL as a SQL*Plus command file to create or replace the sample tables used throughout this chapter. The contents of the file show the structure of a SQL*Plus command file.

SET TERMOUT OFF

SPOOL CHP2_7.LOG

DROP TABLE DEPT2_1;

DROP TABLE EMP2_1;

CREATE TABLE DEPT2_1 (

DEPTNO NUMBER(6),

DNAME VARCHAR2(30));

CREATE TABLE EMP2_1 (

EMPNO NUMBER(6),

ENAME VARCHAR2(30),

SALARY NUMBER(12,2),

DEPTNO NUMBER(6));

INSERT INTO DEPT2_1

VALUES (1, ‘MARKETING’);

INSERT INTO DEPT2_1

VALUES (2, ‘SALES’);

INSERT INTO DEPT2_1

VALUES (3, ‘ACCOUNTING’);

INSERT INTO EMP2_1

VALUES (1, ‘SMITH, JOHN’, 24000,1);

INSERT INTO EMP2_1

VALUES (2, ‘JONES, MARY’, 42000,1);

INSERT INTO EMP2_1

VALUES (3, ‘BROWN, BILL’, 36000,2);

INSERT INTO EMP2_1

VALUES (4, ‘CONWAY, JIM’, 52000,3);

INSERT INTO EMP2_1

VALUES (5, ‘HENRY, JOHN’, 22000,3);

INSERT INTO EMP2_1

VALUES (6, ‘SMITH, GARY’, 43000,2);

INSERT INTO EMP2_1

VALUES (7, ‘BLACK, WILMA’, 44000,2);

INSERT INTO EMP2_1

VALUES (8, ‘GREEN, JOHN’, 33000,2);

INSERT INTO EMP2_1

VALUES (9, ‘JONHSON, MARY’, 55000,3);

INSERT INTO EMP2_1

VALUES (10, ‘KELLY, JOHN’, 20000,2);

COMMIT;

SPOOL OFF

SET TERMOUT ON

The first command is a SQL*Plus command that suppresses the output of the statements when a script runs using the START command. The second line writes the output generated by the statements to the file specified with the SPOOL command. The two DROP TABLE statements remove the sample tables if they already exist. The CREATE TABLE statements build the sample tables, and the INSERT statements add data to the tables. For more information on creating and modifying tables, see Chapter 4, “Tables.” The COMMIT statement saves the transactions to the database. The SPOOL OFF statement stops the writing of the file, and the final statement resets the TERMOUT system variable to ON.

How It Works

The START command substituted with the @ symbol runs a file that can contain SQL, PL/SQL, and SQL*Plus commands. The statements and commands in the file are executed by SQL*Plus as if they were typed into SQL*Plus in order. If you were to take a SQL*Plus command file and enter each line, it would behave exactly as if running the file. The command file CHP2_7.SQL contains SQL statements and SQL*Plus commands. The first line writes the output of the script to file CHP2_7.LOG. The next two lines remove the sample tables, if they exist. If they don’t exist, errors will appear; however, you can ignore them. The two CREATE TABLE statements create two sample tables. The INSERT statements put sample data into the tables. The COMMIT statement saves the transactions to the database, and the last command closes the output file.

Comments

When you work with long SQL*Plus scripts, it is often easier to develop the script using a text editor such as Windows Notepad and run it with the START command. Although SQL*Plus has text editing capabilities, they can be difficult to use when working with large statements. If you run CHP2_7.SQL, you must be connected to an Oracle database and have privileges to create tables. If you are not sure what database access you have within your organization, contact your database administrator.