|Oracle8 Administrator's Reference
Release 8.0.5 for Intel-LINUX
The setup files for SQL*Plus are
glogin.sql, the global setup file which defines the site profile, and
login.sql, which defines the user profile. The
login.sql files contain either SQL statements or SQL*Plus commands that you choose to execute at the beginning of each SQL*Plus session. When you invoke SQL*Plus,
glogin.sql is read first, followed by
The Site Profile file is
$ORACLE_HOME/sqlplus/admin/glogin.sql. SQL*Plus executes this command file whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The default Site Profile is placed in
$ORACLE_HOME/sqlplus/admin whenever SQL*Plus is installed. If a Site Profile already exists, it will be overwritten. An existing Site Profile is deleted whenever SQL*Plus is de-installed.
The User Profile file is
login.sql. SQL*Plus attempts to execute this command file whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The User Profile is run after the Site Profile. SQL*Plus always searches the current directory for the User Profile. The environment variable SQLPATH may be set to a colon-separated list of directories that SQL*Plus will search in order.
For example, if the current directory is
/u02/oracle and SQLPATH is set as follows:
SQL*Plus will first look for
login.sql in the current directory
/u02/oracle. If it is not found there, SQL*Plus will then look in
Here is a sample
set echo off set feedback 4 set pause on set pause "PLEASE PRESS RETURN TO CONTINUE" set message on set echo on
The SQL script
$ORACLE_HOME/sqlplus/admin/pupbld.sql may be run as the user SYSTEM to create the Product and User Profile tables.
$ORACLE_HOME/sqlplus/admin/pupbld.sql may also be run using the shell script
$ORACLE_HOME/bin/pupbld. To use this script, the environment variables ORACLE_HOME and SYSTEM_PASS must be set. SYSTEM_PASS must be set to the SYSTEM's username and password. For example:
% setenv SYSTEM_PASS SYSTEM/manager % pupbld Installing product user profile tables... Product user profile tables installed.
pupbld.sql will only be run by the Installer during SQL*Plus installation if Create Database Objects was selected.
SQL*Plus is shipped with demonstration tables that may be used for testing.
If using Default Install and Create Database Objects, the user SCOTT and the demonstration tables will be created automatically.
When installing SQL*Plus using Custom Install, if Create Database Objects is selected and you answer 'Yes' to the prompt "Would you like to load the SQL*Plus Demo Tables?", the Installer will create the user SCOTT with the password TIGER and create the demonstration tables.
The SQL script
$ORACLE_HOME/sqlplus/demo/demobld.sql is used to create the demonstration tables. The file
demobld.sql, may be run in SQL*Plus as any user to create the demonstration tables in that schema. For example:
$ORACLE_HOME/sqlplus/demo/demobld.sql may also be run using the shell script
$ORACLE_HOME/bin/demobld as follows:
The SQL script
$ORACLE_HOME/sqlplus/demo/demodrop.sql is used to drop the demonstration tables. The file
demodrop.sql may be run in SQL*Plus as any user to drop the demonstration tables from that user's schema. For example:
$ORACLE_HOME/sqlplus/demo/demodrop.sql may also be run using the shell script
$ORACLE_HOME/bin/demodrop as follows:
Both SQL scripts
If using Default Install and Create Database Objects, the Help Facility is installed automatically.
When installing SQL*Plus, if Create Database Objects is selected and you answer 'Yes' to the prompt "Would you like to load the SQL*Plus Help Facility?", the Installer will create the Help Facility.
Installing the Help Facility Manually
The Help Facility may be installed manually using the shell script
$ORACLE_HOME/bin/helpins. To use this script, the environment variables ORACLE_HOME and SYSTEM_PASS must be set. SYSTEM_PASS must be set to SYSTEM's username and password. For example:
$ setenv SYSTEM_PASS SYSTEM/manager $ helpins SQL*Loader: Release 220.127.116.11.0 - Production (c) Copyright 1998 Oracle Corporation. All rights reserved. Commit point reached - logical record count 828 SQL*Loader: Release 18.104.22.168.0 - Production (c) Copyright 1998 Oracle Corporation. All rights reserved. Commit point reached - logical record count 1024 Commit point reached - logical record count 1207 SQL*Loader: Release 22.214.171.124.0 - Production (c) Copyright 1998 Oracle Corporation. All rights reserved. Commit point reached - logical record count 1024 Commit point reached - logical record count 1304 Commit point reached - logical record count 2328 Commit point reached - logical record count 2724 Commit point reached - logical record count 2835
Refer to the SQL*Plus User's Guide and Reference, and the
edit command entered at the SQL*Plus prompt calls a default operating system editor, such as
ed, emacs, ned, or
vi. Your PATH variable must include the directory of the editor.
The global default editor is usually set by the DBA in
glogin.sql using the SQL*Plus
_editor option. Override this setting by specifying an editor in
login.sql. Both files are read by SQL*Plus at startup, the local file taking precedence. The
_editor option can also be set during a SQL*Plus session, overriding the setting in either file.
_editor option is not set, the EDITOR and VISUAL environment variables specify the SQL*Plus editor. These variables are not set in
login.sql. They are set in a user startup file, or at the system prompt. If both are set, the EDITOR variable is used.
SQL*Plus searches for the default editor in this order:
When none of these values are set, SQL*Plus uses
Set the SQL*Plus
_editor option by adding the following line to the
where editor_name is a LINUX editor.
For the Bourne or Korn shell, set the default editor with an environment variable by entering:
For the C shell, set the default editor with an environment variable by entering:
Environment variable syntax is explained in Table 4-1.
the EDITOR or VISUAL environment variable
the LINUX editor (for example,
If you call the system editor, the current SQL buffer is placed in the edit buffer and all statements available to the editor can change the SQL statement. SQL*Plus uses the
afiedt.buf temporary file. When you exit the editor, the changed SQL buffer is returned to SQL*Plus.
An exclamation point (!) in the first position after the SQL*Plus prompt indicates subsequent character strings are passed to a sub-shell. The SHELL environment variable selects the shell you use to execute operating system commands. The default shell is
/bin/sh (sh). If the shell cannot be executed, an error message is displayed.
Use the following SQL*Plus commands to perform specific tasks:
While running SQL*Plus:
The default filename extension for files generated by the SPOOL command is .
lst. To change the extension, specify a spool file containing a period (.).
COPY command in SQL*Plus is supported without restrictions on different machines running the same version of the operating system.
COPY may also work between machines running different versions of the operating system. If
COPY fails, test the connection using
ftp. Restrictions in vendor-supplied networking software may prevent
COPY from functioning properly between systems.
COPY does not function between systems, create a database link to the system and user ID indicating the table you want to copy. To do this, enter:
This selects the rows and columns from the original table on the remote system and enters them in the new table on the local system.
The default value for SQL*Plus LINESIZE is 80 and for PAGESIZE is 25. These variables do not automatically adjust for window size.
LINUX return codes use only one byte, which is not enough space to return an Oracle error code. The range for a return code is 0 to 255.