Oracle SQL*Plus
Version 11.2.0.3
 
General Information
Note: Oracle in its near infinite wisdom dropped sqlplusw.exe from the initial release of 11gR1. Instructions on how to get SQL*Plus Windows from 10gR2 versions to work with 11.1.0.6 through 11.2.0.2 can be found [here] at the bottom of the page.New 11g features will not work but there is only one of those of which I am aware (show edition).
Constants
Constant Usage Example
SQL.LNO Line Number SELECT COUNT(*)
FROM all_objects;

show lno
SQL.PNO Page Number SELECT object_name
FROM all_objects;

show pno
SQL.RELEASE Oracle Version show release
SQL.SQLCODE Current error code show sqlcode
SQL.USER Currently connected user show user
Startup Parameters: Usage 1
Flags Description
-H Displays the SQL*Plus version and the usage help
-V Displays the SQL*Plus version
sqlplus -C | -H
Startup Parameters: Usage 2
Flags Description
-C<version> Sets the compatibility of affected commands to the version specified. The version has the form "x.y[.z]. For example -C 10.2.0 
-L Attempts to log on just once, instead of reprompting on error
-M<option>  
-R<level> Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 1, 2, or 3 with R3 being the most restrictive and disables all such commands
-S Sets the silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands
sqlplus [ [<option>] [<logon>] [start>] ]
Logon Parameters sqlplus <user_name>[/password][@<connect_identifier>] | /
[AS SYSDBA | AS SYSOPER | /NOLOG
sqlplus system/manager@orabase AS SYSOPER /NOLOG
Start Parameters @<url>|<filename>[.<extension>] [<parameter> ....]
TBD
 
View All Parameters
List SQL*Plus Parameters show all
SQL> show all
 
Column Formatting
Character COL <column_name> FORMAT a<integer>
SELECT object_name, object_type
FROM all_objects
WHERE ROWNUM < 6;

COL object_name FORMAT a30

SELECT object_name, object_type
FROM all_objects
WHERE ROWNUM < 6;
Money COL <column_name> FORMAT $99,999
COL salary FORMAT $99,999
Number
Element Example Description
9 9999 Number of significant digits returned
COL test FORMAT 99.99
SELECT 100/3 TEST FROM dual;

COL test FORMAT 99.9999
SELECT 100/3 TEST FROM dual;
0 0999
9990
Display a leading zero or a value of zero in this position as 0
COL test FORMAT 099.999
SELECT 100/3 TEST FROM dual;
$ $9999 Prefixes with dollar sign
CREATE TABLE t (
test NUMBER(10,4));

INSERT INTO t VALUES (1234);
INSERT INTO t VALUES (-234);
INSERT INTO t VALUES (0);

COL test FORMAT $9999

SELECT * FROM t;
B B9999 Display a zero value as blank
COL test FORMAT B9999

SELECT * FROM t;
MI 9999MI Display "-" after a negative value
COL test FORMAT 9999MI

SELECT * FROM t;
S S9999 Display "+" for positive values and "-" for negative values
COL test FORMAT S9999

SELECT * FROM t;
PR 9999PR Displays a negative value in <angle brackets>
COL test FORMAT 9999PR

SELECT * FROM t;
D 99D99 Display the decimal character
COL test FORMAT 9999D99

SELECT * FROM t;
G 9G999 Display the group separator
COL test FORMAT 9G999

SELECT * FROM t;
C C999 Display the ISO currency symbol
COL test FORMAT C9999

SELECT * FROM t;
L L999 Display the local currency symbol
COL test FORMAT L9999

SELECT * FROM t;
, 9,999 Display a comma
COL test FORMAT 9,999

SELECT * FROM t;
. 99.99 Display a period
COL test FORMAT 9999.99

SELECT * FROM t;
V 999V99 Multiplies value by 10n, where n is number of "9"s after "V"
COL test FORMAT 9999V99

SELECT * FROM t;
EEEE 9.999EEEE Display value in scientific notation
COL test FORMAT 9999.99EEEE

SELECT * FROM t;
RN or rn RN Display upper or lowercase Roman numerals. Value can be an integer between 1 and 3999
COL test FORMAT RN

SELECT * FROM t;
DATE DATE Format a NUMBER columns that represent Julian dates as MM/DD/YY
COL test FORMAT DATE

SELECT * FROM t;
 
Arraysize
Array Size Demo ARRAYSIZE nnn
The default value of nnn is 15, which is too small for large data transfers. Try larger and larger values of nnn until response improvements become marginal.

CREATE TABLE t AS
SELECT *
FROM all_objects;

SELECT COUNT(*)
FROM t;

CREATE INDEX t_idx
ON t(object_id)
PCTFREE 0;

set autotrace traceonly

show arraysize

SELECT * FROM t;

SELECT * FROM t;

SELECT * FROM t;

set arraysize 10

SELECT * FROM t;

set arraysize 100

SELECT * FROM t;

set arraysize 250

SELECT * FROM t;
 
Connect
CONN as SYS CONN <logon> AS <SYSDBA | SYSOPER>
conn / as sysdba

conn sys@orabase AS SYSDBA
CONN as a user CONN <logon> / <password> @ <instance>
conn uwclass/uwclass@orabase

conn uwclass@orabase
 
Define / Undefine
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jul 15 10:26:44 2011

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

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

SQL> DEFINE

SQL> DEFINE _CONNECT_IDENTIFIER
DEFINE _CONNECT_IDENTIFIER = "ORABASE" (CHAR)

SQL> DEFINE _DATE
DEFINE _DATE = "04-JAN-2010 13:28:22" (CHAR)

SQL> DEFINE _EDITOR
DEFINE _EDITOR = "Notepad" (CHAR)

SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options" (CHAR)

SQL> DEFINE _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

SQL> DEFINE _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1002000200" (CHAR)

SQL> DEFINE _USER
DEFINE _USER = "SYS" (CHAR)

SQL>UNDEFINE _USER
 
Describe
Describe a function desc <function_name>
CREATE OR REPLACE FUNCTION upperString (stringin VARCHAR2) RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN UPPER(stringin);
END upperString;
/

desc upperString
Describe a package desc <package_name>
CREATE OR REPLACE PACKAGE demopkg IS

PROCEDURE demoproc;
FUNCTION demofunc RETURN BOOLEAN;

END;
/

desc demopkg

CREATE OR REPLACE PACKAGE BODY demopkg IS

PROCEDURE demoproc IS
BEGIN
  NULL;
END;

FUNCTION demofunc RETURN BOOLEAN IS
BEGIN
  RETURN TRUE;
END;

END demopkg;
/

desc demopkg
Describe a procedure desc <procedure_name>
CREATE OR REPLACE PROCEDURE demoproc (numbin NUMBER, stringin IN OUT NOCOPY VARCHAR2, tfin OUT BOOLEAN) AUTHID DEFINER IS
BEGIN
  NULL;
END demoproc;
/

desc demoproc
Describe a table desc <table_name>
CREATE TABLE demotable (
rid     NUMBER(2,2),
testcol VARCHAR2(20),
insdate DATE);

desc demotable
Describe an object table set describe depth all {linenum <ON | OFF>} {INDENT <ON | OFF>}
CREATE OR REPLACE TYPE rectangle_t AS OBJECT (
h  NUMBER,
w  NUMBER,
x  NUMBER,
y  NUMBER);
/

CREATE TABLE rectable (
rectangle_name VARCHAR2(20),
rectangle      rectangle_t);

desc rectable

set describe depth all

desc rectable

set describe depth all linenum on indent on

desc rectable
Describe a view desc <view_name>
CREATE OR REPLACE VIEW demoview AS
SELECT *
FROM demotable;

desc demotable
Describe a synonym desc <synonym_name>
CREATE SYNONYM demosyn FOR rectable;

desc demosyn
 
Disconnect
Disconnect from the current session disconnect
conn uwclass/uwclass@pdbdev

disconnect
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
 
Display
Clear Screen clear scr
SELECT object_name, created
FROM all_objects
WHERE ROWNUM < 2;

clear scr
Column Separators SET COLSEP <column separator>
set colsep ','

SELECT table_name, column_name, data_type
FROM user_tab_cols
WHERE rownum < 10;
Display Headers SET HEAD <OFF | ON>
SELECT table_name FROM all_tables;

set head off

SELECT table_name FROM all_tables;

set head on
Changes the default instance for your session to the specified instance path.

Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.
set instance <instance_path | local>
I have found only a single usage of this syntax in Oracle and that by accident: It is in the script oramtsadmin.sql located in the $ORACLE_HOME/oramts/admin directory. Here is the usage.

disconnect

Rem connect as the Oracle MTS admin user
set instance &mtsadm_con
connect &mtsadm_usr/&mtsadm_pwd
Sets the total number of characters that SQL*Plus displays on one line before beginning a new line SET LINESIZE <integer>
SELECT text
FROM all_source
WHERE rownum < 21;

set linesize 121

SELECT text
FROM all_source
WHERE rownum < 21;
Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions SET NUMWIDTH <integer>
CREATE TABLE t (col NUMBER);

INSERT ITNO t VALUES (9999999999999999999999);

SELECT * FROM t;

set numwidth 25

SELECT * FROM t;
Sets the number of lines on each page of output SET PAGESIZE <integer>
SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pagesize 20

SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pagesize 0

SELECT object_name
FROM all_objects
WHERE rownum < 60;
Page Break BREAK ON <column_name> [SKIP PAGE]
break on overload skip page

set pagesize 25
set linesize 121
col overload format a8

SELECT overload, position, argument_name, in_out, data_type
FROM all_arguments
WHERE object_name = 'CREATE_TUNING_TASK'
ORDER BY overload, position;
PAUSE Enables control terminal scrolling when running reports. First, SET PAUSE text, and then SET PAUSE ON to make text to appear each time SQL*Plus pauses SET PAUSE <OFF | ON>
SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pause on

SELECT object_name
FROM all_objects
WHERE rownum < 60;

set pause off
TIMING controls the display of timing statistics.

ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command.
SET TIMING <OFF | ON>
SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set timing on

SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set timing off
Title ttitle {LEFT <text>} {CENTER <text>} {RIGHT <text>}
set pagesize 25

ttitle LEFT '01-Jan-2005' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO

SELECT * FROM dual;

ttitle LEFT '01-Jan-2005' CENTER 'University of Washington' RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER - 'Oracle Application Development'

SELECT object_name
FROM all_objects
WHERE rownum < 60;
Wrap wrap <ON | OFF>
show wrap

SELECT * FROM dba_tables WHERE rownum < 11;

set wrap OFF

SELECT * FROM dba_tables WHERE rownum < 11;

set wrap ON

SELECT * FROM dba_tables WHERE rownum < 11;
 
Display Output From DBMS_OUTPUT.PUT_LINE built-in package set serveroutput <ON | OFF>
DECLARE
 x VARCHAR2(20) := 'This is a test';
BEGIN
  dbms_output.put_line(x);
END;
/

set serveroutput on

DECLARE
 x VARCHAR2(20) := 'This is a test';
BEGIN
  dbms_output.put_line(x);
END;
/
 
Edit / Editor
Define An Editor _editor
define _editor=vi
Edit The Last Command ed
ed
Change the Edit File Location set editfile <edit file location and file name>
set editfile c:\tmp\afiedt.buf
 
Error Handling
OS Errors WHENEVER OSERROR <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK>
WHENEVER OSERROR EXIT
@c:\temp\nofile.sql
SQL Errors
See DBMS_REDEFINITION Demo to view usage of this feature
WHENEVER SQLERROR <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK>
WHENEVER SQLERROR CONTINUE
SQL*Plus Erorr Logging (new in 11gR2) ERRORLOGGING < ON | OFF >
SQL> SET ERRORLOGGING ON
 
Escape
Defines the character used as the escape character ESC[APE] <\ | c | ON | OFF>
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'

-- return to the default backslash (\) character

SET ESCAPE ON
 
Escaped Character (new in 11g)
Specifies a character to be escaped and not interpreted SET ESCCHAR <@ | ? | % | $ | OFF>
SET ESCCHAR $

RUN LOADFILE$
 
Execute
Run a stored procedure exec <procedure_name>
CREATE OR REPLACE PROCEDURE demoexec IS
BEGIN
  dbms_output.put_line('*** Executed ***');
END demoexec;
/

set serveroutput on

exec demoexec
 
Exit
Exit exit
exit
 
Help
Display SQL*Plus Help help <command>
help index

help variable
 
Host
Shell to the operating system host
host

exit
Unix Shell !
SQL> !

exit
Windows Shell $
SQL> $

exit
 
Password
Change Password password
SQL> password;
 
Quit
Quit quit
SQL> quit;
 
Run
Run RUN
set serveroutput on

BEGIN
  dbms_output.put_line('test');
END;
/

run
 
Run Script
@ @ <path_and_script_name>
@c:\oracle\product\ora102\rdbms\admin\catplan.sql

-- in a directory under $ORACLE_HOME

@?\rdbms\admin\catplan.sql
Get get <path_and_script_name>
get c:\oracle\product\ora102\rdbms\admin\catplan.sql

-- in a directory under $ORACLE_HOME

get ?\rdbms\admin\catplan.sql
 
Save
Save the most recently executed SQL statement save <file_name>
SELECT tablespace_name, status
FROM all_tablespaces;

save ts

SQL> host

$ more ts

$ exit
 
Show
Show the current container name sho[w] con_name
SQL> sho con_name

SQL> show con_name
Show the current container number sho[w] con_id
SQL> sho con_id

SQL> show con_id
Show the current session edition sho[w] edition
SQL> sho edition

SQL> show edition
Show the current connected connected session user sho[w] user
SQL> sho user

SQL> show user
 
Spool
Spool

Short version: SPO
spo[ol] [path_and_file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
spool c:\temp\zzyzx.txt

SELECT table_name
FROM all_tables;

spo off

spo c:\temp\zzyzx.txt app

SELECT object_id
FROM user_objects

spool off
Termout termout <OFF | ON>
-- termout is one of those sqlplus settings which tend to cause confusion. It only applies to output from running script files.

abc.sql:
set termout off
select 'abc' from dual

and run it like this in sqlplus:
@abc.sql
Trimspool trimspool <OFF | ON >
spool c:\temp\trimspool.txt

SELECT table_name
FROM all_tables
WHERE rownum < 11;

trimspool on

SELECT table_name
FROM all_tables
WHERE rownum < 11;

spool off
 
SQL Prompt
Set the SQL Prompt sqlprompt <value>
Predefined Variable Description
_CONNECT_IDENTIFIER Connection identifier used to make connection, where available
_DATE Current date, or a user defined fixed string
_EDITOR Specifies the editor used by the EDIT command
_O_RELEASE Full release number of the installed Oracle Database
_O_VERSION Current version of the installed Oracle Database
_PRIVILEGE Privilege level of the current connection
_SQLPLUS_RELEASE Full release number of installed SQL*Plus component
_USER User name used to make connection
SQL> set sqlprompt "_user'@'_connect_identifier>"
SYS@orabase>conn uwclass/uwclass
Connected.
UWCLASS@orabase>

SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER> '

SQL> set sqlprompt 'SQL> '
 
Variable
Define Variable variable <variable_name> <data_type>
variable x VARCHAR2(20)

variable x

BEGIN
  :x := 'ABC';
END;
/
View Variable Value print <variable_name>
print x
Undefine Variable undefine <variable_name>
undefine x
 
Save Settings
glogin.sql set pagesize 45
set linesize 121
set long 1000000
col column_name format a30
col data_type format a20
col file_name format a60
col name format a30
col object_name format a30
col owner format a25
col segment_name format a30
col service_name format a30
col triggering_event format a35
col value format a30
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
SET DEFINE OFF
SET SERVEROUTPUT ON
login.sql Can be the same as glogin.sql
Create and save State File SQL> conn uwclass/uwclass
SQL> show linesize
SQL> set linesize 141
SQL> show linesize
SQL> STORE SET statefile REPLACE
SQL> EXIT

SQL> conn uwclass/uwclass
SQL> show linesize
SQL> @statefile
SQL> show linesize
 
Related Topics
SHOW
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved