Executing a Script

Table of contents:

Once you've written a script, you can invoke it in different ways. One option is to invoke it interactively from the SQL*Plus prompt, a technique you've already seen used in previous examples. You do this using the @ command. To run the script ex8-4b.sql , you write an @ symbol followed immediately by the filename of the script you wish to execute:



The .sql extension is optional and is assumed unless you specify otherwise .

Usually, you write scripts so you can invoke them automatically. To that end, you should know how to invoke them from your operating system command line. In addition, under Windows, you can package a script in a way that lets you easily invoke it by double-clicking an icon. Finally, i SQL*Plus enables you to invoke scripts via the Internet.

8.5.1 Invoking a Script from the Command Line

To execute a script from your operating system command line, use the following syntax:







To pass arguments to your script, include them after the script name . Enclose parameters containing whitespace within quotes. If you have any doubts as to whether to use quotes, then use them:










" . . .

If you are connecting to a remote database, you must also specify the net service name for that database:








Net service names are often defined by your DBA in a file named tnsnames.ora but may be defined in an LDAP directory. If you have any doubts about what service_name to use, ask your DBA. You may also use the easy connection identifier syntax described in Chapter 2.

Be sure that any script you invoke from the command line ends with an EXIT command. If you omit EXIT, your command-line session will remain in SQL*Plus, at the SQL> prompt.

If you're invoking a SQL*Plus script from within another program, you may wish to use the silent mode to prevent any trace of SQL*Plus from showing through to your users. When you run in silent mode, all prompts and startup messages are suppressed, and command echoing does not occur. Invoke SQL*Plus in silent mode by using the -S (or -s ) option:

sqlplus -s


. . .

Ideally, you should avoid embedding Oracle passwords within scripts that invoke SQL*Plus. To that end, you can avoid the need to specify an Oracle password on the SQL*Plus command line by taking advantage of Oracle's operating system authentication feature. See the sidebar "Running SQL*Plus Reports from Shell Scripts" in Chapter 5.

Early versions of SQL*Plus on Windows used names other than sqlplus for the SQL*Plus executable. For example, in Oracle7 Release 7.3 for Windows, you had plus23 and plus23w , depending on whether you wanted to invoke command-line SQL*Plus or the Windows GUI version. The changing version numbers embedded in these names were a constant source of frustration. Thankfully, Oracle stopped embedding version numbers in their Windows executable names when Oracle8 i Database was released.


8.5.2 Accessing Command-Line Arguments

You can access command-line arguments using the special substitution variables &1 , &2 , &3 , etc. The first command-line argument is &1 , and the rest are numbered in the order they occur. A sometimes useful technique is to issue PROMPT commands to remind the user of the arguments:

PROMPT Script arguments:

PROMPT 1 - Schema name

PROMPT 2 - Table name


This script is meant to be run as follows :

@describe gennick employee

If you omit the arguments, the PROMPTs will remind you of what the arguments should be. Then, SQL*Plus will automatically prompt you for the undefined substitution variables:



Script arguments:

1 - Schema name

2 - Table name

Enter value for 1:

Like any other substitution variable, any values you supply for &1 , &2 , etc., will linger for the duration of your SQL*Plus session or until you remove them using the UNDEFINE command.

What Type of Slash?

Unix requires a forward slash in directory paths, while Windows uses a backward slash. What do you do when you want to write a script that runs on both platforms? It turns out that on Windows you can use either type of slash. For example, on Windows you can use either of the following commands to invoke a script within a subdirectory of your current working directory:






If you're writing scripts that you may need to run on both platforms, use a forward slash all the time.


8.5.3 Specifying a Search Path for Scripts

If you have collections of scripts in different directories, you can specify a search path that SQL*Plus will use to find those scripts. This saves you from the bother of having to type a directory path each time you invoke a script.

Use the SQLPATH environment variable to specify a search path. From Unix and Linux shells , you must separate the directory names using colons, and you usually set environment variables using the export command:

export SQLPATH=$HOME/sqlplus/ExampleData:$HOME/sqlplus/ExampleScripts

On Windows systems, use the SET command and separate paths by semicolons:

set SQLPATH=c:sqlplusExampleData;c:sqlplusExampleScripts

When you execute a script using the @ command, SQL*Plus will always look for that script in your current working directory first. Then, it will search the directories specified by SQLPATH, in the order in which they occur.

