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:
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.
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
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.
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
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
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.
If you do not want to be prompted for user ID/password, then enter:
/START-PROGRAM $ORAC1120.SQLPLUS * /NOLOG SQL> connect userid/password
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.
The following BS2000 commands, if used with the
$ command, do not return you to SQL*Plus when they have finished running:
Some examples of how you can use the
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 _EDITORcommand 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> EDIT login[.SQL]
Refer to the SQL*Plus User's Guide and Reference for more details about the
SQL EDIT command.
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'.
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
tempfile is a temporary copy of the spool file. This routes the file to the central printer. If you need to specify any
options is any sequence of
/OSD manual, Benutzerkommandos (ISP-Format) for more information about these options). The program then issues a
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.
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
$GLOBAL, as follows:
then, when you enter the following command:
SQL*Plus searches for the command file in the following sequence, until a matching file name is found:
Refer to Chapter 1, " Getting Started" for more information about default file name extensions.
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.
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:
|File name length||54 (including catalog-id and user ID)|
|Maximum number of nested command files||12|
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:
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.
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.
bs2cmd POSIX shell command to execute BS2000 SDF commands.
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
ssh using a xterm terminal, then the default editor in SQL*Plus is set to
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
vi does not work on blockmode terminals.
edtu does not work on xterm terminals.