3 SQL*Plus

3.1.1 SQL*Plus User Profiles

There are two startup files for SQL*Plus:

• GLOGIN.SQL, which is the global startup file

• LOGIN.SQL, which is designed for local and individual use

Whenever any user starts SQL*Plus, first the GLOGIN.SQL file is read, followed by the user's LOGIN.SQL file.

set echo off
set feedback 4
set pause on
set pause  PLEASE ACKNOWLEDGE TO CONTINUE
set echo on 

3.1.2 Starting SQL*Plus

To start SQL*Plus, enter:

/START-PROGRAM $ORAC1020.SQLPLUS
* userid/password

If you omit either the user ID or password, then you are prompted to enter them.

Once you are logged in to SQL*Plus, the SQL prompt is displayed:

SQL>

If you do not want to be prompted for userID/password, enter:

/START-PROGRAM $ORAC1020.SQLPLUS
* /NOLOG
SQL> connect userid/password

You can enter any SQL statement (SELECT . . . FROM, CREATE TABLE, and so on.) or any SQL*Plus command (SET LINESIZE, COLUMN x FORMAT..., and so on) in response to this prompt.

3.1.3 Interrupting SQL*Plus

Use the INTERRUPT key [K2] to interrupt SQL*Plus SQL statements. For example, you can interrupt SQL*Plus if you receive a long report that you do not want to select. When you press the INTERRUPT key [K2], the Oracle Database stops retrieving rows and returns you to the SQL*Plus command level.

3.1.4 Issuing BS2000 Commands from SQL*Plus

The SQL*Plus HOST command and the $ command enable you to enter a BS2000 command while you are logged on to SQL*Plus.

The following BS2000 commands, if used with the HOST or $ command, do not return you to SQL*Plus when they have finished running:

• START-PROGRAM

• LOAD-PROGRAM

• CALL-PROCEDURE

• HELP-SDF

• LOGOFF

Some examples of how you can use the HOST command:

• If you enter the HOST command without any BS2000 command, then it takes you to the command level:

  SQL> HOST 
  

  To return to SQL*Plus, you must use the RESUME command.

• If you enter the HOST command with a BS2000 command, then the command is run and you return to SQL*Plus:

  SQL> HOST STA L
  

3.1.5 Starting the BS2000 Editor

You can use the SQL*Plus EDIT command to start the BS2000 editor:

SQL> EDIT

This command:

• writes the SQL buffer (which contains the current SQL statement) to a file called SQLEDT.BUF

• starts the editor EDT, which reads the file SQLEDT.BUF into the work area

You can then edit and write to this file (using the @write command). Using the @halt command, you can leave the editor and return to SQL*Plus. SQL*Plus then reads the current contents of SQLEDT.BUF back into its command buffer, from which the SQL statement can be run.

You can also use the EDIT command to edit a SQL file by specifying the SQL file in the EDIT command. For example, if you enter the following command, then the editor EDT is called to edit the LOGIN.SQL file. Note that you can omit the default file name extension .SQL.

SQL> EDIT login[.SQL]

Refer to the SQL*Plus User's Guide and Reference for more details about the SQL EDIT command.

3.1.6 The SQL ASCII Function

The ASCII function takes a character (under BS2000/OSD, this will be an EBCDIC character) and returns the numerical representation of that character in the given character set. The ASCII function does not convert an EBCDIC character into its ASCII equivalent. For example, the ASCII function returns the value 193 for the character A. The inverse function is CHR, for example, CHR(193)='A'.

3.1.7 Spooling SQL*Plus Output

When using the SQL*Plus SPOOL command, SQL*Plus uses the default output-file suffix, .LST.

When you issue a SPOOL OUT request, the program issues the BS2000 /PRINT command:

/PRINT tempfile,ERASE 

where tempfile is a temporary copy of the spool file. This routes the file to the central printer. If you need to specify any /PRINT command options, such as character sets, or routing to a remote printer, then do so by adding the following line to the ORAENV file:

PRINTPAR=options  

Where options is any sequence of/PRINT command options (refer to the BS2000/OSD manual, Benutzerkommandos (ISP-Format) for more information about these options). The program then issues a /PRINT command, which includes these options.

3.1.8 Using SQL*Plus Symbols

The SQL symbol used for negation is the exclamation point (!). The use of exclamation point is recommended when specifying "not equal," especially for applications that may be run in different environments.

If no exclamation point is available on your keyboard, then you can use left and right angle brackets (<>) for "not equal."

The SQL*Plus symbol used for concatenation is the vertical bar, "|" (X'4F'). For users with German keyboards, any key that transmits a X'4F' (for example, "ö"), can be used.