11
SQL*Plus

This chapter explains how to use SQL*Plus in the OpenVMS environment. For detailed information about SQL*Plus, see the SQL*Plus User's Guide and Reference.

The following topics are covered in this chapter:

Warning Message When Invoking SQL*Plus

When invoking SQL*Plus, you might see the message:

Error accessing PRODUCT_USER_PROFILE 
Warning:  Product user profile information not loaded. 
You may need to run PUPBLD.SQL as SYSTEM

This means that the table PRODUCT_USER_PROFILE does not exist. This table is used to provide additional security.

You can ignore the warning. However, if you want additional, product-level security, you must create the PRODUCT_USER_PROFILE table. To do this, run the file ORA_SQLPLUS_DEMO:PUPBLD.SQL from the Oracle SYSTEM account. For more information, see Appendix E in the SQL*Plus User's Guide and Reference.

Interrupting SQL*Plus

SQL*Plus has its own [CONTROL]-C handler. For example, you cannot disable [CONTROL]-C from the OpenVMS environment by typing:

SET NOCONTROL=Y

Note that [CONTROL]-C and [CONTROL]-Y work differently in SQL*Plus. Pressing [CONTROL]-C interrupts SQL*Plus. If records are being displayed when SQL*Plus is interrupted, [CONTROL]-C terminates the SQL statement currently executing, stops the display, and returns you to the SQL*Plus prompt. You can also use [CONTROL]-Y to exit SQL*Plus; however, this sequence requires process recovery, so you should not use it under normal conditions.

Installing Help Tables

The SQL*Plus Help tables are not installed as part of the installation procedure. Instead, you must install these tables manually by executing the following command:

$ @ORA_SQLPLUS:HELPINS

Before running this procedure, you must have SQL*Loader installed.

The procedure prompts you for the password of SYSTEM. At the prompt, enter the current SYSTEM password. For example, if you have not changed passwords, enter "MANAGER."

Running System Commands

To run DCL commands from SQL*Plus, precede the DCL command with a dollar sign (or the word HOST). Terminate HOST commands with a semi-colon, otherwise you might encounter DCL syntax errorsFor example, you can use either of the following DIR commands:

SQL> HOST DIR [.NOTES]; 
SQL> $ DIR [.NOTES];

The dollar sign indicates that the rest of the line should be passed to OpenVMS as a DCL command. SQL*Plus attempts to create a subprocess that will execute the command. Your OpenVMS account must have the privileges or quotas required to create subprocesses. If you cannot create a subprocess, SQL*Plus returns control to your terminal.

If you are issuing a DCL command from within SQL*Plus or another product with a SQL interpreter, the command cannot be longer than 256 characters. If, however, you issue the same command directly from the DCL line, the limit may be higher depending on the number of parameters the command can take.

For example, the SUBMIT command has eight optional parameters; therefore, you can use up to 9x256 characters to issue the command from the DCL command line--256 characters for each command or parameter. If you are issuing the SUBMIT command from within SQL*Plus, you have a total of only 256 characters for the entire command (including parameters).

Passing Parameters to SQL*Plus

This section discusses the three ways you can pass parameters to SQL*Plus:

Passing Parameters to SQL*Plus from OpenVMS

To pass parameters to SQL*Plus from the DCL command line, list the values of the parameters after the @<sqlplus_script_file>. If you want to preserve case, put the parameter values in double quotes.

For example, to pass the values SAL, EMP, and Adams to a script file named TESPAR.SQL from the DCL command line, enter:

$ SQLPLUS <username>/<password> @TESPAR SAL EMP "Adams"

Within the TESPAR script file, &1, &2, and &3 correspond to the values passed in the command line. You could then have the following statement in the script file:

SELECT &1 FROM &2 WHERE ENAME = '&3'

The only limit to the number of parameters that can be passed to SQL*Plus is the size of the command line. For more information, look up the topic "Substitution variables" in the SQL*Plus User's Guide and Reference.

Note:
You should avoid naming your script files with names of I/O logical names (for example, 'TT'), as SQL*Plus may try to run the I/O logical name rather than the script file. As a general rule, care should be taken when using logical names.

When a parameter contains spaces, passing it to SQL*Plus is somewhat tricky. Suppose you have the following SQL script called TEST.SQL:

SELECT '&1'

FROM dual;