SQL*Plus on Unix and Linux systems will also search any directories specified by the ORACLE_PATH environment variable. However, SQLPATH directories will be searched first. On VMS systems, you specify the SQL*Plus search path using the ORA_PATH logical name.

8.5.4 Placing SQL*Plus Commands into a Shell Script

On Unix and Linux systems, it's unnecessary to run all scripts from .sql files. You can invoke SQL*Plus from within a shell script and redirect standard input to that same script, which will cause SQL*Plus to read subsequent lines in the shell script as if they were typed in from the command line. Example 8-5 illustrates this, showing a shell script that invokes SQL*Plus to execute a MERGE statement that loads new and updated project data from an external table named project_external .

Example 8-5. SQL*Plus commands embedded directly in a Linux shell script

sqlplus gennick/secret << EOF


MERGE INTO project p

USING (SELECT * FROM project_external) pe

ON (p.project_id = pe.project_id)


 /* update budget in an existing project record */

 SET p.project_budget = pe.project_budget


 /* insert a new project record */

 (project_id, project_name, project_budget)

 VALUES (pe.project_id, pe.project_name, pe.project_budget);



There are several important aspects of this script:

sqlplus gennick/secret << EOF

The << EOF causes the Unix (or Linux) shell to pass subsequent lines in the shell script to SQL*Plus as input. As far as SQL*Plus is concerned , it's as if you typed those lines yourself interactively from a SQL> prompt.

MERGE INTO project p

MERGE is a SQL statement that will do an INSERT or an UPDATE depending upon whether a condition that you specify is true.

ON (p.project_id = pe.project_id)

This is the condition. When the value of project_id from the external table matches an existing project_id in the project table, the existing row will be updated with ( potentially ) new information. Otherwise, if no match occurs, a new row will be inserted into project .


You don't need to update all columns . Example 8-5 updates only the project budget.


However, if a new project is loaded, then all three values are inserted.


This EOF marker in the shell script has meaning to the shell, not to SQL*Plus. When the shell sees this, it stops passing lines of input to SQL*Plus. This marker must match that used following the << on the line originally invoking SQL*Plus.

Depending on the shell you are using, you may be able to use arbitrary markers, such as JGG instead of EOF, as long as you are consistent and use the same marker in both places.


The ability to embed SQL*Plus commands within a shell script can sometimes save you from having to create two files. Rather than write a shell script for the sole purpose of running a separate SQL*Plus script, you can combine both into one. Be aware though, that reading SQL*Plus commands that are embedded within a shell script is semantically not quite the same as executing a separate .sql file. SQL*Plus sees those lines as interactive input, and in at least one situation the difference is significant: You can't, for example, SET TERMOUT OFF in a shell script because SQL*Plus refuses to disable terminal output when it's executing interactively. A SET TERMOUT OFF command in Example 8-5 would be ignored.

8.5.5 Creating a Windows Shortcut

Under Windows, it's possible to create an icon or a shortcut that can be used to invoke a SQL*Plus script. Add to that some good prompting and a bit of error checking, and you can write SQL*Plus scripts that are accessible to end users or at least to your more technically inclined end users.

You need to make two decisions if you are going to create an icon or shortcut to execute a script. One is whether to embed the Oracle username and password into the shortcut or to prompt the user for this information. The second is which version of SQL*Plus you want to use: the GUI version or the DOS version.

Both of these decisions affect the command used by the shortcut to invoke SQL*Plus and start the script. Your job is easiest if you can embed an Oracle username and password into the shortcut. However, it's far safer to prompt for at least the password. You want to avoid embedding a password in a Windows shortcut definition where any user of the system can see it.

For purposes of example, let's assume you are going to create a Windows shortcut to run the Project Hours and Dollars Report shown earlier in Example 8-1. Starting the SQL*Plus executable

The Windows version of Oracle contains two SQL*Plus executables. Use sqlplus to start the command-line version. Use sqlplusw to start the GUI version. Before you can create the shortcut, you need to decide on the exact command you will use to start SQL*Plus. Here are two possibilities to consider:






sqlplus /nolog @c:aex8-2

The first option provides SQL*Plus with a username but not a password. Consequently, SQL*Plus will prompt the user for a password. This option works well if you know the username up front and if your users will only log in with that one username. To provide more flexibility, use the second option, which requires that your script prompts for username and password. The second option is the one I'll use in this chapter's example.

