3 SQL*Plus

SQL*Plus is a general purpose utility used for running SQL commands and PL/SQL blocks, perform database administration, and so on. This chapter describes how you use SQL*Plus under BS2000/OSD. It supplements the SQL*Plus User's Guide and Reference with information about the following topics:

3.1 Running SQL*Plus

The following sections describe how to run SQL*Plus under BS2000/OSD:

For restrictions related to using SQL*Plus, refer to the section, Known Problems, Restrictions, and Workarounds in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD.

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.

3.1.1.1 The GLOGIN.SQL Global Startup File

The global startup file GLOGIN.SQL begins whenever any user starts SQL*Plus. This file can contain SQL statements or SQL*Plus commands to be run at the beginning of the SQL*Plus session. The GLOGIN.SQL file is located under the $ORAC1120 user ID, and its name is $ORAC1120.SQLPLUS.ADMIN.GLOGIN.SQL. The database administrator may customize the GLOGIN.SQL file if required. The GLOGIN.SQL file will be run regardless of the current user ID.

3.1.1.2 The LOGIN.SQL User Startup File

The LOGIN.SQL startup file is called after the GLOGIN.SQL command file each time a user starts SQL*Plus. Like GLOGIN.SQL, this file may contain either SQL statements or SQL*Plus commands that a user wants to run at the beginning of every SQL*Plus session.

SQL*Plus first searches for LOGIN.SQL under the current BS2000 user ID. If the LOGIN.SQL file is not found, but the SQLPATH environment variable specifies a path, then SQL*Plus searches along that path. If SQL*Plus finds a LOGIN.SQL file, then it runs the first LOGIN.SQL file that it finds. For a customized SQL*Plus environment, each user ID can have its own LOGIN.SQL file.

Refer to Appendix B, "Oracle Environment Variables" for a description of the SQLPATH environment variable. Refer to the SQL*Plus User's Guide and Reference for more information about LOGIN.SQL.

The following is a sample startup 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.

Note:

If you issue an INTERRUPT when input is requested, then you must answer this request before the processing can be interrupted. However, this answer will be ignored.

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.

Note:

If you used the SQL*Plus DEFINE _EDITOR command to define a name for the editor, then BS2000 will ignore it. It always starts EDT.

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.

Note:

The output generated by BS2000/OSD operating system commands will not be spooled.

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.2 Specifying the Search Path for SQL*Plus Command Files

When you use the following command, SQL*Plus searches for a file called filename.SQL under the current BS2000 user ID:

SQL> START filename

If this file cannot be found, then SQL*Plus searches the paths specified by the ORAENV environment variable SQLPATH. This variable is used to specify one or more file name prefixes separated by a semicolon (;), which should be applied when searching for the command file.

For example, if SQLPATH is set to PRIVATE and $GLOBAL, as follows:

SQLPATH=PRIVATE;$GLOBAL 

then, when you enter the following command:

@filename

SQL*Plus searches for the command file in the following sequence, until a matching file name is found:

  1. filename.SQL

  2. PRIVATE.filename.SQL

  3. $GLOBAL.filename.SQL

Refer to Chapter 1, " Getting Started" for more information about default file name extensions.

3.3 Sample Schemas and SQL*Plus

The sample schemas provide a common platform for examples. For more information about the sample schemas and SQL*Plus, Refer to SQL*Plus User's Guide and Reference.

Refer to the chapter "Creating and Upgrading a Database" in Oracle Database Installation and Administration Guide for Fujitsu BS2000/OSD for information about how to install the sample schemas.

3.4 SQL*Plus Limits

The limits of several SQL*Plus elements are specified in the SQL*Plus User's Guide and Reference. The following table defines BS2000/OSD specific limits:

Item Limit
File name length 54 (including catalog-id and user ID)
LINESIZE 32767
MAXDATA 32767
Maximum number of nested command files 12

3.5 Using SQL*Plus in the POSIX environment

Starting with Oracle Database 11g Release 2 on Fujitsu BS2000/OSD you can run SQL*Plus not only in the normal BS2000 environment, but also in the POSIX environment.

This section describes the following:

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

Note:

  • The editor vi does not work on blockmode terminals.

  • The editor edtu does not work on xterm terminals.