3
Administering SQL*Plus

This chapter describes the following features of SQL*Plus:

• Administering SQL*Plus

• Using SQL*Plus

• Restrictions of SQL*Plus

Administering SQL*Plus

This section describes how to administer SQL*Plus.

Using Setup Files

When you invoke SQL*Plus, it executes the glogin.sql site profile setup file and then executes the login.sql user profile setup file.

Using the Site Profile File

The global site profile file is $ORACLE_HOME/sqlplus/admin/glogin.sql. The default site profile is placed in $ORACLE_HOME/sqlplus/admin when SQL*Plus is installed. If a site profile already exists, it is overwritten. If SQL*Plus is de-installed, the site profile file is deleted.

Using the User Profile File

The user profile file is login.sql. SQL*Plus looks in the current directory, and then in the directories you specify until it finds login.sql. You can specify the directories to search by setting the SQLPATH environment variable to a colon-separated list of directories.

For example, if the current directory is /u02/oracle and SQLPATH is set to /home:/home/oracle:/u01/oracle, SQL*Plus looks for login.sql in the following order:

1. /u02/oracle (the current directory)

2. /home

3. /home/oracle

4. /u01/oracle

The options set in login.sql override those set in glogin.sql.

See Also: For more information about profile files, see the SQL*Plus User's Guide and Reference.

Using the PRODUCT_USER_PROFILE Table

During a typical installation, the PRODUCT_USER_PROFILE table is created automatically. This table is used to disable the SQL and SQL*Plus commands you specify. To recreate this table, run the
$ORACLE_HOME/sqlplus/admin/pupbld.sql script in the SYSTEM schema.

For example, enter:

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

SQL*Plus uses the value of $ORACLE_HOME wherever a question mark ( ? ) appears.

Using Demonstration Tables

SQL*Plus is shipped with demonstration tables that you can use for testing.

Performing a Typical Installation

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. In SQL*Plus, you can use any user name to run demobld.sql file to create the demonstration tables in a schema. For example, enter:

$ sqlplus scott/tiger 
SQL> @?/sqlplus/demo/demobld.sql 

You can also use the $ORACLE_HOME/bin/demobld shell script to run $ORACLE_HOME/sqlplus/demo/demobld.sql by entering:

$ demobld scott tiger

Deleting Demonstration Tables

You can use the $ORACLE_HOME/sqlplus/demo/demodrop.sql SQL script to drop demonstration tables. In SQL*Plus, you can use any user name to drop the demonstration tables in the user's schema. For example, enter:

$ sqlplus scott/tiger 
SQL> @?/sqlplus/demo/demodrop.sql

You can also use the $ORACLE_HOME/bin/demodrop shell script to run $ORACLE_HOME/sqlplus/demo/demodrop.sql by entering:

$ demodrop scott tiger

Note: Both the demobld.sql and demodrop.sql scripts drop the EMP, DEPT, BONUS, SALGRADE, and DUMMY. tables. Prior to running either script, make sure that no table with one of these names exists in the desired schema, or the table data is lost.

Using the Help Facility

This section describes how to use the help facility.

Performing a Typical Installation

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, SQL*Plus automatically installs the Help Facility.

Using the Database Configuration Assistant

You can use the Oracle Database Configuration Assistant to create help tables when creating a database.

Installing the Help Facility Manually

You can use the $ORACLE_HOME/bin/helpins shell script to manually install the Help Facility. Before you run the script, set the SYSTEM_PASS environment variable to the SYSTEM schema name and password. For example, enter:

$ 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. In SQL*Plus, the system user can run the helpbld.sql file and its argument, helpus.sql, to create help facility tables. For example, enter:

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

Note: Both the helpins shell script and the helpbld.sql SQL*Plus script drop 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 a schema. For example, enter:

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

See Also: For more information about the help facility, see the SQL*Plus User's Guide and Reference.

Using SQL*Plus

This section describes how to use SQL*Plus.

Using a System Editor from SQL*Plus

If you enter an ed or edit command at the SQL*Plus prompt, the system 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, in login.sql, or entered during a SQL*Plus session. For example, to set the default editor to vi, enter:

SQL> DEFINE _editor=vi

If you do not set the _editor variable, the value of either the EDITOR or VISUAL environment variable 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.

If you invoke the editor, SQL*Plus uses the afiedt.buf temporary file to pass text to the editor. You can use SET EDITFILE to rename this file. For example, enter:

SQL> SET EDITFILE /tmp/myfile.sql

SQL*Plus does not delete the temporary file.

Running Operating System Commands from SQL*Plus

Using the HOST command or an exclamation point (!) as the first character after the SQL*Plus prompts 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 ! command. For example, to enter one command, enter:

SQL>! command

where command  represents the operating system command you want to execute.

To execute more than one operating system command, press [Enter] after the HOST or ! command. After the command executes, control returns to SQL*Plus.

Interrupting SQL*Plus

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

Using the SPOOL Command

The default extension name of files generated by the SPOOL command is .lst. To change an extension, specify a spool file containing a period (.). For example, enter:

SQL> SPOOL query.lis

Restrictions of SQL*Plus

This section describes SQL*Plus restrictions.

Resizing Windows

The default values for SQL*Plus LINESIZE and 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.