9
SQL*Plus

Running Under UNIX System Services

Oracle9i provides a native OS/390 UNIX System Services version of SQL*Plus.  To run it, first ensure the environment is established as described in Chapter 4, Running an Oracle Utility Under USS, then enter the following at the command prompt:

sqlplus

For additional information, refer to Chapter 4, " Accessing Oracle9i Under USS" and the SQL*Plus User's Guide and Reference.

Running Under TSO

To access SQL*Plus as a command processor, use the following syntax:

SQLPLUS [-S] [userid[/password[@connect-string]]] [@start_file] 

where:

All parameters are optional to SQL*Plus.

According to the following syntax, the command connects Oracle user SCOTT to the Oracle instance specified in the ORA1 tnsnames alias.  This automatically runs the SQL statements contained in member INITIAL in data set TEST.ORACLE.SQL:

SQLPLUS SCOTT/TIGER@ORA1 @/DSN/TEST.ORACLE.SQL(INITIAL)

Attention Processing

Use of the [PA1] or [Attn] key interrupts the operation of SQL*Plus.  When you enter a command producing results you did not expect or are not interested in viewing, using the attention interrupt is useful.

When running in TSO/E, SQL*Plus attempts to establish an attention processing exit using the TSO/E STAX service.  For this to work, the ORASTAX module must be loadable from JOBLIB/STEPLIB, a linklist library, or LPA.  If ORASTAX cannot be loaded, attention processing is suppressed; no messages are issued to indicate this condition.

When attention processing is established, it is used by both SQL*Plus and by the Oracle program interface code.  The effect of an attention signal depends on whether SQL*Plus or an Oracle server is in control.  If a request has been issued to a server, the attention signal is associated with the Oracle program interface and it will halt the in-progress server request.  Otherwise, the signal is handled by SQL*Plus itself and will halt the current SQL*Plus activity.  For example, if SQL*Plus is displaying large amounts of data from a SELECT statement, and attention will halt the output display and return to the "SQL>" prompt.

! 

Oracle ATTN.

Error: ORA-1013: user requested cancel of current operation 

Running in Batch

You can call SQL*Plus in the batch environment by running the ORASQL JCL procedure.  This procedure is created during the Oracle9i database server installation process.  Consult with your DBA or systems programming staff for the location of this procedure.  A copy of the procedure is reproduced in the following example:

//ORASQL   PROC  INDEX=oran, 

//               LIBV=orav, 

//               SYSOUT='SYSOUT=*' 

//ORASQL   EXEC  PGM=SQLPLUS,REGION=4M 

//STEPLIB  DD    DSN=&INDEX..&LIBV..CMDLOAD,DISP=SHR 

//ORA$LIB  DD    DSN=&INDEX..&LIBV..MESG,DISP=SHR

When running this JCL procedure, include additional DD statements referring to other data sets required for proper operation of the utility.

The following JCL illustrates the use of the procedure described in the previous example:

//USER1JOB JOB   ........ 

//STEP1    EXEC  ORASQL,PARM='SCOTT/TIGER' 

//ORA@sid  DD    DUMMY 

//SYSIN    DD    DSN=SCOTT.ORACLE.SQL(SQLSTUFF),DISP=SHR 

Note the additional DD statements:

• ORA@sid, where sid is the SID associated with the Oracle9i database server to which you want to connect.

• SYSIN, which contains the SQL commands you want to run.

• SYSOUT and SYSERR will be used if present.

Use this procedure as a model for creating your own JCL procedures conforming to your installation's policies.

To pass parameters to SQL*Plus in the batch environment, use the JCL PARM field.

You can also access the Oracle9i database server through the terminal monitor program (TMP) in batch mode.  Refer to "Running Under TSO " in this case.  File allocations required in TSO access are also required in batch mode.

SQL*Plus Profiles

During initialization, SQL*Plus searches for an allocation to the SQLLOGIN DD statement.  If a file is allocated to this DD statement, then it is opened for input and the contents are run automatically, one command at a time, until an EOF is encountered.  This DD statement points to a file that can contain any valid SQL or SQL*Plus statements you want run with each logon to SQL*Plus.

If the file allocated by the SQLLOGIN DD statement is not found during initialization, then the following messages are displayed:

ERROR OPENING /DD/SQLLOGIN: DDNAME STATEMENT MISSING OR MISSPELLED

UNABLE TO OPEN FILE: "/DD/SQLLOGIN"

These messages signal an unsuccessful SQLLOGIN allocation and you are provided with the normal SQL prompt.  This message display is not an error condition.  It indicates automatic running of SQL or SQL*Plus statements at startup did not occur.

If you are not using automatic running of SQL or SQL*Plus statements at startup and do not want to receive these messages, then use the DUMMY parameter in the data set allocation.

A short SQLLOGIN profile example is shown in the following example:

_________________________________________________


Set echo off

set feedback 4

set message on

set echo on

select * from daily_reminder;

__________________________________________________

SQL*Plus HOST Command

The SQL*Plus HOST command allows you to use a variety of commands and utilities from within SQL*Plus.

SQL> HOST SEND 'GOOD MORNING' USER(SMITH) 

SQL> HOST clist 

SQL> HOST EDIT filename OLD DATA 

SQL> HOST ISPF 2 

SQL*Plus Time Usage Information

SQL*Plus provides two means of gathering time usage information:  the TIMING and SET TIMING commands.  Refer to the SQL*Plus User's Guide and Reference for more information about TIMING and SET TIMING.

Elapsed x:xx:xx.xx,  user-CPU    y.yy sec 

Elapsed x:xx:xx.xx,  user-CPU    y.yy sec 

Using OS/390 Editors from SQL*Plus

You can use the ISPF editor with SQL*Plus:

Data Set Enqueuing

When you begin editing a member with ISPF, ISPF obtains an exclusive ENQ for QNAME SPFEDIT and a 52-byte RNAME consisting of the data set name and member.  ENQ is held during the entire edit session.  When you save the member, ISPF obtains a second exclusive ENQ for QNAME SPFEDIT and a 44-byte RNAME containing only the data set name.  ISPF opens the PDS for output, writes the edited member, and closes the PDS.  The two enqueues are released in the order they were obtained.

Restricting User's Privileges in SQL*Plus

The DBA can use the PRODUCT_USER_PROFILE table, located in the SYSTEM account, to disable certain SQL*Plus commands for a user.  You might be able to use the PRODUCT_USER_PROFILE table with your applications.  For more information about the PRODUCT_USER_PROFILE table, consult with the DBA for your system and refer to the SQL*Plus User's Guide and Reference.

Exiting SQL*Plus

To exit SQL*Plus, you can enter one of the following:

SQL>EXIT [return code keyword | variable] 

SQL>QUIT [return code keyword | variable] 

QUIT is a synonym for the EXIT command.

The EXIT command lets you specify an operating system return code.  This enables you to run SQL*Plus command files in batch mode and detect any unexpected events.  Under OS/390, the return code values are:

The syntax for the EXIT command is described in the SQL*Plus User's Guide and Reference.

Usage Notes

There are special considerations for the following:

• Special characters

• String concatenation

• Input line truncation

• SQL*Plus ASCII function

CONCAT('x','y') 

SELECT * FROM EMP WHERE ENAME = CONCAT('CL','ARK'); 

Unsupported Functions

The following functions are not supported:

• SPOOL OUT

• SET NEWPAGE 0

• RUNFORM