Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL*Plus Commands

Oracle Supplied Packages

SQL*Plus Commands

by BJCooperIT Posted Apr 22, 2003 (Edited Jun 10, 2004)

Lately there have been a number of questions regarding Oracle SQL*Plus commands. I recommend purchasing a copy of the OÆReilly Oracle SQL*Plus Pocket Reference (ISBN 1-56592-941-1, about $10). This manual was used as a reference in writing this FAQ. This is a Windows/Oracle 8i perspective. The purpose of this FAQ is to familiarize you with some of the most commonly used SQL*Plus commands. I will leave the more advanced features for another FAQ.

[color green]It is important to recognize the difference between:
[ol][li]DDL statements such as CREATE TABLE, DROP TABLE, etc. which manage the database[/li]
[li]SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. which manipulate data in the database[/li]
[li]PL/SQL which is a language that that allows you to write programs with procedural statements such as IF..THEN...ELSE and looping constructs[/li]
[li]SQL*Plus commands which set the SQL*Plus environment[/li][/ol][/color]
SQL*Plus Commands
1. Are usually entered on one line and do not require a ô;ö at the end.
2. Stay in effect until you reset them or exit the session.
3. May be entered in upper or lowercase.

SHOW
When you start a SQL*Plus session it gets itÆs initial settings from the LOGIN.SQL file, if one exists, in your directory path. To get a list of your current settings type:
[color blue]

Code:

SQL> show all

[/color]
you should see a list similar to this:
[color blue][sup]appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
shiftinout INVISIBLE
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
linesize 100
lno 24
loboffset 1
long 80
longchunksize 80
newpage 1
null ""
numformat ""
numwidth 9
pagesize 24
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 800050000
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the 1st few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "MY_SCHEMA_NAME"
verify ON
wrap : lines will be wrapped[/sup][/color]

STORE
If you want to save your settings after you have made changes:
[color blue]

Code:

SQL> store set my_settings_file.new create

[/color]
will store the settings in the new file which you have named.
If you omit the file extension it will use æ.sqlÆ by default.
The keyword ôcreateö may be changed to ôreplaceö to overwrite an
existing file or to ôappendö to add to an existing file.

DESCRIBE
To describe most database objects:
[color blue]

Code:

SQL> desc dual
 Name                            Null?    Type
 ------------------------------- -------- ----
 DUMMY                                    VARCHAR2(1)

[/color]

START or @
To run a script:
[color blue]

Code:

SQL> start my_script.tst

[/color] or [color blue]

Code:

SQL> @my_script.tst

[/color]
If you omit the file extension, it will use æ.sqlÆ by default.

/
To run the SQL statement or PL/SQL block you typed:
[color blue]

Code:

 SQL> select sysdate from dual
  2  
SQL> /

SYSDATE
---------
21-APR-03

[/color]

SPOOL
To have displayed output written into an output file:
[color blue]

Code:

SQL> spool my_output.txt

[/color]
If you omit the file extension, it will use æ.lstÆ by default.
To stop spooling:
[color blue]

Code:

SQL> spool off

[/color]

COLUMN
This will format a column for output:
[color blue]

Code:

SQL> column today head 'Todays Date'
SQL> select sysdate today from dual;

Todays Da
---------
21-APR-03

[/color]
Since the heading is wider than the data you might add:
[color blue]

Code:

 SQL> column today format a11
SQL> /

Todays Date
-----------
21-APR-03

[/color]
or you could have set both in one statement:
[color blue]

Code:

column today format a11 head 'Todays Date'

[/color]

The format parameter may be used to specify a smaller width for a character string which may cause it to wrap:
[color blue]

Code:

column table_name format a15
SQL> select table_name from all_tables where owner = 'SYS' order by 1;

TABLE_NAME
---------------
ACCESS$
AQ$_MESSAGE_TYP
ES
à

[/color]

The format parameter also may assign a mask to a numeric field.
[color blue]

Code:

 SQL> column num format 99,990.00
SQL> select 12345.98 num from dual;

       NUM
----------
 12,345.98

[/color]
Date masks are assigned by using the to_char function in your select statements, not in SQL*Plus. There are many optional parameters to the column command, so refer to the manual.

EDIT
To edit the contents of the SQL buffer:
[color blue]

Code:

ed

[/color]
To edit a file:
[color blue]

Code:

ed my_script.tst

[/color]
If you omit the file extension, it will use æ.sqlÆ by default.

GET
Used to retrieve the contents of a file into the buffer.
[color blue]

Code:

SQL> get my_script.new 
  1* select sysdate from dual;

[/color]
or
[color blue]

Code:

SQL> get my_script.new nolist
SQL>

[/color]
If you omit the file extension it will use æ.sqlÆ by default.

LIST
To list the contents of the SQL buffer:
[color blue]

Code:

SQL> l
  1* select sysdate from dual;

[/color]

PROMPT
To display a user message:
[color blue]

Code:

SQL> prompt Welcome Back!
Welcome Back!

[/color]

SAVE
Used to save the contents of the buffer to a file:
[color blue]

Code:

SQL> save my_sql.new create

[/color]
This will store the buffer contents in the new file which you have named.
If you omit the file extension it will use æ.sqlÆ by default.
The keyword ôcreateö may be changed to ôreplaceö to overwrite an
existing file or to ôappendö to add to an existing file.

SET
This sets the environment and there are many parameters. This is a list of some of the commonly used ones. See a manual for detailed more syntax information.

[color purple]DEFINE[/color]
Sets the special character used for substitution variables (default is æ&Æ).
[color blue]