Then, as an example, you could pass the string "This is a test" by entering either:

$ SQLPLUS SCOTT/TIGER @TEST "'This is a test.'"

or:

$ SQLPLUS SCOTT/TIGER @TEST """This is a test."""

Passing Parameters to SQL*Plus Interactively from a User

For all variables that have not been defined or passed as arguments in the command line, SQL*Plus prompts for values from the user. The ACCEPT statement can be used to prompt the user for the values of variables. For more information, refer to "Writing Interactive Commands" in Chapter 3 of the SQL*Plus User's Guide and Reference.

Passing Parameters to SQL*Plus from an Input File

You can pass parameters dynamically to SQL*Plus from other programs and utilities. The parameters are passed from the DCL command procedure, via the command line, to SQL*Plus.

The following example is a DCL command procedure that reads values from an input data file and passes those values to SQL*Plus:

SELECT &1 FROM EMP WHERE SAL = &2 AND ENAME = '&3'

The input file INFILE.DAT contains the parameter values in a predetermined order, one value on each line. For example:

SAL 

5000 

ADAMS

The DCL command procedure, TOSQL.COM, opens INFILE.DAT and reads the values from INFILE.DAT, each into a different symbol, before invoking SQL*Plus.

The following example shows the DCL command procedure:

$ OPEN/READ INF INFILE.DAT 
$ READ INF PAR1 
$ READ INF PAR2 
$ READ INF PAR3 
$ CLOSE INF 
$ SQLPLUS <username>/<password> @TOSQL 'PAR1' 'PAR2' - "''PAR3'" 
$ . . .

Using the single quote around the arguments is necessary to substitute the value of the symbol (PAR1, PAR2, and PAR3). The order and number of parameters is predetermined and known by the command procedure. Note the special use of quotes around the third parameter; this ensures that for the corresponding value (ADAMS), uppercase will be preserved.

As an alternative, the parameters in INFILE.DAT could have been placed in one line. Then, a DCL lexical function could be used to extract and separate the parameters.

Passing Values from SQL*Plus

Besides passing parameters to SQL*Plus, you can also:

Values must be numeric; you cannot pass strings. However, a numeric value can be either a constant or a variable.

Passing a Numeric Value

To pass a single numeric value from SQL*Plus back to DCL, use the EXIT facility of SQL*Plus.

Constant

To pass a constant value, place that value after the EXIT statement of SQL*Plus. For example, to return the value 66 to DCL, execute the following command:

SQL> EXIT 66

The value 66 is placed in the system symbol $STATUS. To check for this value or to use this value from DCL, type:

$ IF $STATUS .EQ. 66 THEN GOTO SUB66

Variable

To pass a variable value back to DCL, use the NEW_VALUE function of SQL*Plus. For example, to display the message "You are underpaid" on the screen whenever the value of SAL from EMP is below 1000, follow these steps:

Add the following lines to the SQL*Plus script file:

COLUMN VARY NEW_VALUE SALVAL 
BREAK ON VARY 
SELECT SAL VARY FROM EMP WHERE ENAME = 'JAMES'; 
EXIT SALVAL

Add the following lines to the DCL command procedure:

$ SET MESSAGE/NOFACILITY/NOSEVERITY/NOIDENTIFICATION/NOTEXT 
$ SET NOON 
$ SQLPLUS <username>/<password> @sqlfile 
$ IF $STATUS .LT. 1000 THEN -
	 WRITE SYS$OUTPUT "You are underpaid" 
$ SET ON 
$ SET MESSAGE/FACILITY/SEVERITY/IDENTIFICATION/TEXT

The SET MESSAGE command suppresses the display of the DCL message that corresponds to the value returned in $STATUS. The SET NOON command prevents the command procedure from aborting if the SQL*Plus exit value is interpreted as an OpenVMS error status.

Passing Multiple Values

To pass more than one value from SQL*Plus to DCL, write the values from SQL*Plus to an ASCII file. Then, open and read that file from the DCL command procedure.

When you use the SQL*Plus commands EXIT and WHENEVER SQLERROR EXIT to pass variables back to the operating system, the variable is stored in the OpenVMS symbol $STATUS.

Note:
This symbol stores only the status of the most recent command (that is, the status of the SQLPLUS command itself).

Using Profile Files

