4 Administering 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 PRODUCT_USER_PROFILE Table

Oracle Database provides the PRODUCT_USER_PROFILE table that you can use to disable the SQL and SQL*Plus commands that you specify. This table is created automatically when you choose an installation type that installs a preconfigured database.

To recreate the PRODUCT_USER_PROFILE table, run the $ORACLE_HOME/sqlplus/admin/pupbld.sql script in the SYSTEM schema. For example, enter the following commands, where SYSTEM_PASSWORD is the password of the SYSTEM user:

$ sqlplus SYSTEM/SYSTEM_PASSWORD
SQL> @?/sqlplus/admin/pupbld.sql

You can also recreate the PRODUCT_USER_PROFILE table manually in the SYSTEM schema by using the $ORACLE_HOME/bin/pupbld shell script. This script prompts for the SYSTEM password. If you need to run the pupbld script without interaction, set the SYSTEM_PASS environment variable to the SYSTEM user name and password.

Using Demonstration Tables

Oracle Database provides demonstration tables that you can use for testing. To install the demonstration tables in a database, you must choose an installation type that installs a preconfigured database.

SQL*Plus Command-Line Help

This section describes how to install and remove the SQL*Plus command-line help.

Installing the SQL*Plus Command-Line Help

There are three ways to install the SQL*Plus command-line help:

• Complete an installation that installs a preconfigured database.

  When you install a preconfigured database as part of an installation, SQL*Plus automatically installs the SQL*Plus command-line help in the SYSTEM schema.

• Install the command-line help manually in the SYSTEM schema using the $ORACLE_HOME/bin/helpins shell script.

  The helpins script prompts for the SYSTEM password. If you need to run this script without interaction, set the SYSTEM_PASS environment variable to the SYSTEM user name and password. For example:

  • For the Bourne, Bash, or Korn shell, enter the following command, where system_password is the password of the SYSTEM user:

    $ SYSTEM_PASS=SYSTEM/system_password; export SYSTEM_PASS
    
    

  • For the C or tcsh shell, enter the following command, where system_password is the password of the SYSTEM user:

    % setenv SYSTEM_PASS SYSTEM/system_password
    
    

• Install the command-line help manually in the SYSTEM schema using the $ORACLE_HOME/sqlplus/admin/help/helpbld.sql script.

  For example, enter the following commands, where system_password is the password of the SYSTEM user:

  $ sqlplus SYSTEM/system_password
  SQL> @?/sqlplus/admin/help/helpbld.sql ?/sqlplus/admin/help helpus.sql
  

  
  

  Note: Both the helpins shell script and the helpbld.sql script drop existing command-line help tables before creating new tables.

  
  

Removing the SQL*Plus Command-Line Help

To manually drop the SQL*Plus command-line help tables from the SYSTEM schema, run the $ORACLE_HOME/sqlplus/admin/help/helpdrop.sql script. For example, enter the following commands, where system_password is the password of the SYSTEM user:

$ sqlplus SYSTEM/system_password
SQL> @?/sqlplus/admin/help/helpdrop.sql

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 ed, emacs, ned, or vi. However, the PATH environment 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 vi, enter:

SQL> DEFINE _EDITOR=vi

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 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 /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 mark (!) as the first character after the SQL*Plus prompt causes subsequent characters to be passed to a sub-shell. The SHELL environment variable sets the shell used to run operating system commands. The default shell is the Bourne shell (/bin/sh). If the shell cannot be run, SQL*Plus displays an error message.

To return to SQL*Plus, enter the exit command or press Ctrl+d.

For example, to run one command, enter:

SQL> ! command

In this example, command  represents the operating system command that you want to run.

To run multiple operating system commands from SQL*Plus, enter the HOST or ! command then 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 .lst. To change this extension, specify a spool file containing a period (.), for example:

SQL> SPOOL query.txt

Resizing Windows

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

Hiding Your Password

If you set the SYSTEM_PASS environment variable to the user name and password of the SYSTEM user, the output from the ps command might display this information. To prevent unauthorized access, enter the SYSTEM password only when prompted by SQL*Plus.

If you want to automatically run a script, consider using an authentication method that does not require you to store a password, for example, externally authenticated logins to Oracle Database. If you have a low security environment, you might consider using UNIX pipes in script files to pass a password to SQL*Plus, for example:

$ echo system_password | sqlplus SYSTEM @MYSCRIPT

Alternatively, enter the following lines at the command prompt:

$ sqlplus <<EOF
SYSTEM/SYSTEM_PASSWORD
SELECT ...
EXIT
EOF

In the preceding examples, system_password is the password of the SYSTEM user.