Executing a Script

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:

SQL> @ex8-4b

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:

sqlplus username / password @ script_name

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:

sqlplus username / password @ script_name arg " arg " . . .

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

sqlplus username / password @ service_name script_name

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.

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

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.

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 DESCRIBE &1..&2

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:

SQL> @describe 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.

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 SET VERIFY OFF MERGE INTO project p USING (SELECT * FROM project_external) pe ON (p.project_id = pe.project_id) WHEN MATCHED THEN UPDATE /* update budget in an existing project record */ SET p.project_budget = pe.project_budget WHEN NOT MATCHED THEN INSERT /* insert a new project record */ (project_id, project_name, project_budget) VALUES (pe.project_id, pe.project_name, pe.project_budget); COMMIT; EOF

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 .

WHEN MATCHED THEN UPDATE

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

WHEN NOT MATCHED THEN INSERT

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

EOF

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.

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.

8.5.5.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 username @ service_name @c:aex8-2 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

SET ECHO OFF SET RECSEP OFF ACCEPT username CHAR PROMPT 'Enter your Oracle username >' ACCEPT password CHAR PROMPT 'Enter your password >' CONNECT &username/&password@db01 --Set up pagesize parameters SET NEWPAGE 1 SET PAGESIZE 55 --Set the linesize, which must match the number of equals signs used --for the ruling lines in the headers and footers. SET LINESIZE 66 --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 - RIGHT "Page " FORMAT 999 SQL.PNO --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 BREAK ON employee_id SKIP PAGE NODUPLICATES - 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. SET TERMOUT OFF SPOOL c:aex8-6.lst SELECT e.employee_id, e.employee_name, p.project_id, p.project_name, TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date, ph.hours_logged, ph.dollars_charged 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; SPOOL OFF EXIT

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.

8.5.5.2 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:

SQL> @http://gennick.com/sqlplus/ex8-4b 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.