Code:

SQL> set def on ^

[/color] or [color blue]

Code:

SQL> set def off

[/color]

[color purple]ECHO[/color]
Controls whether SQL*Plus commands from a command file are displayed when the command file is run. For example, if you have a file called xxx.sql which contains the SQL command:
[color blue]

Code:

 select sysdate from dual;

[/color]
Then:
[color blue]

Code:

SQL> set echo on
SQL> @xxx
SQL> select sysdate from dual;

SYSDATE
---------
21-APR-03

[/color]
or
[color blue]

Code:

SQL> set echo off
SQL> @xxx

SYSDATE
---------
21-APR-03

[/color]

[color purple]FEEDBACK[/color]
Controls whether SQL*Plus displays the number of rows affected.
For example:
[color blue]

Code:

SQL> set feedback on
SQL> select sysdate from dual;

SYSDATE
---------
21-APR-03

1 row selected.

[/color]
or
[color blue]

Code:

 SQL> set feedback off
SQL> select sysdate from dual;

SYSDATE
---------
21-APR-03

[/color]

[color purple]HEADING[/color]
Controls whether SQL*Plus displays headings.
For example:
[color blue]

Code:

SQL> set head on
SQL> select sysdate from dual;

SYSDATE
---------
21-APR-03

[/color]
or
[color blue]

Code:

 SQL> set head off
SQL> select sysdate from dual;

21-APR-03

[/color]

[color purple]LINESIZE[/color]
Specifies the number of characters on a line.
[color blue]

Code:

SQL> set linesize 100

[/color]

[color purple]LONG[/color]
Specifies the maximum number of characters to display for a long datatype.
[color blue]

Code:

SQL> set long 200

[/color]

[color purple]NEWPAGE[/color]
Specifies the number of lines between pages. A æ0Æ causes a formfeed.
[color blue]

Code:

SQL> set newpage 0

[/color]

[color purple]PAGESIZE[/color]
Specifies the number of lines on a page.
[color blue]

Code:

SQL> set pagesize 55

[/color]

[color purple]SCAN[/color]
Turns user variable substitution on/off.
[color blue]

Code:

SQL> set scan on

[/color] or [color blue]

Code:

SQL> set scan off

[/color]

[color purple]SERVEROUTPUT[/color]
Controls whether PL/SQL blocks can print output. This is also used to set the buffer size. Server output must be set on for DBMS_OUTPUT to work correctly.
[color blue]

Code:

SQL> set serveroutput on

[/color] or [color blue]

Code:

SQL> set serveroutput off

[/color] or
[color blue]

Code:

SQL> set serveroutput on size 100000

[/color]

[color purple]SHOWMODE[/color]
Determines if the before and after values of settings are displayed.
[color blue]

Code:

SQL> set show on
new: showmode BOTH
SQL> set linesize 100
old: linesize 100
new: linesize 100
SQL> set show off
old: showmode BOTH
SQL> set linesize 80
SQL>

[/color]

[color purple]TERMOUT[/color]
Determines if output from a script is displayed.
If file XXX.SQL contains: [color blue]

Code:

select sysdate from dual;

[/color] then:
[color blue]

Code:

SQL> set term on
SQL> @xxx

SYSDATE
---------
22-APR-03

SQL> set term off
SQL> @xxx
SQL>

[/color]

[color purple]TIME[/color]
Controls whether time is displayed in the SQL prompt.
[color blue]

Code:

SQL> set time on
13:31:33 SQL>

[/color]

[color purple]TIMING[/color]
Controls whether the elapsed execution time displays.
[color blue]

Code:

SQL> set timing off
SQL> @xxx

SYSDATE
---------
22-APR-03

SQL> set timing on
SQL> /

SYSDATE
---------
22-APR-03

 real: 10

[/color]

[color purple]TRIMOUT[/color]
Determines if trailing spaces are trimmed from lines displayed on the screen.
[color blue]

Code:

set trim on

[/color] or [color blue]

Code:

set trim off

[/color]

[color purple]TRIMSPOOL[/color]
Determines if trailing spaces are trimmed from lines spooled to a file.
[color blue]

Code:

set trims on

[/color] or [color blue]

Code:

set trims off

[/color]

[color purple]TRUNCATE[/color]
Determines if long lines are truncated.
If file XXX.SQL contains: [color blue]

Code:

select '&1' hi from dual;

[/color] then:
[color blue]

Code:

 SQL> set truncate off
SQL> set linesize 25
SQL> @xxx 123456789012345678901234567890

HI
-------------------------
1234567890123456789012345
67890


SQL> set truncate on
SQL> /

HI
-------------------------
1234567890123456789012345

[/color]

[color purple]VERIFY[/color]
Determines if before and after images of lines with substitution variables are displayed. If flat file XXX.SQL contains: [color blue]

Code:

 select '&1' hi from dual;

[/color] then:
[color blue]

Code:

SQL> @xxx Hello!
old   1: select '&1' greeting from dual
new   1: select 'Hello!' greeting from dual

HI
------
Hello!

SQL> set verify off
SQL> @xxx Greetings!

HI
----------
Greetings!

[/color]

These settings work in combination to modify the SQL*Plus environment to suit your needs. If flat file XXX.SQL contains: [color blue]

Code:

 select '&1' msg from dual;

[/color] then:
[color blue]

Code:

SQL> set verify off
SQL> set feed off
SQL> set echo off
SQL> column msg format a25 head FAQ_Purpose
SQL> @xxx 'Hope this helps!'

FAQ_Purpose
-------------------------
Hope this helps!

[/color]

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top