If you plan to run SQL*Plus often, with the same parameters and options in effect, you can store these preferences in one or more profile files. Whenever you run SQL*Plus, SQL*Plus looks for these files and calls up a session using the preferences specified in the files.

Types of Files

You can have one or both of the following profile files:

GLOGIN.SQL

Preferences that apply to all users at your site

LOGIN.SQL

Contains only preferences for a particular user

Order of Execution

If both files exist for a particular user, SQL*Plus first executes GLOGIN.SQL. Then it executes LOGIN.SQL. Thus, the preferences in LOGIN.SQL either supplement or override the preferences in GLOGIN.SQL.

Creating the Files

To create the files and make SQL*Plus use them, you must do the following:

Editing the Sample Files

A sample GLOGIN.SQL is in the ORA_SQLPLUS directory. Edit this file to specify your site's preferences.

Running DEMOBLD.COM creates a sample LOGIN.SQL file and places it in the directory from which you ran DEMOBLD. For each user who needs his or her own preferences, place a copy of LOGIN.SQL in the user's login directory and edit it accordingly.

Telling SQL*Plus Where the Files Are

To tell SQL*Plus where the site-profile files are, define the following logical names:

ORA_DATA

Directory containing GLOGIN.SQL

ORA_PATH

Directory containing LOGIN.SQL, and any other SQL script other than GLOGIN.SQL

When SQL*Plus looks for LOGIN.SQL, or any other SQL script, it first looks in the current directory, and then looks in the directory specified by ORA_PATH. When SQL*Plus looks for GLOGIN.SQL, it only looks in ORA_DATA. So, unless you define ORA_DATA, SQL*Plus will not execute GLOGIN.SQL.

For example, if you keep GLOGIN.SQL in ORA_SQLPLUS, execute the following command:

$ DEFINE/SYSTEM/NOLOG ORA_DATA ORA_SQLPLUS

Then, to tell SQL*Plus to look for LOGIN.SQL in each user's login directory, execute the following command:

$ DEFINE/SYSTEM/NOLOG ORA_PATH SYS$LOGIN

Using the OpenVMS Editor from SQL*Plus

You can bypass the SQL*Plus editor and use an OpenVMS editor such as TPU from SQL*Plus. When the user exits the OpenVMS editor, SQL*Plus regains control. Your OpenVMS account must have the privileges or quotas required to create subprocesses.

To invoke the editor, type:

SQL> EDIT

or:

SQL> ED

The current SQL*Plus text buffer is placed in the edit buffer and is given the temporary name AFIEDT.BUF. If you invoke the editor with a filename argument, as in:

SQL> ED QUERY4.SQL

the named file is placed in the edit buffer.

The default editor for SQL*Plus is the editor invoked by the EDIT command, usually TPU. To change this default, include the following line in your LOGIN.SQL for SQL*Plus, or execute it as a command from SQL*Plus:

DEFINE _EDITOR = '<editor>'

where <editor> is the name of the editor you want to run. For example:

DEFINE _EDITOR = 'EDIT/EDT'

Converting SQL*Plus Output Files

By default, text files created by SQL*Plus's SAVE command have the extension .SQL, and spool files created by SQL*Plus's SPOOL command have the extension .LIS. These files are StreamLF type rather than VariableLength type. If other utilities are not compatible with StreamLF type files, you may want to convert your .LIS files to VariableLength type.

To convert .LIS files:

Invoke EDT to create a new file with the extension .TXT:

$ EDIT/EDT <filename>.TXT
Include the .LIS file:

* INCLUDE <filename>.LIS
Exit the editor:

* EXIT

Interpreting Output from the SQL*Plus TIMING Command

The SQL*Plus TIMING command analyzes the performance of SQL*Plus commands and command files by writing their execution time to a timing area. Use the TIMING command to start and stop performance analysis and to display the contents of the current timing area.

On OpenVMS, output from the timing command appears as shown below:

ELAPSED:  0.00:00:01.33

where:

ELAPSED

Elapsed execution time, in seconds

Exit Status within DCL

Note that the DECC runtime library will modify the exit status of "0". If you specify:

SQL> EXIT 0

Then the DCL $STATUS symbol will actually receive the value "1." This occurs for portability reasons: on UNIX systems, "0" is a success indicator and it is translated to the VMS success status of "1."

For more information, see the section on "exit" in the DEC C Runtime Library Functions and Macros reference manual.

11 SQL*Plus