|Oracle8i Server User's Guide
Release 3 (8.1.7) for Fujitsu Siemens Computers BS2000/OSD
Part Number A95463-01
SQL*Plus is an interactive program for accessing data in an Oracle Server database. 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 refer to Installation and Database Administration Guide (see section Known Problems, Restrictions and Workarounds).
There are two startup files for SQL*Plus: GLOGIN.SQL, the global startup file, and LOGIN.SQL, designed for local and individual use. Whenever any user invokes 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 executed at the beginning of the SQL*Plus session. The GLOGIN.SQL file is located under the $ORACL817 userid and its name is $ORACL817.SQLPLUS.ADMIN.GLOGIN.SQL. The Database Administrator may customize this file if desired. This file will be executed regardless of the current userid.
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 execute at the beginning of every SQL*Plus session.
SQL*Plus first searches for LOGIN.SQL under the current BS2000 userid. If there is no LOGIN.SQL file there, but the SQLPATH environment variable specifies a path, SQL*Plus searches along that path. SQL*Plus then executes the first LOGIN.SQL file, if any, it finds. For a customized SQL*Plus environment, each userid can have its own LOGIN.SQL file.
See the appendix "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.
Here is a sample startup file:
set echo off
set feedback 4
set pause on
set pause PLEASE ACKNOWLEDGE TO CONTINUE
set message on
set echo on
To invoke SQL*Plus, enter:
If you omit either your userid or password, you are prompted for them.
Once you are logged in to SQL*Plus, the SQL prompt is displayed:
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.
You can use the INTERRUPT key [K2] to interrupt SQL*Plus SQL statements. For example, you may wish to interrupt SQL*Plus if you receive a long report that you did not really want to select. When you press INTERRUPT [K2], the Oracle Server stops retrieving rows and returns you to the SQL*Plus command level.
If you issue an interrupt when input is requested, you must answer this request before the processing can be interrupted. However, this answer will be ignored.
The SQL*Plus HOST command, and $ command, enable you to enter a BS2000 command while you are logged on to SQL*Plus.
Some BS2000 commands, when issued using the HOST or $ command, do not return you to SQL*Plus, when they have finished executing. This is the case, when you use any of the following BS2000 commands:
Here are some examples of how you can use the HOST command:
To return to SQL*Plus, you must use the RESUME command.
You use the SQL*Plus EDIT command to invoke the BS2000 editor:
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 executed.
If you used the SQL*Plus DEFINE_EDITOR command to define a name for the editor, BS2000 will ignore it. It always invokes 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, the editor EDT is called to edit your LOGIN.SQL file. Note that you can omit the default filename extension .SQL.
Refer to the SQL*Plus User's Guide 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 that 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:
where tempfile is a temporary copy of the spool file. This normally 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, you can do so by adding the following line to your ORAENV file:
where options is any sequence of/PRINT command options (refer to your BS2000/OSD Command Language Manual for more information about these options). The program then issues a /PRINT command, which includes these options.
The SQL symbol for negation is the exclamation point (!). The use of the 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, you can use left and right angle brackets ("<>") for "not equal".
The SQL*Plus symbol for concatenation is the solid 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:
When you use the following command, SQL*Plus searches for a file called "filename.SQL" under the current BS2000 userid:
If this file cannot be found, SQL*Plus searches the path(s) specified by the ORAENV environment variable SQLPATH. This variable is used to specify one or more filename prefixes (separated by ';'), which should be applied when searching for the command file.
For example, if SQLPATH is set to PRIVATE and $GLOBAL, as follows:
when you issue the following command:
SQL*Plus looks for the command file in the following sequence, until a matching filename is found:
For further information on default filename extensions, see the chapter "Getting Started".
The demonstration files containing the examples in the SQL*Plus User's Guide are in the files $ORACL817.SQLPLUS.DEMO.DEMOBLD.SQL and $ORACL817.SQLPLUS.DEMO.DEMODROP.SQL.
See the chapter "Creating a Database" in the Oracle8i Server for Fujitsu Siemens Computers BS2000/OSD Installation and Database Administration Guide for information on how to install this demonstration.
Refer to the SQL*Plus User's Guide for information on how to run this demonstration.
The limits of several SQL*Plus elements are specified in the SQL*Plus User's Guide. The following table defines BS2000/OSD specific limits:
54 (including catalog-id and user-id)
Maximum number of nested command files