Oracle SQL*Plus Version 10.2

Oracle SQL*Plus
Version 10.2

General
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 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
-R 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 [ [] [] [start>] ]
Logon Parameters sqlplus [/password][@] | /
[AS SYSDBA | AS SYSOPER | /NOLOG
sqlplus system/manager@orabase AS SYSOPER /NOLOG
Start Parameters @|[.] [ ....]
TBD

View All Parameters
List SQL*Plus Parameters show all
SQL> show all

Column Formatting
Character COL FORMAT a
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 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
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 way
too small for large data transfers. Try larger and larger values of nnn until response improvements are insignificant.

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 AS
conn / as sysdba

conn sys@orabase AS SYSDBA
CONN as a user CONN / @
conn uwclass/uwclass@orabase

conn uwclass@orabase

Define / Undefine
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Nov 19 15:20:10 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


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

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

SQL> DEFINE _DATE
DEFINE _DATE = "19-NOV-04" (CHAR)

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

SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)

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

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

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

SQL>UNDEFINE _USER

Describe
Describe a function desc
CREATE OR REPLACE FUNCTION dayofweek (stringin VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
NULL;
END dayofweek;
/

desc dayofweek
Describe a package desc
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
CREATE OR REPLACE PROCEDURE demoproc (numbin NUMBER,
stringin IN OUT VARCHAR2, tfin OUT BOOLEAN)

BEGIN
NULL;
END demoproc;
/

desc demoproc
Describe a table desc
CREATE TABLE demotable (
Describe an object table set describe depth all {linenum } {INDENT }
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
CREATE OR REPLACE VIEW demoview AS
SELECT *
FROM demotable;

desc demotable
Describe a synonym desc
CREATE SYNONYM demosyn FOR rectable;

desc demosyn

Display
Clear Screen clear scr
SELECT object_name, created
FROM all_objects
WHERE ROWNUM < 2;

clear scr
Column Separators SET COLSEP
set colsep ','

SELECT table_name, column_name, data_type
FROM user_tab_columns
WHERE rownum < 10;
Display Headers SET HEAD
SELECT table_name FROM all_tables;

set head off

SELECT table_name FROM all_tables;

set head on
Line Size SET LINESIZE
SELECT text
FROM all_source
WHERE rownum < 21;

set linesize 121

SELECT text
FROM all_source
WHERE rownum < 21;
Page Size SET PAGESIZE
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 [SKIP PAGE]
break on overload skip page

set pagesize 20
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 SET PAUSE
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 SET TIMING
SELECT COUNT(*)
FROM all_objects;

set timing on

SELECT COUNT(*)
FROM all_objects;
Title ttitle {LEFT } {CENTER } {RIGHT }
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;
Display Output From DBMS_OUTPUT.PUT_LINE built-in package set serveroutput
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

Error Handling
OS Errors WHENEVER OSERROR
WHENEVER OSERROR EXIT
@c:\temp\nofile.sql
SQL Errors WHENEVER SQLERROR
WHENEVER OSERROR EXIT SQL.SQLCODE

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

set serveroutput on

exec demoexec

Help
Display SQL*Plus Help help
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
password

Quit
Exit exit
exit
Quit quit
quit

Run
Run RUN
set serveroutput on

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

run

Run Script
@ @
@c:\oracle\product\ora102\rdbms\admin\catplan.sql

-- in a directory under $ORACLE_HOME

@?\rdbms\admin\catplan.sql
Get get
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
SELECT tablespace_name, status
FROM all_tablespaces;

save ts

SQL> host

$ more ts

$ exit

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

SQL> set sqlprompt "_user'@'_connect_identifier>"
SYS@orabase>conn uwclass/uwclass
Connected.
UWCLASS@orabase>

Variable
Define Variable variable
variable x VARCHAR2(20)

variable x

BEGIN
:x := 'ABC';
END;
/
View Variable Value print
print x
Undefine Variable undefine
undefine x

Save Settings
glogin.sql
login.sql
State File SQL> show linesize
SQL> set linesize 120
SQL> show linesize
SQL> STORE SET statefile REPLACE
SQL> EXIT

SQL> show linesize
SQL> @statefile
SQL> show linesize