SQL*Plus

Screenshot of SQL*Plus on Windows

SQL*Plus is a command line interface to the Oracle Database. From SQL*Plus, one can issue SQL, PL/SQL and special SQL*Plus commands.

SQL*Plus provides a user-friendly interface which allows you to define and manipulate data in an Oracle database. SQL*Plus adheres to all SQL standards and contains some Oracle-specific add-ons. The "Plus" in SQL*Plus refers to an extension of SQL.

Starting SQL*Plus

To run SQL*Plus, on Windows choose:

Start > Programs > Oracle - HOME-NAME > Application Development > SQL Plus.

Alternatively, start it from the command line: sqlplus.exe in ORACLE_HOME\bin

Unix/Linux: sqlplus in $ORACLE_HOME/bin

Example:

$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 23 09:42:49 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

Leaving SQL*Plus

Use the "exit" command to terminate your SQL*Plus session.

SQL> exit;

To send a return code back to the operating system:

SQL> exit 10;

Command line parameters

• Connect to database: userid/password@connectstring
• Start in silent mode: -s
• Execute a script: @scriptname.sql
• Do not log in to a database: /nolog

Example:

$ sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Oct 21 13:08:36 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL*Plus Commands

EDIT

Edit the command buffer or a SQL file. Can be abbreviated as "ed".

SQL> ed scriptname.sql

To change the external editor (in this example, use vi).

SQL> DEF _EDITOR=vi

RUN

Execute the command buffer or execute a SQL file. Can be abbreviated as "@".

SQL> @scriptname.sql

LIST

List the content of the command buffer.

SQL> l
  1* select * from emp

SHOW

Show all SQL*Plus settings:

SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT  statement
cmdsep OFF
colsep " "
...

Show the value for a specific setting:

SQL> SHOW autotrace
autotrace OFF

SET

To change a SQL*Plus setting:

SQL> SET autotrace ON

DEFINE

See what variables are defined:

SQL> DEFINE
DEFINE _DATE           = "22-JUL-2006 11:34:34" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "o10gr2" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
...

Define a new variable:

SQL> DEFINE var1=123

Undefine a variable:

SQL> UNDEFINE var1

Using a variable in a SELECT statement:

SQL> SELECT * FROM tab1 WHERE col1 = &&var1;

SPOOL

Spool output to file:

SPOOL myreport.lst
...
SPOOL OFF

COPY

The copy command can be used to copy tables between databases and schemas. Great for copying tables with LONG fields.

Example:

COPY FROM scott/tiger@local_db TO scott/tiger@remote_db -
  CREATE remote_emp                                     -
  USING  SELECT * FROM emp;

Note: The "-" at the end of each line (except the last) indicates that the command continues on the next line.

CLEAR

Clear the SQL*Plus screen and the screen buffer. Syntax: CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING}

Example:

CLEAR SCREEN

To clear SQL*Plus screen you can also use ALT+E+R; another shortcut is Shift+Del.

Report formatting

Columns can be formatted with:

col col_name options values

where options = null, heading, format, print, noprint, clear, etc.

Example:

SQL> col empno heading emp|security|no
SQL> col sal format $09,999.99
SQL> col comm null '-na-'
SQL> col mgr noprint
SQL>
SQL> select * from emp;
      emp
 security
       no ENAME      JOB       HIREDATE          SAL      COMM    DEPTNO
--------- ---------- --------- --------- ----------- --------- ---------
     7369 SMITH      CLERK     17-DEC-80  $00,800.00 -na-             20
     7499 ALLEN      SALESMAN  20-FEB-81  $01,600.00       300        30
     7521 WARD       SALESMAN  22-FEB-81  $01,250.00       500        30
 ...

SQL> clear col
columns cleared
SQL> select * from emp;
   EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
-------- ---------- --------- --------- --------- --------- --------- ---------
    7369 SMITH      CLERK          7902 17-DEC-80       800                  20
    7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30
    7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30
...

Similar to this, we have different option for title (tti, bti).

Also see

• SQL*Plus FAQ