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.

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 COL ) command defines the display attributes of a field. After the display attributes are defined, the column values will be displayed in the format defined in COL command. The display attributes can be cleared, turned on or off, and also it can be used by other columns.


(1)  The COLUMN command can be used to format the output

COLUMN ename HEADING  Employee | Name  FORMAT  model


ename   : the name of field/column of a table.


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 > COL ename OFF                                     -- turn off the display attributes

           SQL > COL ename ON                                      -- turn on the display attributes


(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 > COL  field_name  WORD_WRAPPED


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:


           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.




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.       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 >  TTITLE  CENTER   Supplies Report on March-3-1997  SKIP 1

                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 > COL  snum NEW_VALUE varsnum                    -- save every value value in varsnum

                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 -



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



        The following example sets the pagesize and define a variable, named _date and holding the date when you start the SQLPLUS:



                                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.



OFF:  no commit after each SQL statement

ON :  automatically commit pending changes to database.

IMM:  Same as ON.




Set a non-alphanumeric character used to end the block to c.




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




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:




to see mode, buffer size and format of server output.