SQL*Plus FAQ

SQL*Plus FAQ:

What is SQL*Plus and where does it come from?

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server software. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.

If you are familiar with other databases, sqlplus is equivalent to:

• "sql" in Ingres,
• "isql" in Sybase and SQL Server,
• "sqlcmd" in Microsoft SQL Server,
• "db2" in IBM DB2,
• "psql" in PostgreSQL, and
• "mysql" in MySQL.

SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first Oracle releases up to Oracle 4. The UFI interface was extremely primitive and, in today's terms, anything but user friendly. If a statement was entered incorrectly, UFI issued an error and rolled back the entire transaction.

How does one use the SQL*Plus utility?

Start using SQL*Plus by executing the "sqlplus" command-line utility from the $ORACLE_HOME/bin directory. Some of the command line options:

userid/password@db -- Connection details
/nolog             -- Do not login to Oracle. You will need to do it yourself.
-s or -silent      -- start sqlplus in silent mode. Not recommended for beginners!
@myscript          -- Start executing script called "myscript.sql"

Look at this example session:

sqlplus /nolog
SQL> connect scott/tiger
SQL> select * from tab;
SQL> disconnect
SQL> exit

Please note that one must prepare the environment before starting sqlplus.

Linux/ Unix example:

$ . oraenv
ORACLE_SID = [orcl] ? orcl
$ sqlplus scott/tiger

Windows Example:

Click on "Start" -> "Run" and enter "cmd"
C:> set ORACLE_SID=orcl
C:> sqlplus scott/tiger

or...

C:> sqlplus scott/tiger@orcl

What commands can be executed from SQL*Plus?

One can enter three kinds of commands from the SQL*Plus command prompt:

SQL*Plus commands

SQL*Plus commands are used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on. SQL*Plus commands do not interact with the database. These commands do not have to be terminated with a semicolon (;) (as is the case with SQL commands). Examples:

SQL> CLEAR SCREEN
SQL> SHOW USER
SQL> SET PAGESIZE 100
SQL> START myscrip.sql

SQL commands

For more information see the Oracle SQL FAQ. Eg:

SQL> SELECT * FROM user_tables;

PL/SQL blocks

For more information see the Oracle PL/SQL FAQ. Eg:

BEGIN
  dbms_output.put_line('Hello World!');
END;
/

What are the basic SQL*Plus commands?

Here is a list of some of the most frequently used SQL*Plus commands:

• 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 an 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: @)

What is AFIEDT.BUF?

AFIEDT.BUF is the SQL*Plus default edit save file. When you issue the command "ed" or "edit" without arguments, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.

In the prehistoric days when SQL*Plus was called UFI (User Friendly Interface) this file was named "ufiedt.buf", short for UFI editing buffer. When new features were added to UFI, it was 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's name like this:

SET EDITFILE "afiedt.buf"

One can edit SQL scripts and the command buffer (the last command entered) with the EDIT (or ED) command. However, sometimes one needs to select an editor before using this command. Examples:

Use the Unix/Linux vi-editor:

DEFINE _EDITOR=vi

Use the Notepad on Windows:

DEFINE _EDITOR=notepad

Use the gVim vi-editor on Windows and return afiedt.buf back to SQL*Plus when done:

DEFINE _EDITOR="gvim -f"

TIP: Add this command in your login.sql or glogin.sql scripts so it executes every time you start sqlplus.

How does one enable the SQL*Plus HELP facility?

To enable HELP for SQl*Plus, run the supplied "helpins" script in $ORACLE_HOME/bin. The "helpins" command will prompt you for the SYSTEM password and load the help data into the SYSTEM schema.

Alternatively you can load the help facility manually like this:

cd $ORACLE_HOME/sqlplus/admin/help
sqlplus system/manager @helpdrop.sql        # Drop the HELP table
sqlplus system/manager @hlpbld.sql helpus.sql # Create the HELP table and load the data

