Executing a Script

Most of this chapter has focused on what you need to know to enter a command directly into SQL*Plus and have it executed. Another option available to you is to have SQL*Plus execute a script , which is simply a text file that contains one or more statements to execute. When SQL*Plus executes a script, the commands or statements in the file are executed just as if you had typed them in directly from the keyboard. A script file can contain any combination of valid SQL*Plus commands, SQL statements, or PL/SQL blocks.

Let's say you have a file named ex2-17.sql , and it contains the following SQL*Plus commands:

SET ECHO ON DESCRIBE employee DESCRIBE project DESCRIBE project_hours

You can execute this file using the @ command, as shown in Example 2-17. Type an @ character, follow it by the path to the script you wish to execute, and press Enter.

Example 2-17. Executing a SQL*Plus script

SQL> @$HOME/sqlplus/ExampleScripts/ex2-17 SQL> DESCRIBE employee Name Null? Type ----------------------------------------- -------- ---------------- EMPLOYEE_ID NOT NULL NUMBER EMPLOYEE_NAME VARCHAR2(40) EMPLOYEE_HIRE_DATE DATE EMPLOYEE_TERMINATION_DATE DATE EMPLOYEE_BILLING_RATE NUMBER(5,2) SQL> DESCRIBE project Name Null? Type ----------------------------------------- -------- ---------------- PROJECT_ID NOT NULL NUMBER(4) PROJECT_NAME VARCHAR2(40) PROJECT_BUDGET NUMBER(9,2) SQL> DESCRIBE project_hours Name Null? Type ----------------------------------------- -------- ---------------- PROJECT_ID NOT NULL NUMBER(4) EMPLOYEE_ID NOT NULL NUMBER TIME_LOG_DATE NOT NULL DATE HOURS_LOGGED NUMBER(5,2) DOLLARS_CHARGED NUMBER(8,2)

The @ command in Example 2-17 specifies the full path to the script. If the script happens to be in your current working directory, you can omit the path. By default, SQL*Plus doesn't display commands, statements, and blocks as it executes them from the script. The SET ECHO ON command in ex2-17.sql changes this behavior and is the reason why you see the three DESCRIBE commands in the output from the script. Otherwise, you'd see only the output from those commands.

You can do a lot with scripts. They are handy for running reports , extracting data, creating new database users, and performing any other complex task that you need to repeat on a periodic basis. Much of this book centers on the concept of writing SQL*Plus scripts to automate these types of routine tasks . You will begin to see scripts used beginning in Chapter 5 where you will learn how to write scripts to take advantage of SQL*Plus's reporting functionality. Chapter 8 and Chapter 11 delve into the subject of scripting even more deeply.