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 Siemens 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 LOGIN.SQL
file.
The global startup file GLOGIN.SQL
is invoked 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 $ORAC1020
user ID, and its name is $ORAC1020.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.
The LOGIN.SQL
startup file is invoked 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 wishes 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, "ORAENV 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
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.
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 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:
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*PlusDEFINE_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.
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'.
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.
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.
The SQL*Plus TIMING
command is used to check the performance of SQL commands and command files. Under BS2000/OSD, data is displayed by TIMING
in the format "CPU time: time sec.". For example:
CPU time: 0.4926 sec.
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:
filename
.SQL
PRIVATE.
filename
.SQL
$GLOBAL.
filename
.SQL
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 Siemens 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:
Item | Limit |
---|---|
File name length | 54 (including catalog-id and userID) |
LINESIZE | 32767 |
MAXDATA | 32767 |
Maximum number of nested command files | 12 |