8
Administering SQL*Plus

This chapter describes how to use and administer SQL*Plus on Oracle9i.

This chapter contains the following topics:

Administering SQL*Plus

This section describes how to administer SQL*Plus.

Using Setup Files

When you start SQL*Plus, it executes the glogin.sql site profile set-up file and then executes the login.sql user profile set-up file.

Using the Site Profile File

The global site profile file is $ora_sqlplus:glogin.sql. If a site profile already exists at this location, it is overwritten when you install SQL*Plus. If SQL*Plus is removed, the site profile file is deleted.

Using the User Profile File

The user profile file is login.sql. SQL*Plus looks for this file in the current directory, and then in the directories you specify using the SQLPATH environment variable. Set this environment variable to a comma-separated list of directories. For example,

define SQLPATH "Disk1:[Path1],Disk2:[Path2]"

SQL*Plus searches these directories for the login.sql file in the order they are listed.

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

See Also:
For more information on 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 ora_sqlplus_demo:pupbld.sql
script in the SYSTEM schema.

For example, enter:

$ sqlplus SYSTEM/MANAGER
SQL> @ora_sqlplus_demo:pupbld.sql

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

$ SQLplus system/manager 
SQL> @ora_sqlplus_demo:demobld.sql

Deleting Demonstration Tables

Use the ora_sqlplus_demo:demodrop.sql script to drop the 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> @ora_sqlplus_demo:demodrop.sql

Note:
Both the demobld.sql and demodrop.sql scripts drop the EMP, DEPT, BONUS, SALGRADE, and DUMMY tables. Before you run the demobld.sql script, make sure that these tables do not exist or are not in use for other purposes.

SQL*Plus Online Help

This section describes how to install and remove the SQL*Plus online help.

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

Installing the SQL*Plus Online Help

There are four ways to install the SQL*Plus online help:

Perform a typical installation

When you copy a starter database with pre-built data files as part of the Typical installation, SQL*Plus automatically installs the SQL*Plus online help.
Use the Database Configuration Assistant

You can use the Oracle Database Configuration Assistant to create help tables when creating a database.
Install the help facility manually using the helpbld.sql SQL script

You can use the ora_sqlplus_demo:helpbld.sql script with the helpus.sql script to manually install the online help. For example, enter:
```
$ set def ORA_sqlplus_demo
$ define/job system_pass "system/manager"
SQL>@hlpbld.sql ora_sqlplus:helpus.sql
```
Note:
The helpbld.sql SQL*Plus script drop existing online help tables before creating new tables.

Removing the SQL*Plus Online Help

You can also run the ora_sqlplus:helpdrop.sql in SQL*Plus to manually drop the online help tables in a schema. For example, enter:

$ sqlplus SYSTEM/MANAGER
SQL> @ora_sqlplus:helpdrop.sql

Using SQL*Plus

This section describes how to use SQL*Plus on Alpha OpenVMS systems.

Using a System Editor from SQL*Plus

If you enter an ED or EDIT command at the SQL*Plus prompt, the system starts an operating system editor, such as edit or EDT. Your PATH variable must include the directory where the editor executable is located.

When you start 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 starts by defining the SQL*Plus _EDITOR variable. You can define this variable in the glogin.sql site profile, the login.sql user profile, or define it during the SQL*Plus session. For example, to set the default editor to EDT, enter:

SQL> DEFINE _EDITOR=EDT

If you do not set the _EDITOR variable, the value of either the EDITOR or the VISUAL environment variable is used. If both environment variables are set, the value of the EDITOR variable value is used. When _EDITOR, EDITOR, and VISUAL are not specified, the default editor is ed.

If you start the editor, SQL*Plus uses the afiedt.buf temporary file to pass text to the editor. You can use the SET EDITFILE command to specify a different file name. For example, enter:

SQL> SET EDITFILE /test15:[tmp]myfile.sql

SQL*Plus does not delete the temporary file.

Running Operating System Commands from SQL*Plus

Using the HOST command or a dollar sign ($) as the first character after the SQL*Plus prompt causes subsequent characters to be passed to a sub-shell.

To return to SQL*Plus, enter LOGOUT.

For example, to enter one command, enter:

SQL> Host or $

In the preceding example, Host represents the operating system command you want to execute.

To enter multiple operating system commands from SQL*Plus, enter the Host or $ command, and press return. SQL*Plus returns you to the operating system prompt.

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 file extension of files generated by the SPOOL command is .lis. To change this extension, specify a spool file containing a period (.). For example, enter:

SQL> SPOOL query.txt

SQL*Plus Restrictions

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

OpenVMS return codes use only one byte, that is not enough space to return an Oracle error code. The range for a return code is 0 to 255.

8 Administering SQL*Plus