×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Oracle Supplied Packages

SQL*Plus Commands by BJCooperIT
Posted: 22 Apr 03 (Edited 10 Jun 04)

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.

It is important to recognize the difference between:
  1. DDL statements such as CREATE TABLE, DROP TABLE, etc. which manage the database
  2. SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. which manipulate data in the database
  3. PL/SQL which is a language that that allows you to write programs with procedural statements such as IF..THEN...ELSE and looping constructs
  4. SQL*Plus commands which set the SQL*Plus environment
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:

CODE

SQL> show all

you should see a list similar to this:
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


STORE
If you want to save your settings after you have made changes:

CODE

SQL> store set my_settings_file.new create

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:

CODE

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

START or @
To run a script:

CODE

SQL> start my_script.tst
  or  

CODE

SQL> @my_script.tst
  
If you omit the file extension, it will use æ.sqlÆ by default.

/
To run the SQL statement or PL/SQL block you typed:

CODE

SQL> select sysdate from dual
  2  
SQL> /

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


SPOOL
To have displayed output written into an output file:

CODE

SQL> spool my_output.txt

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

CODE

SQL> spool off


COLUMN
This will format a column for output:

CODE

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

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

Since the heading is wider than the data you might add:

CODE

SQL> column today format a11
SQL> /

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

or you could have set both in one statement:

CODE

column today format a11 head 'Todays Date'


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

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
à


The format parameter also may assign a mask to a numeric field.

CODE

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

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

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:

CODE

ed

To edit a file:

CODE

ed my_script.tst

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

GET
Used to retrieve the contents of a file into the buffer.

CODE

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

or

CODE

SQL> get my_script.new nolist
SQL>

If you omit the file extension it will use æ.sqlÆ by default.

LIST
To list the contents of the SQL buffer:

CODE

SQL> l
  1* select sysdate from dual;


PROMPT
To display a user message:

CODE

SQL> prompt Welcome Back!
Welcome Back!


SAVE
Used to save the contents of the buffer to a file:

CODE

SQL> save my_sql.new create

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.

DEFINE
Sets the special character used for substitution variables (default is æ&Æ).

CODE

SQL> set def on ^
  or  

CODE

SQL> set def off


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

CODE

select sysdate from dual;

Then:

CODE

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

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

or

CODE

SQL> set echo off
SQL> @xxx

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


FEEDBACK
Controls whether SQL*Plus displays the number of rows affected.
For example:

CODE

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

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

1 row selected.

or

CODE

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

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


HEADING
Controls whether SQL*Plus displays headings.
For example:

CODE

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

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

or

CODE

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

21-APR-03


LINESIZE
Specifies the number of characters on a line.

CODE

SQL> set linesize 100


LONG
Specifies the maximum number of characters to display for a long datatype.

CODE

SQL> set long 200


NEWPAGE
Specifies the number of lines between pages. A æ0Æ causes a formfeed.

CODE

SQL> set newpage 0


PAGESIZE
Specifies the number of lines on a page.

CODE

SQL> set pagesize 55


SCAN
Turns user variable substitution on/off.

CODE

SQL> set scan on
  or  

CODE

SQL> set scan off


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

CODE

SQL> set serveroutput on
  or  

CODE

SQL> set serveroutput off
  or

CODE

SQL> set serveroutput on size 100000


SHOWMODE
Determines if the before and after values of settings are displayed.

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>


TERMOUT
Determines if output from a script is displayed.
If file XXX.SQL contains:

CODE

select sysdate from dual;
then:

CODE

SQL> set term on
SQL> @xxx

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

SQL> set term off
SQL> @xxx
SQL>


TIME
Controls whether time is displayed in the SQL prompt.

CODE

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


TIMING
Controls whether the elapsed execution time displays.

CODE

SQL> set timing off
SQL> @xxx

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

SQL> set timing on
SQL> /

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

 real: 10


TRIMOUT
Determines if trailing spaces are trimmed from lines displayed on the screen.

CODE

set trim on
  or  

CODE

set trim off


TRIMSPOOL
Determines if trailing spaces are trimmed from lines spooled to a file.

CODE

set trims on
  or  

CODE

set trims off


TRUNCATE
Determines if long lines are truncated.
If file XXX.SQL contains:

CODE

select '&1' hi from dual;
then:

CODE

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

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


SQL> set truncate on
SQL> /

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


VERIFY
Determines if before and after images of lines with substitution variables are displayed. If flat file XXX.SQL contains:

CODE

select '&1' hi from dual;
then:

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!


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

CODE

select '&1' msg from dual;
then:

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!


Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close