When you start up SQL*Plus with the /NOLOG option, your script must log into a database before it executes any SQL statements. Use the CONNECT command to do this, and use substitution variables to allow the user to enter her username and password at runtime. Example 8-6 shows a new version of the script from Example 8-1. This script uses two ACCEPT commands and one CONNECT command to prompt for a username and password and then to log the user into Oracle. The script executes SET TERMOUT OFF to save the user from having to watch the output scroll by on the screen. Finally, report output is spooled to a file named ex8-6.lst in the c:a directory (one I commonly use for scratch files on Windows systems).

Example 8-6. A script that explicitly prompts for username and password



ACCEPT username CHAR PROMPT 'Enter your Oracle username >'

ACCEPT password CHAR PROMPT 'Enter your password >'

CONNECT &username/&password@db01

--Set up pagesize parameters



--Set the linesize, which must match the number of equals signs used

--for the ruling lines in the headers and footers.


--Set up page headings and footings

TTITLE CENTER "The Fictional Company" SKIP 3 -

 LEFT "I.S. Department" -

 RIGHT "Project Hours and Dollars Detail" SKIP 1 -

 LEFT "========================================" -

 "==========================" -

 SKIP 2 "Employee: " FORMAT 9999 emp_id_var " " emp_name_var SKIP 3

BTITLE LEFT "========================================" -

 "==========================" -

 SKIP 1 -


--Format the columns

COLUMN employee_id NEW_VALUE emp_id_var NOPRINT

COLUMN employee_name NEW_VALUE emp_name_var NOPRINT

COLUMN project_id HEADING "Proj ID" FORMAT 9999

COLUMN project_name HEADING "Project Name" FORMAT A26 WORD_WRAPPED

COLUMN time_log_date HEADING "Date" FORMAT A11

COLUMN hours_logged HEADING "Hours" FORMAT 9,999

COLUMN dollars_charged HEADING "DollarsCharged" FORMAT 9,999.99

--Breaks and computations


 ON employee_name NODUPLICATES -

 ON project_id SKIP 2 NODUPLICATES -

 ON project_name NODUPLICATES

COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id

COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id

COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id

COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id

--Execute the query to generate the report.


SPOOL c:aex8-6.lst

SELECT e.employee_id,




 TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date,



 FROM employee e INNER JOIN project_hours ph

 ON e.employee_id = ph.employee_id

 INNER JOIN project p

 ON p.project_id = ph.project_id

WHERE e.employee_id = 107

ORDER BY e.employee_id, p.project_id, ph.time_log_date;



Once you have decided how to start SQL*Plus and which version to run, you are ready to create a Windows shortcut to run your script. Creating the shortcut

To create a Windows shortcut, right-click on the Windows desktop and select New Shortcut from the pop-up menu. Type the command to start SQL*Plus and execute your script in the location (or command) field. For example, if your command is sqlplusw /nolog @c:aex8-6 , the resulting screen should look like that shown in Figure 8-1.

Figure 8-1. The Windows shortcut wizard


Press the Next button to advance to the next step in which you select a name for the shortcut. Be sure to pick a name that makes sense, one that will remind you later of what the script does. For this example, use the name "Project Hours and Dollars Report." Figure 8-2 shows this screen after the name has been entered.

Figure 8-2. Naming the shortcut


Finally, press the Finish button. The shortcut will be created and will appear on your desktop. If you've specified command-line SQL*Plus ( sqlplus ), the icon will be a generic application icon. If you've specified Windows GUI SQL*Plus ( sqlplusw ), the icon will be drawn from the associated executable and will be the familiar disk platter with a plus on top. Both icons are shown in Figure 8-3.

Figure 8-3. The shortcut icon


Now you can run the script. Double-click the icon and try it.

8.5.6 Executing a Script Over the Internet

Oracle9 i Database introduced the exciting capability of invoking SQL*Plus scripts over the Internet. Rather than specifying the name of a script file on your local filesystem, you can specify the name of a script file accessible via a web server. Oracle9 i Database Release 1 introduced this functionality to SQL*Plus on Windows. Release 2 brought the capability to all platforms.

To invoke a script over the Internet, simply specify a URL following the @ command. The following example invokes a version of Example 8-4 from my own web site:



This script will first DESCRIBE a table. Then

it will list the definitions for all indexes

on that table.

Enter the table name >

This is an incredible boon. No longer must you manually cart your library of scripts from server to server, trying vainly to keep all copies of a script in sync. Place your utility scripts on a web server, and you can easily access them from any Oracle server or client connected to the Internet.


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