SQL/Plus
Dr.
H. Wang, Feb. 1997 Updated 2013
The
document list the features of Oracle extensions to the interactive SQL language.
The extensions largely relate to the output formatting, interactive SQL
environment settings, interface between SQL statements and
users.
1.
SQL
Commands and Command File
SQL
commands can be used to
create, delete and update
database, tables, indexes views, stored procedures, triggers and etc.;
grant/revoke
user s privileges of accessing oracle databases
retrieve
update, delete and query data in tables
do many
other database management, system maintenance chords.
One
or more SQL commands make a command file, usually named with .sql extension.
Store a SQL commands in a file is recommended since the file can be run,
modified easily.
To log into Oracle interactive SQL environment, type
$ sqlplus oracle-username
and
you will prompted for password.
(1)
Two
ways of Running a SQL Command File
SQL
> start command_file_name -- No extension needed if
the file is extended with .sql
SQL
> @command_file_name
-- No extension needed if
(2)
Run
command File Substitution Variables
SQL
Statements in command file may contain substitution variables. If numbers (1, 2,
3, and etc.) are used to name the variable, then user can provide arguments for
the substitution variables at command line:
* SQL > start command_file arg1 arg2
assuming
that the command file has two substitution variables named as 1 and 2 and
prefixed with &.
No
semi-colon necessary after the last argument.
(3)
Rerun
the commands in the Buffer
After
you type a SQL command or run a SQL command file, the command(s) are stored in
buffer. To run the command again, you can
Type run : SQL >
run
Type slash: SQL > /
(4)
Save Tested Query into File
SAV[E] file_name[.ext]
CRE[ATE] | REP[LACE] |
APP[END]
2.
Output
Formatting
The COLUMN ( or
(1) The COLUMN command can be used to format
the output
COLUMN
ename HEADING Employee | Name FORMAT
model
where
ename : the name of field/column of a
table.
Employee
Name : the new field title
when query result is displayed.
model:
A10: print a text field with at
most 10 columns
$9,999.990
: put dollar sign before value of a numeric column. Use comma after each 3
digits
and 3 digits after decimal point.
The
order of HEADING and FORMAT clause in column command are not important.
Moreover, either of the two clauses can be used independently. The vertical bar,
'|', in string represent a new line character.
(2)
Display,
Clear, Turn Off and On Column Display Attributes
SQL
> COLUMN -- show the display attributes
of all fields and ON or OFF
SQL>
COLUMN ename CLEAR
-- clear the display attributes of ename
SQL
>
SQL
>
(3)
Apply
display format of a Field to Other Field
The
following example show how to apply the format of one field to another
field:
SQL
> COLUMN price HEADING Retail
Price format $9,999.99
SQL
> COLUMN total LIKE price HEADING Total
Remember
that you have to have HEADING. Otherwise the Retail|Price will be used as
heading for the total field.
(4)
Wrap
Field In Word
If
the field width is set short than some field value of a record, the value will
be wrapped to next line. If you don t want the word to be cut in the middle,
use
SQL
>
3.
Interacting
with User
You
can use a substitution variable to accept value from user in interactive SQL.
The substitution variables may or may not defined before they are used in the
SQL statements.
(1)
Define
a Substitution Variable:
SQL
> DEFINE supName =
John
Define variable supName and
initialize the variable John. The DEFINE cannot be added in the SQL buffer while
you can use it interactively and used in SQL script file.
(2)
How
to use variable in the SQL statement?
If
a variable is used in a SQL statement, the variable is called substitution
variable since the value of the variable will be used to replace the variable
name. The substitution variables in SQL statements must be prefixed with &
sign. If the value in a variable is to be string, enclose &variable with
single quotes. If the value does not need the single quotes, you shout not
enclose the variable within quotes. For example
SQL
> SELECT * FROM s where sname =
&supName ;
SQL
> SELECT * FROM s where status > &st ;
While the first query will
retrive supplier(s) whose name is John and second query retrieve all
suppliers
which status is larger than the value in the substitution variable
st.
(3)
Use
Undefined Variables
When
you use undefined variables in a SQL statement, you will be prompt automatically
for values for each of the variables used in the statement when the statement is
executed. While the defined variable in the SQL statement will use the value you
gave to the variable previously until you have the value by DEFINE, UNDEFINE or
ACCEPT command is applied to the variable.
(4)
Use
Number as Substitution Variable
Number
can be used to define user variables in the following way:
SELECT * FROM S WHERE Status BETWEEN &1 AND &2
;
When numbers used as user variables in a SQL file, the arguments passed
to the command file will replace the number substitution
variables.
(5)
Concatenated
Variable with Other Alpha-numeric characters
DEFINE
col= B
SELECT
* FROM p WHERE color = &col.lue // will list blue color
parts.
(6)
Start
Command File with Parameter
Syntax:
STA[RT] file_name[.ext] [arg1 arg2 ]
The
argument I will replace number substitution variable i. As for the non digital
substitution variables in the file, SQLPLUS will prompt for their
values:
Tmp.sql contains:
SELECT * FROM s where Status BETWEEN &1 AND &2 AND City = %city
;
Run the file with
START tmp 10
20
Will substitute variable 1 with 10 and 2 with 20 and prompt for value for
variable city.
(7)
Prompt
User
To
prompt user what to do, you can use PROMPT command followed by text without
included in single quotes.
PROMPT Enter a value for
status like 20, 50
You
cannot add prompt command in the buffer. It can only be used in SQL file, and be
tested interactively.
(8)
Accept
Value from User
Syntax:
ACC[EPT] variable [NUM[BER] ] | [CHAR ]
[ PROMPT text | NOPR[OMPT]
[ HIDE ]
Accept value for a variable with either numerical or character type, with
or without prompt. It also allow you hide the string typed on the
screen.
4.
Environment
Variables
(1)
Change
Page Size
The
Page size determine the number of rows after which the table heading will
reprinted. If you set the page size to 20, then the table heading will be
printed for each 20 rows in the select statement.
SQL
> set pagesize 20
-- set page size
SQL
> set linesize
100 -- 100
column per line.
(2)
Set
Underline Character
In
the table heading, each field name is underlined. The default the under line
character is - and can be changed by setting the following environment
variable:
SQL
> set underline
=
After
the above command, the table heading will be underlined with = instead of
- .
(3)
Set
Record Separator
The
records from select statement can be separated by a line of
character:
SQL > SET RECSEPCHAR -
-- records may be separated by a line of -
Whether
the record will be separated is determined by the value of anther variable,
called record separator.
SQL > SET RECSEP WRAPPED -- a record separating line will be
printed if a record is
wrapped.
Otherwise no line separator will be printed out.
SQL > SET RECSEP
EACH -- line separator is
printed out after each record.
SQL > SET RECSEP
OFF
-- set no record separator line.
5.
Organizing
Report
The
result from a select statement can be formatted somehow by SQL* Plus extension.
You can add title to your report, suppress duplicated value in rows and computer
summary data from the selected record.
(1)
Setting
the Title of Your Report
(2)
Suppressing
Duplicate Values
When
a record set selected and sorted by some column, the values in the sorted column
may repeated in different records. The duplicated value can be suppressed by the
following command:
BREAK ON
col_name [SKIP n | PAGE] [ ON col_name [SKIP n | PAGE] ]
If
the col_name is used in the ORDER BY clause, then the duplicated value on the
column will be suppressed. When a different value appear in that column, n rows
or a page will be skipped before the records with different value being
printed.
(3)
Compute
Summary Data
To
apply one summary function to one or more columns after certain break, use
compute command:
COMPUTE function_name OF col, col, , ON break_col |
REPORT
Function
name is one the following function names:
SUM,
MIN, MAX, AVG, STD, VAR, COUNT, NUM
If
break_col is used then the computation is done at each break and if REPORT is
used then
the
computation is done at the end of the report.
Example:
break on color skip 1
compute avg of pnum weight on color;
compute count of pnum on color;
select color, pnum, weight from p
order by color ;
SQL> @tmp2
COLOR PNUM WEIGHT
---------- ----- ----------
Blue p3 17
p5 12
********** ----- ----------
avg 14.5
count 2
Green p2 17
********** ----- ----------
avg 17
count 1
Red p4 14
p1 12
p6 19
********** ----- ----------
avg 15
count 3
Yellow p7 40
p8 3
********** ----- ----------
avg 21.5
count 2
8 rows selected.
* Each
compute command can add only one function to multiple
columns.
(4)
List
and Clear Breaks and Computes
To
list all breaks and computes use the following commands:
SQL
> BREAK
-- list all breaks
SQL
> CLEAR BREAK
-- clear all breaks
SQL
> COMPUTE
-- list all summary functions
SQL
> CLEAR COMPUTE
-- clear all computes
(5)
Set
Time On and OFF
SQL
> SET TIME ON | OFF
12:03:10
SQL > will be displayed as prompt instead of SQL > if time variable is set
to on.
(6)
6.
Report
Titles
The output from SQL
SELECT statement is a report. You can give title on the top of each page or a
title on the bottom of each page depending on whether TTITLE (top title) or
BTITLE (bottom title) command is used. The top title can also contain the value
in some ( break ) column and the page number.
Example
1:
SQL >
SQL > SELECT * FROM sp
The sp table contents are display with
line Supplies Report on March-3-1997 displayed on the top-center of each page
of the report. Between the report title and contents, there 1 blank
line.
Example
2:
SQL > TTITLE OFF
-- turn off the top title
SQL > BTITLE SKIP 2 LEFT Supplies Report RIGHT Page No.
FORMAT999 SQL.Pno
SQL > SELECT * FROM sp
The contents of sp table is
displayed in pages. At the end of each page, a page title is displayed at the
left-bottom of the page. Between the contents and the page title, there are 2
blank lines. On the left is the report title is on the left. On the right, Page
No. 1 will be
displayed on the bottom of the first page and the number for the page is
formatted with 3 columns.
Example
3: Put field name in the report
title:
SQL > BTITLE OFF
-- turn off bottom title
SQL > BREAK ON snum
SKIP PAGE
-- suppress the duplicate value in sunum
SQL >
SQL > TTITLE LEFT Report
on Suppliers skip 1 Supplier: varsnum Skip 2
SQL > SELECT * from sp order by snum, pnum ;
Report
on Supplies
Supplier:
s1
PNUM
QTY
-----
----------
p1
300
p2
200
p3
400
p4
200
p5
100
p6
100
Report
on Supplies
Supplier:
s2
PNUM
QTY
-----
----------
p1
300
p2
400
Report
on Supplies
Supplier:
s3
PNUM
QTY
-----
----------
p2
200
Report
on Supplies
Supplier:
s4
PNUM
QTY
-----
----------
p2
200
p4
300
p5
400
Report
on Supplies
Supplier:
s6
PNUM
QTY
-----
----------
p1
1
p2
2
p3
3
p4
4
p5
5
p5
10
p5
10
p6
6
20
rows selected.
SQL>
spool off
7.
Save
and Print SQL Result
SQL > SPOOL
tmp.txt
-- save everything displayed on the screen into tile
tmp.txt
SQL > select * from s
;
-- sql command and contents of s will be saved into text
file
SQL > SPOOL OFF
-- close the file.
Before you turn off the
spooling, you can send the whatever in the file into printer
by
SQL > SPOOL OUT
8.
Connect to Remote Database
Connect SQLPLUS command can be used to connect a user to any local or
remote database if SQL*NET and appropriate driver is installed.
9.
Copy
Table from One Database to Another Database
A table in
one database can be copied into another database by the following
command:
SQL> COPY FROM
scott/tiger@TEST
-
SQL>
TO wang/wang
-
SQL>
Create Emp -
SQL>
USING SELECT * FROM EMP
;
The
above example copy the table EMP from scott s database into wang s database.
Both the table structure and data are copied from the source to the
destination.
Both
the source and the destination databases could be remote databases. TEST in the
example above is the UID for the scott s database. If database located at other
location, not on the same machine, then use the full database specification
string. The database specification string is different depending on the SQL*NET
protocol used. Use SQL*NET related document.
TO
CLAUSE: The TO clause is optional in COPY command if you want to copy the table
from another database to the default database. The default database is the
database you are connected to.
CREATE
CLAUSE: Create TableName ( list_of_field_name )
If
you want to use the field names of source table for destination table, you don t
need to list the field names in the CREATE clause.
USING
clause: Using clause consists of
USING and a select_statement.
The
select statement determine the destination table s structure and
contents.
10.
SQL/PLUS
login File and ORACLE_PATH
A
login.sql file can be added to your homework directory so that the commands in
the file will be executed every time you start SQL*PLUS. To make login.sql run
every time you start SQLPLU in any sub-directory, you need put your home
directory in the ORACLE_HOME environment variable in the .profile
for
kshell.
The
following example sets the pagesize and define a variable, named _date and
holding the date when you start the SQLPLUS:
login.sql:
set pagesize 22
set termout
off
break on
today
column today new_value _date --
associate col. Name with a variable
select
to_char( sysdate, fmMonth DD, YYYY )
today
from dual ;
clear
breaks
set
termout on
Usually, a SQL command file is executable from the current working
directory. However, if you have a set of SQL command files which need to be
executed from time to time, then this is what you may do:
1.
Put
the frequently used SQL command files in one sub-directory,
and
2.
Put
the path in the ORACLE_PATH variable in the .profile file.
3.
Log
out and log in the system again, all the SQL command files in that sub-directory
will be executable from the any sub-directory.
11.
More
SQL/PLUS Environmental Variables
The
Session environment can set by SET commands on the following
arguments:
SET
ARRAY [ 20 | n
]
Set
number of rows SQL*Plus fetched at one time. Larger value will increate the
efficiency, not the result. N is between 1 to 5000.
SET
AUTO[COMMIT] { OFF | ON | IMM[EDIATE]
}
OFF:
no commit after each SQL
statement
ON
: automatically commit pending changes to
database.
IMM:
Same as ON.
SET
BLO[CKTERMINATOR] { | c }
Set
a non-alphanumeric character used to end the block to c.
SET
COM[PATIBILITY] {V5 | V6 }
Determine whether COMMIT and ROLLBACK command will be save in the buffer or not. V5 don t store those two command while V6 does.
SET
CON[CAT] { . | c | OFF | ON }
Set the character which ends the substitution variable and other string to different character than . or turns the separator on or off.
SET
COPYC[OMMIT] { 0 | n }
Set the number of batches before the SQLPLUS commit the changes when copy command is used to copy records from one database to another database. If 0 is used, then no commit until the end of copy command. The number of records before commit is determined by n and the array size.
SET
DEF[INE] { & | c | OFF | ON
}
Set the character used to prefix the substitution variable to c. Off and On will ask SQLPLUS to scan or not to scan SQL statement for substitution variable in the statement.
SET
HEA[DING] { ON | OFF }
Set table heading on or off when records are displayed on screen.
SET
HEADS[EP]] { | | OFF | ON }
Change the character used to separate the field/column names in the table heading.
SET
HEADS[EP]] { 80 | n }
Change the line size and 1 <= n < 32,767
SET
NEWP[AGE] (1 | n }
Set the number of blank lines between the beginning of the page the top line of that page.
SET
NULL text
Set the text string used for displaying NULL values.
SET
NUMF[ORMAT] format
Set the default format for numbers.
SET
NUM[WIDTH] { 10 | n }
Set default width for displaying numbers.
SET
PAGES[IZE] { 14 | n }
Set the number of lines from the top title to the end of page.
SET SHOW [MODE] {
ON | OFF }
Controls whether SQL*Plus lists the old and new settings of SQL*Plus system variable when you change the setting with SET command.
Many more variables that Set command can set.
It will allow you to print message from PL/SQL with DBMS_OUTPUT package. There is a buffer size limit (default to 2000 byte). If the number is exceeded, error ORA-20000 ORU-10027, Buffer overflow, limit of 2000 byte.
Use the following command to show the server output setting:
SHOW SERVEROUTPUT
to see mode, buffer size and format of server output.