Oracle SQL*Plus FAQ

$Date: 29-Apr-2000 $
$Revision: 1.61 $
$Author: Frank Naudé $

Topics

SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first releases of Oracle, its interface was extremely primitive and all but user friendly.

• SQL*Plus commands - for formatting query results, setting options, and editing and storing SQL commands and PL/SQL blocks. This FAQ is dedicated to SQL*Plus commands, eg.
  SHOW USER

• SQL commands - for more information see the Oracle SQL FAQ. Eg:
  SELECT * FROM TAB;

• PL/SQL blocks - for more information see the Oracle PLSQL FAQ. Eg:

  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World!');
  END;
  /

ACCEPT	Get input from the user
DEFINE	Declare a variable (short: DEF)
DESCRIBE	Lists the attributes of tables and other objects (short: DESC)
EDIT	Places you in an editor so you can edit a SQL command (short: ED)
EXIT or QUIT	Disconnect from the database and terminate SQL*Plus
GET	Retrieves a SQL file and places it into the SQL buffer
HOST	Issue a operating system command (short: !)
LIST	Displays the last command executed/ command in the SQL buffer (short: L)
PROMPT	Display a text string on the screen. Eg prompt Hello World!!!
RUN	List and Run the command stored in the SQL buffer (short: /)
SAVE	Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql
SET	Modify the SQL*Plus environment eg. SET PAGESIZE 23
SHOW	Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
SPOOL	Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst
START	Run a SQL script file (short: @)

                SQL> STORE SET filename REPLACE
                SQL> (do whatever you like)
                SQL> @filename

In the prehistoric days when SQL*Plus was called UFI, the file name was "ufiedt.buf", short for UFI editing buffer.

When new features were added to UFI, it was the initially named Advanced UFI and the filename was changed to "aufiedt.buf" and then to "afiedt.buf". They presumably needed to keep the name short for compatibility with some of the odd operating systems that Oracle supported in those days.

The name "Advanced UFI" was never used officially, as the name was changed to SQL*Plus before this version was released.

You can overwrite the default edit save file name like this:

                SET EDITFILE "afiedt.buf"

A single @ symbol runs the script in your current directory, or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH.

@@ will start a sqlplus script that is in the same directory as the script that called it (relative to the directory of the current script). This is normally used for nested command files.

"&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a value it will use that value every time the variable is referenced.

Eg: SQL> SELECT * FROM TAB WHERE TNAME LIKE '%&TABLE_NAME.%';

To use the help facility, type HELP followed by the command you need to learn more about. For example, to get help on the SELECT statement, type:

        HELP SELECT

cd $ORACLE_HOME/sqlplus/admin/help
sqlplus system/manager @helptbl
sqlplus system/manager @helpindx
sqlldr system/manager control=plushelp.ctl
sqlldr system/manager control=sqlhelp.ctl
sqlldr system/manager control=plshelp.ctl

HELP.SQL

select info
from   system.help
where  upper(topic)=upper('&1')
/

        SET ESCAPE ON
        SET ESCAPE "\"
        SELECT 'You \& me' FROM DUAL;

        SET DEFINE ?
        SELECT 'You & me' FROM DUAL;

SQL> WHENEVER OSERROR EXIT 9
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

1. Run the PLUSTRCE.SQL script from the SYS database user. This script is located the in $ORACLE_HOME/sqlplus/admin.

2. Create a PLAN_TABLE using the UTLXPLAN.SQL script. This script is in $ORACLE_HOME/rdbms/admin.

3. Use the "SET AUTOTRACE ON" command to trace SQL execution. This will print the result of your query, an explain plan and high level trace information. Look at this example:

   SQL> set autotrace on
   SQL> select * from dual;
   
   D
   -
   X
   
   Execution Plan
   ----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE
      1    0   TABLE ACCESS (FULL) OF 'DUAL'
   
   Statistics
   ----------------------------------------------------------
             0  recursive calls
             2  db block gets
             1  consistent gets
             0  physical reads
             0  redo size
           181  bytes sent via SQL*Net to client
           256  bytes received via SQL*Net from client
             3  SQL*Net roundtrips to/from client
             0  sorts (memory)
             0  sorts (disk)
             1  rows processed
   

Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

This messages will stop appearing when you create the PRODUCT_USER_PROFILE table in the SYSTEM schema. This is performed by the PUPBLD.SQL script.

Go to the $ORACLE_HOME/sqlplus/admin directory, connect as SYSTEM and run @PUPBLD from the sqlprompt.

Eg. to disable all users whose names starts with OPS$ from executing the CONNECT command:

SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'OPS$%', 'CONNECT', NULL, NULL, 'DISABLED', NULL, NULL);

        SET ECHO OFF
        SET NEWPAGE 0
        SET SPACE 0
        SET PAGESIZE 0
        SET FEEDBACK OFF
        SET HEADING OFF
        SET TRIMSPOOL ON

      SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON

	sqlplus username/password @cmdfile.sql var1 var2 var3

Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:

	sqlplus scott/tiger @x.sql  '"test parameter"' dual

Where x.sql consists of:

	select '&1' from &2;
	exit;

• ORACLE SQL*Plus: An Introduction and Tutorial

• Oracle: SQL*Plus Tutorial