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 $ORAC1120.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 user ID/password, then enter:

/START-PROGRAM $ORAC1120.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.

3.5.1 Starting SQL*Plus

You can start SQL*Plus in the POSIX shell. Refer to "Starting Oracle Utilities in the POSIX environment" for more information about running SQL*Plus in the POSIX shell.

3.5.2 Running Shell Commands From SQL*Plus

The SQL*Plus HOST command enables you to enter a POSIX shell command, while you are logged on to SQL*Plus.

Keep the following points in mind when using the HOST command:

• If you enter the HOST command without any shell command, then it takes you to the command level. To return to SQL*Plus, you must use the exit command in the POSIX subshell.

• If you enter the HOST command with a shell command, then the command is executed and you return to SQL*Plus.

• Use the bs2cmd POSIX shell command to execute BS2000 SDF commands.

3.5.3 Using an Editor in SQL*plus

Start a text editor in SQL*Plus with the EDIT command, if you want to edit an SQL statement.

The default editor depends on the terminal connected with your POSIX session. If the POSIX shell is started on a blockmode terminal, then the default editor in SQL*Plus is set to edtu. If the POSIX shell is started by a remote X-client through rlogin or ssh using a xterm terminal, then the default editor in SQL*Plus is set to vi.

SQL*Plus provides the opportunity to define a preferred text editor with the DEFINE _EDITOR command. In the POSIX environment you can define a preferred editor. For example, if you want to define the editor, used by the EDIT command, to be the POSIX editor edtu, then enter the following command in SQL*Plus:

DEFINE _EDITOR = edtu