If the HELP command is not supported on your operating system, you can access the help table with a simple script like this (let's call it help.sql):

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

Whenever you need help, you can now run the help.sql script:

@help SELECT

What is the difference between ? and HELP?

There is no difference. Both "?" and HELP will read the SYSTEM.HELP table (if available) and shows help text on the screen.

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

One can also extend the help system by inserting information into the HELP table. Look at this example:

SQL> insert into help values ('MYTOPIC', 1, 'Detail line 1');
1 row created.

SQL> insert into help values ('MYTOPIC', 2, 'Detail line 2');
1 row created.

SQL> help MYTOPIC
Detail line 1
Detail line 2

SQL> ? MYTOPIC
Detail line 1
Detail line 2

What is the difference between @ and @@?

The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.

SQL> @myscript.sql

A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in your 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. This technique is commonly used by scripts that call subscripts in the ?/rdbms/admin directory. The @@ reference does not support relative directory references such as @@dir/file.sql or @@./file.sql.

What is the difference between & and &&?

"&" is used to create a temporary substitution variable that will prompt you for a value every time it is referenced. Example:

SQL> SELECT sal FROM emp WHERE ename LIKE '&NAME';
Enter value for name: SCOTT
old   1: SELECT sal FROM emp WHERE ename LIKE '&NAME'
new   1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'
       SAL
----------
      3000

SQL> /
Enter value for name: SCOTT
old   1: SELECT sal FROM emp WHERE ename LIKE '&NAME'
new   1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'
       SAL
----------
      3000

"&&" is used to create a permanent substitution variable. Once you have entered a value (defined the variable) its value will used every time the variable is referenced. Example:

SQL> SELECT sal FROM emp WHERE ename LIKE '&&NAME';
Enter value for name: SCOTT
old   1: SELECT sal FROM emp WHERE ename LIKE '&&NAME'
new   1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'

       SAL
----------
      3000

SQL> /
old   1: SELECT sal FROM emp WHERE ename LIKE '&&NAME'
new   1: SELECT sal FROM emp WHERE ename LIKE 'SCOTT'
       SAL
----------
      3000

The "&&" will actually define the variable similarly to what the DEFINE command or OLD_VALUE/ NEW_VALUE clauses of a COLUMN statement would have done.

SQL> define
DEFINE NAME            = "SCOTT" (CHAR)

What is the difference between ! and HOST?

Both "!" and "HOST" will execute operating system commands as child processes of SQL*Plus. The difference is that "HOST" will perform variable substitution (& and && symbols), whereas "!" will not. Examples:

SQL> ! whoami
oracle

SQL> DEFINE cmd="whoami"
SQL> HOST &&cmd
oracle

Note: use "$" under OS/390, VMS, and Windows environments, not "!".

Can one run commands when SQL*Plus starts up?

When SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed.

Thereafter, sqlplus will try to find a local login script called login.sql in the directory where you start sqlplus from, alternatively the directories listed in the SQLPATH environment variable. When found, sqlplus will execute it.

NOTE: From Oracle 10g SQL*Plus will attempt to execute glogin.sql and login.sql after each successful connection. This is handy if you want to change the sqlprompt to include the current user. Here is an example (g)login.sql file:

prompt Loading login.sql file...
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"
define _editor=vi

A bit of history: when SQL*Plus was still called UFI, this file was called login.ufi (located in $ORACLE_HOME/demo).

Can one set the SQL*Plus command prompt to something more useful?

One can change the default 'SQL> ' prompt by changing the SQLPROMPT setting. For example:

SET SQLPROMPT 'Enter SQLPlus Command> '

The following example scripts can be used to include the connected username and database name into the prompt:

For Oracle 10g and above:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "

Pre 10g:

undefine usr db
col usr new_value usr
col db  new_value db

set termout off
select lower(user) usr,
       substr(global_name, 1, instr(global_name, '.')-1) db
  from   global_name
/
set termout on

set sqlprompt '&&usr.@&&db.> '

NOTE: For the above example it might be better to get the database name from v$database. However, not all users do have access to the v$database view.

How does one disable interactive prompting in SQL*Plus?

If you run a script that contains "&" symbols, SQL*Plus thinks that you want to prompt the user for a value. Some clients allow one to escape the ampersand character with a backslash, however, that doesn't work from SQL*Plus. Here are a couple of solutions:

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

or

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

Note: You can disable substitution variable prompting altogether by issuing the SET DEFINE OFF or SET SCAN OFF command.

How does one trap errors in SQL*Plus?

Use the "WHENEVER SQLERROR ..." command to trap SQL and PL/SQL errors, and the "WHENEVER OSERROR ..." to trap operating system errors. Eg:

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

How does one trace (and explain) SQL statements from SQL*Plus?

Method 1: Autotrace Facility

When the AUTOTRACE setting is enabled, SQL*Plus will print an EXPLAIN PLAN and execution statistics after each SQL statement. Look at this example:

SQL> set autotrace on
SQL> select * from dept where deptno = 40;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
   2    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Follow these steps to enable this feature:

• Run the PLUSTRCE.SQL script from the SYS database user. This script is located in the $ORACLE_HOME/sqlplus/admin directory.
• Create a PLAN_TABLE table by running the UTLXPLAN.SQL script. This script is in $ORACLE_HOME/rdbms/admin.
• Use the "SET AUTOTRACE ON" command to trace SQL execution. This will print an explain plan and high level trace information after your query results.

Method 2: DBMS_XPLAN Package

SQL> EXPLAIN PLAN FOR select * from dept where deptno = 40;
Explained.

SQL> set linesize 132

SQL> SELECT * FROM TABLE( dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2852011669

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=40)

14 rows selected.

How can one prevent SQL*Plus connection warning messages?

When I go to SQl*Plus, I get the following errors:

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.SQL from the sql prompt.

Can one prevent users from executing devious commands?

Yes, command authorization is verified against the SYSTEM.PRODUCT_USER_PROFILE table. This table is created by the PUPBLD.SQL script. Note that this table is not used when someone signs on as user SYSTEM.

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);

Example implementation:

CONNECT / AS SYSDBA
DELETE FROM system.product_user_profile;
INSERT INTO system.product_user_profile(product, userid, attribute, char_value) VALUES ('SQL*Plus','%','SHUTDOWN','DISABLED');
INSERT INTO system.product_user_profile(product, userid, attribute, char_value) VALUES ('SQL*Plus','%','STARTUP','DISABLED');
INSERT INTO system.product_user_profile(product, userid, attribute, char_value) VALUES ('SQL*Plus','%','RECOVER','DISABLED');
INSERT INTO system.product_user_profile(product, userid, attribute, char_value) VALUES ('SQL*Plus','%','ARCHIVE LOG','DISABLED');
INSERT INTO system.product_user_profile(product, userid, attribute, char_value) VALUES ('SQL*Plus','%','NOAUDIT','DISABLED');
SELECT product, userid, attribute, char_value FROM system.product_user_profile;

How does one restore session state in SQL*Plus?

Use the "STORE SET" command to write current settings (SHOW ALL) to a file. This file can later be executed to restore all settings. Look at the following example (Oracle8 and above):

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

How does one disable SQL*Plus formatting?

SQL*Plus tries to format data from the database into a human friendly format. This formatting can be disabled by issuing the following SET commands:

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

These settings can also be abbreviated and entered on one line, eg.:

SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF

You may also want to run the "show all" command to display all the other sqlplus settings that can be changed.

Can one pass operating system parameters to SQL*Plus?

One can pass operating system variables to sqlplus using this syntax:

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 5;

Example passing the Windows User's Temp Path Location to sqlplus:

sqlplus username/password @cmdfile.sql %TEMP%

within @cmdfile.sql

SPOOL &1\myscript.log
-- Your script commands
SPOOL OFF

Something's wrong with my CLOB/ NCLOB/ LONG column. Where is my data?

SQL*Plus by default only shows the first 80 bytes of any LONG, CLOB and NCLOB datatypes. The data is there, but since sqlplus is a command-line tool it tries not to print out too much data. You can override this to tell sqlplus exactly how many bytes you want to see:

SET LONG 32000
SELECT text FROM user_views WHERE rownum = 1;

How does one copy data from one database to another in SQL*Plus?

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example:

SQL> COPY FROM scott/tiger@db1 TO scott/tiger@db2 INSERT mytable USING select * from mytable;

From SQL*Plus help:

COPY
----

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
           {APPEND|CREATE|INSERT|REPLACE} destination_table
           [(column, column, column, ...)] USING query

where database has the following syntax:
    username[/password]@connect_identifier

Note that this command is no more enhanced since Oracle 8.0 and may be declared as obsolete in a future version (but is still there in 11gR2) but there is no replacement to easily copy tables with LONG column (LONG datatype is obsolete).

Can one generate HTML reports from SQL*Plus?

One can generate static HTML pages from SQL*Plus (8.1.6 and above) by setting the MARKUP option to HTML ON. This can be done by specifying -MARKUP "HTML ON" from command line, or with the "SET MARKUP HTML ON" command. Look at this example SQL Script:

set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off

The spooled index.html file should look like this:

TNAME	TABTYPE	CLUSTERID
BONUS	TABLE
DEPT	TABLE
EMP	TABLE

Note: You can deploy this file on your web site or edit it in an HTML editor (like FrontPage or Dreamweaver). Another good idea is to develop a CSS to present the data more elegantly. One can also embed HTML tags in the select statement to create hyperlinks and add more HTML features.

How to have optional parameters and default values in SQL*Plus script

If you have a script like the simple following one:

prompt 1="&1"
prompt 2="&2"
prompt 3="&3"
undef 1
undef 2
undef 3

and call it with only 1 or 2 parameters then SQL*Plus asks you for the other parameters:

SQL> @c:\michel v1 v2
1="v1"
2="v2"
Enter value for 3: 
3=""

It would often be useful to be able to call the script with a variable number of parameters. This can be done modifying the previous script as:

set termout off
col p1 new_value 1
col p2 new_value 2
col p3 new_value 3
select null p1, null p2, null p3 from dual where  1=2;
set termout on
prompt 1="&1"
prompt 2="&2"
prompt 3="&3"
undef 1
undef 2
undef 3

So you can call it with a variable number of parameters:

SQL> @c:\michel v1 
1="v1"
2=""
3=""
SQL> @c:\michel v1 v2
1="v1"
2="v2"
3=""
SQL> @c:\michel v1 v2 v3
1="v1"
2="v2"
3="v3"

You can give a default value for these optional parameters modifying the script as:

set termout off
col p1 new_value 1
col p2 new_value 2
col p3 new_value 3
select null p1, null p2, null p3 from dual where  1=2;
select nvl('&1','def1') p1, nvl('&2','def2') p2, nvl('&3','def3') p3 from dual;
set termout on
prompt 1="&1"
prompt 2="&2"
prompt 3="&3"
undef 1
undef 2
undef 3

And so:

SQL> @c:\michel v1 
1="v1"
2="def2"
3="def3"

How to give a default value to a possibly not created bind variable in a SQL*Plus script

Assuming you want to give, in a SQL*Plus script, a default value to a bind variable (named "q" in the below example) and you don't know if this bind variable already exists or not. If it exists and has a non-null value you want to keep it.

You can do this in a script like this one ("q" is the bind variable you want to check and set):

set trimout on trims on feed off head off echo off termout off
var qq varchar2(4000)
exec :qq := :q
spool temp.lst
select decode(:qq, null, 'var q varchar2(4000)') from dual;
spool off
@temp.lst
exec :q := nvl(:q,'my default value')
print q

First a call without pre-creating the variable (the script is named "michel.sql" and prints the value of the bind variable):

SQL> print q
SP2-0552: Bind variable "Q" not declared.
SQL> @c:\michel
my default value

Now the variable is created, remove its value:

SQL> exec :q := null
SQL> print q

SQL> @c:\michel
my default value

In the end, give a value and verify it is kept:

SQL> exec :q := 'THE VALUE'
SQL> print q
THE VALUE

SQL> @c:\michel
THE VALUE

Note: this assumes that the spool directory is in the SQLPATH, you can specify a full path for the spool file if you don't want to rely on this.