Oracle SQL*Plus: The Definitive Guide

Packaging Your Script

For most scripts you write, you will be satisfied just invoking them from the SQL*Plus prompt. Sooner or later, however, you are going to write a script that you want to share with an end user who may not be familiar with SQL*Plus, or you may end up implementing a complex batch process as a SQL*Plus script. In either of these cases you may find it convenient to create a command or icon that can easily be used to execute the script in question. In the Microsoft Windows environment, for example, you could easily create an icon on which an end user could double-click in order to produce a report or extract data.

Creating a Windows Shortcut

There are two decisions you need to make 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 to decide 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 the Oracle username and password, or at least the username, into the shortcut. If you need to prompt for both username and password at runtime, then you will need to make some minor additions to your script.

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 this chapter. This section will show you how to do that both for the case where you can hardcode the Oracle username in the shortcut, and for the case where you can’t.

Starting the SQL*Plus executable

The Windows version of Oracle contains two SQL*Plus executables. One starts the command-line version while the other starts the GUI version. The exact executable names vary slightly from one release of Oracle to the next because the SQL*Plus version number is embedded in the last two characters of the filename. The locations of these executables vary as well, because the Oracle home directory is named differently under Windows 95 and NT.

Tip

Beginning with version 8.1, Oracle has changed the naming convention used for the Windows and command-line implementations of SQL*Plus. SQLPLUS.EXE runs the command-line version, while SQLPLUSW.EXE starts the GUI version.

Before you can create the shortcut, you need to decide on the exact command you will use to start SQL*Plus. Table 4.2 shows three commands. Each starts the GUI version of SQL*Plus under Windows 95, and executes the SQL script to produce the report. The difference is in whether or not the user is to be prompted for an Oracle username, a username and password, or not prompted at all.

Table 4-2. Commands to Start SQL*Plus and Execute a Script

Command	Result
PLUS80W username/password@connect @hours_dollars_d	Username and password embedded in the command; user not prompted.
PLUS80W username@connect @hours_dollars_d	Username embedded in the command; SQL*Plus prompts for the password.
PLUS80W /NOLOG @hours_dollars_d	Your script must prompt for both username and password.

Tip

The @ sign is used both to mark the connect string and to start a script file. You must have at least one space between the login string and the command that starts the script; otherwise, SQL*Plus will become confused and try to interpret the script file name as a connect string.

With the first command shown in Table 4.2, your script will simply run. SQL*Plus will connect to Oracle with the username and password provided. The second command supplies only a username, so SQL*Plus will prompt for the password, and then run your script. The third command deserves more explanation. If you leave off both the username and password entirely, yet specify a script file on the SQL*Plus command line, SQL*Plus gets confused. If you want to prompt the user for both his username and password, you have to do it from within your script. In order to allow for that, the /NOLOG option is used to tell SQL*Plus to start up without first attempting to connect to a database.

Tip

The implementation of SQL*Plus that ships with Oracle8 Personal Edition doesn’t seem to handle PLUS80 username@connect properly. Even though your connect string specifies a remote database, that implementation of SQL*Plus will still attempt to connect you to the local database.

When you start up SQL*Plus with the /NOLOG option, your script must log into a database before it executes any SQL statements. The CONNECT command is used to do this, and you can use substitution variables to allow the user to enter the required values at runtime. The following commands, for example, prompt for a username and password, then use that information to connect to Oracle.

ACCEPT username CHAR PROMPT 'Enter your Oracle username >'
ACCEPT password CHAR PROMPT 'Enter your password >'
CONNECT &username/&password

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 95 or NT desktop, and select New → Shortcut from the popup menu. Type the command to start SQL*Plus and execute your script in the “Command line:” field. For example, if your command is “PLUS80W /NOLOG @hours_dollars_d”, the resulting screen should look like that shown in Figure 4.1.

Figure 4-1. The Windows shortcut wizard

Press the Next button to advance to the next step, where 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 4.2 shows this screen after the name has been entered.

Figure 4-2. Naming the shortcut

Finally, press the Finish button. The shortcut will be created, and will appear on your desktop. The icon will come from the SQL*Plus executable, and will be the familiar disk platter with a plus on top if you’ve chosen to use the GUI version. This is shown in Figure 4.3.

Figure 4-3. The shortcut icon

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

Creating a Unix Command

If you are running under Unix, you can write a simple shell script to invoke SQL*Plus and run a SQL*Plus script. The command you need to place in your script file is pretty much the same as the one used in the previous Windows 95 shortcut example. For example, if you are running the Korn shell, the following command is all you need to run the Project Hours and Dollars Report:

sqlplus username @hours_dollars_d

Unlike with Windows, the Unix command to invoke SQL*Plus is almost always sqlplus. Note that the above command does not include a password. It’s always best to avoid hardcoding passwords in script files. In this case, SQL*Plus will prompt you for a password when you execute the script.