CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle SQL*Plus
Version 11.1
General
Note: Oracle in its near infinite wisdom dropped sqlplusw.exe from the initial release of 11gR1. If want it you can copy in the executable from 10.2.0.1 and in most cases rename the DLL oraclient11.dll to oraclient10.dll. Then you will again have a usable interface. If you are as thrilled as we are about this send us an email and we will pass it along to Oracle.

Constants
Constant Usage Example
SQL.LNO Line Number SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

show lno
SQL.PNO Page Number SELECT object_name
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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.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 <function_name>
CREATE OR REPLACE FUNCTION dayofweek (stringin VARCHAR2)
RETURN VARCHAR2 IS

BEGIN
  NULL;
END dayofweek;
/

desc dayofweek

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 VARCHAR2, tfin OUT BOOLEAN)

BEGIN
  NULL;
END demoproc;
/

desc demoproc
Describe a table desc <table_name>
CREATE TABLE demotable (

Describe an object table
set describe depth all {linenum <ON | OFF>} {INDENT <ON | OFF>}
CREATE OR REPLACE TYPE rectangle_t AS OBJECT (
NUMBER,
NUMBER,
NUMBER,
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
 
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

Line Size
SET LINESIZE <integer>
SELECT text
FROM all_source
WHERE ROWNUM < 21;

set linesize 121

SELECT text
FROM all_source
WHERE ROWNUM < 21;

Page Size
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 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 <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
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;

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
 
Error Handling
OS Errors WHENEVER OSERROR <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK>
WHENEVER OSERROR EXIT
@c: emp ofile.sql
SQL Errors WHENEVER SQLERROR <COMMIT | CONTINUE | EXIT | NONE | ROLLBACK>
WHENEVER SQLERROR EXIT SQL.SQLCODE
 
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
 
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
password
 
Quit
Exit exit
exit
Quit quit
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 dbms\admin\catplan.sql

-- in a directory under $ORACLE_HOME

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

-- in a directory under $ORACLE_HOME

get ? dbms\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
 
Spool

Spool

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

SELECT table_name
FROM all_tables;

spo off

spo c: emp\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: emp rimspool.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>

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  
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
 
Related Topics
SHOW
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [134 users online]    © 2010 psoug.org