Release 8.0.4
A56101-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Reference for Sun SPARC Solaris 2.x Release 8.0.4 A56101-01 |
|
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 glogin.sql and 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 login.sql.
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:
% echo $SQLPATH /home:/home/oracle:/u01/oracle
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 /home, /home/oracle, and /u01/oracle, respectively.
Here is a sample login.sql file:
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 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:
% sqlplus scott/tiger SQL> @?/sqlplus/demo/demobld.sql
$ORACLE_HOME/sqlplus/demo/demobld.sql may also be run using the shell script $ORACLE_HOME/bin/demobld as follows:
% demobld scott tiger
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:
% sqlplus scott/tiger SQL> @?/sqlplus/demo/demodrop.sql
$ORACLE_HOME/sqlplus/demo/demodrop.sql may also be run using the shell script $ORACLE_HOME/bin/demodrop as follows:
% demodrop scott tiger
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 8.0.4.0.0 - Production (c) Copyright 1997 Oracle Corporation. All rights reserved. Commit point reached - logical record count 828 SQL*Loader: Release 8.0.4.0.0 - Production (c) Copyright 1997 Oracle Corporation. All rights reserved. Commit point reached - logical record count 1024 Commit point reached - logical record count 1207 SQL*Loader: Release 8.0.4.0.0 - Production (c) Copyright 1997 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
| See Also:
Refer to the SQL*Plus User's Guide and Reference, and the README file, |
An ed or 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.
If the _editor option is not set, the EDITOR and VISUAL environment variables specify the SQL*Plus editor. These variables are not set in glogin.sql or 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:
_editor variable during a SQL*Plus session.
_editor variable in login.sql.
_editor variable in glogin.sql.
When none of these values are set, SQL*Plus uses ed.
Set the SQL*Plus _editor option by adding the following line to the login.sql file:
define _editor=editor_name
where editor_name is a UNIX editor.
For the Bourne or Korn shell, set the default editor with an environment variable by entering:
$ UNIX_VAR=editor_name; export UNIX_VAR
For the C shell, set the default editor with an environment variable by entering:
Environment variable syntax is explained in Table 4-1.
|
UNIX_VAR |
the EDITOR or VISUAL environment variable |
|
editor_name |
the UNIX 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 (.).
For example:
SQL> SPOOL query.lis
The 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 rcp or ftp. Restrictions in vendor-supplied networking software may prevent rcp, ftp, or COPY from functioning properly between systems.
| Note:
The |
If 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:
SQL> create table newtable as \ (SELECT * FROM table@database_link_name)
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.
UNIX 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.
