Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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