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

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

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

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

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:

@@subdir/new_script 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:\sqlplus\ExampleData;c:\sqlplus\ExampleScripts 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.

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.

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:\a\ex8-2
sqlplus /nolog @c:\a\ex8-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-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 "Dollars|Charged" FORMAT $999,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:\a\ex8-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:\a\ex8-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.