3
Administering SQL*Plus

• Administering SQL*Plus

• Using SQL*Plus

• Restrictions

Administering SQL*Plus

Setup Files

The setup files for SQL*Plus are glogin.sql, the global setup file that defines the site profile, and login.sql, which defines the user profile. The glogin.sql and login.sql files contain 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

The Site Profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql. SQL*Plus runs this command file when any user starts SQL*Plus. The default Site Profile is placed in $ORACLE_HOME/sqlplus/admin when SQL*Plus is installed. If a Site Profile already exists, it will be overwritten. An existing Site Profile is deleted when SQL*Plus is de-installed.

The User Profile

The User Profile file is login.sql. SQL*Plus runs this command file, after the Site Profile file has run, when any user starts SQL*Plus. SQL*Plus always searches the current directory first for the User Profile. The environment variable SQLPATH may be set to a colon-separated list of directories that SQL*Plus will search for a login.sql file.

For example, if the current directory is /u02/oracle and SQLPATH is set as follows:

/home:/home/oracle:/u01/oracle

SQL*Plus first looks 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. SQL*Plus runs only the first login.sql file found.

Since login.sql is run last, options set in login.sql override those set in glogin.sql.

See Also: Chapter 3 in the SQL*Plus User's Guide and Reference.

The PRODUCT_USER_PROFILE Table

During a [Typical] installation, the PRODUCT_USER_PROFILE table (PUP) is created automatically. The PUP table is used to disable certain SQL and SQL*Plus commands. If you need to recreate this table, run the
$ORACLE_HOME/sqlplus/admin/pupbld.sql script in the SYSTEM schema.

For example:

$ sqlplus system/manager
SQL> @?/sqlplus/admin/pupbld.sql

SQL*Plus will use the value of $ORACLE_HOME wherever "?" appears.

Demonstration Tables

SQL*Plus is shipped with demonstration tables that may be used for testing.

Typical Install

During a [Typical] installation, the user SCOTT and the demonstration tables are created automatically.

Creating Demonstration Tables Manually

Use the SQL script $ORACLE_HOME/sqlplus/demo/demobld.sql 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

Deleting Demonstration Tables

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

Note: Both SQL scripts demobld.sql and demodrop.sql drop the tables EMP, DEPT, BONUS, SALGRADE, and DUMMY. Make sure that no table with any of these names exists in the desired schema prior to running either script, or the table data will be lost.

Help Facility

Typical Install

When you copy a starter database with pre-built datafiles as part of the Typical installation or as an option in Oracle Database Configuration Assistant, the Help Facility is installed automatically.

Database Configuration Assistant

Oracle Database Configuration Assistant gives you the option to create help tables when creating a database.

Installing the Help Facility Manually

The Help Facility may be installed manually using the shell script
$ORACLE_HOME/bin/helpins. Before you run the script, the SYSTEM_PASS environment variable should be set to the SYSTEM schema name and password. For example:

$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins

If the SYSTEM_PASS variable is not set, helpins prompts you for the SYSTEM password and loads the help data into the SYSTEM schema. You can also run $ORACLE_HOME/sqlplus/help/helpbld.sql helpus.sql to install the Help Facility. The system user can run the file helpbld.sql and its argument, helpus.sql, in SQL*Plus to create Help Facility Tables. For example:

$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql

Note: Both the shell script, helpins, and the SQL*Plus script, helpbld.sql, drop any existing Help Facility tables before creating new tables.

You can also run $ORACLE_HOME/sqlplus/help/helpdrop.sql in SQL*Plus to manually drop the Help Facility tables in that schema. For example:

$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpdrop.sql

See Also: Refer to the SQL*Plus User's Guide and Reference.

Using SQL*Plus

Using a System Editor from SQL*Plus

An edit command entered at the SQL*Plus prompt invokes an operating system editor, such as ed, emacs, ned, or vi. Your PATH variable must include the directory of the editor.

When you invoke the editor the current SQL buffer is placed in the editor. When you exit the editor, the changed SQL buffer is returned to SQL*Plus.

You can specify which editor will be invoked by defining the SQL*Plus _editor variable. This variable can be set in glogin.sql, login.sql or entered during a SQL*Plus session.

For example, to set the default editor to vi, enter:

define_editor=vi

If you do not set the _editor variable, then the value of either the EDITOR or VISUAL environment variables is used. If both are set, the EDITOR variable value is used.

When _editor, EDITOR and VISUAL are not specified, the default editor is ed.

When you invoke the editor, SQL*Plus uses a temporary file called afiedt.buf to pass text to the editor. You can rename this file, using the SET EDITFILE command. For example:

SQL>SET EDITFILE/tmp/myfile.sql

SQL*Plus does not delete the temporary file.

Running Operating System Commands from SQL*Plus

The HOST command or an exclamation point (!) as the first character after the SQL*Plus prompt indicates subsequent characters are passed to a sub-shell. The SHELL environment variable sets the shell used to execute operating system commands. The default shell is /bin/sh(sh). If the shell cannot be executed, an error message is displayed.

You can perform operating system commands without leaving SQL*Plus by entering the HOST or (!) commands.

For example, to enter one command, enter:

SQL>! command

where command represents the operating system command you wish to execute. Once the command has executed, control is returned to SQL*Plus.

To execute more than one operating system command, press [Enter] after the [!] or HOST command.

Interrupting SQL*Plus

While running SQL*Plus you can stop the scrolling record display and terminate a SQL statement by pressing [Ctrl]+[c] on Solaris machines.

Using the SPOOL Command

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

Restrictions

Resizing Windows

The default value for SQL*Plus LINESIZE and for PAGESIZE do not automatically adjust for window size.

Return Codes

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.