| 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 |
| |
| 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; |
| Numwidth |
SET NUMWIDTH <integer> |
CREATE TABLE t (col NUMBER);
INSERT ITNO t VALUES (9999999999999999999999);
SELECT * FROM t;
set numwidth 25
SELECT * FROM t; |
| 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:\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 |
| |
| 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 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 |
