Skip Headers

Oracle8i Server User's Guide
Release 3 (8.1.7) for Fujitsu Siemens Computers BS2000/OSD

Part Number A95463-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

3
SQL*Plus

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:

Running SQL*Plus

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).

SQL*Plus User Profiles

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 GLOGIN.SQL Global Startup 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 User Startup File

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

Invoking SQL*Plus

To invoke SQL*Plus, enter:

/START-PROGRAM $ORACL817.SQLPLUS
* userid/password

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:

SQL>
 

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.

Interrupting SQL*Plus

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.


Note:

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.


Issuing BS2000 Commands from SQL*Plus

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:

Invoking the BS2000 Editor

You use the SQL*Plus EDIT command to invoke the BS2000 editor:

SQL> EDIT 

This command:

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.


Note:

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.

SQL> EDIT login[.SQL]

Refer to the SQL*Plus User's Guide for more details about the SQL EDIT command.

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').

Spooling SQL*Plus Output

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:

/PRINT tempfile,ERASE 

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:

PRINTPAR=options 

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.

Using SQL*Plus Symbols

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.

SQL*Plus Timing Displays

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. 

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 userid:

SQL> START filename
 

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:

SQLPATH=PRIVATE;$GLOBAL 

when you issue the following command:

@filename
 

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

  1. filename.SQL

  2. PRIVATE.filename.SQL

  3. $GLOBAL.filename.SQL

For further information on default filename extensions, see the chapter "Getting Started".

SQL*Plus Demonstration Files

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.

SQL*Plus Limits

The limits of several SQL*Plus elements are specified in the SQL*Plus User's Guide. 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


Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index