Executing a Script

Table of contents:

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.

Referencing Oracle Home

If you're a DBA, you'll often need to run scripts that are installed under the Oracle home directory. One such script is utlxplan.sql , which builds the plan table used by the EXPLAIN PLAN statement. On Unix and Linux systems, you can reference the Oracle home directory using the environment variable $ORACLE_HOME, as in:

SQL>

@$ORACLE_HOME/rdbms/admin/utlxplan

 

When you use $ORACLE_HOME like this, the operating system will replace $ORACLE_HOME with the value of the ORACLE_HOME environment variable.

In Windows, you don't have this $ORACLE_HOME environment variable mechanism. Instead, you can use the question mark (?) to refer to the Oracle home directory. For example:

SQL>

@?/rdbms/admin/utlxplan

 

SQL*Plus recognizes the ? in the path, and replaces that ? with the path to your Oracle home directory. This ? syntax is supported on all platforms, and is especially handy in non-Unix environments. Remember that the Oracle home to which ? refers will be relative to SQL*Plus. If you run SQL*Plus on Windows, connect to an Oracle instance running on a Linux server, and execute @?/rdbms/admin/utlxplan , the utlxplan.sql script that runs will be the one from the Windows machine running SQL*Plus.


     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net