SQL*PLUS COMMANDS
These commands does not require statement terminator and
applicable to the sessions , those will be automatically cleared when session
was closed.
BREAK
This will be used to breakup the data depending on the grouping.
Syntax:
Break or bre [on
<column_name> on report]
COMPUTE
This will be used to perform group functions on the data.
Syntax:
Compute or comp [group_function
of column_name on breaking_column_name or
report]
TTITLE
This will give the top title for your report. You can on or off
the ttitle.
Syntax:
Ttitle or ttit [left |
center | right] title_name skip n other_characters
Ttitle or ttit [on or
off]
BTITLE
This will give the bottom title for your report. You can on or
off the btitle.
Syntax:
Btitle or btit [left |
center | right] title_name skip n other_characters
Btitle or btit
[on or off]
Ex:
SQL> bre on deptno skip 1 on
report
SQL> comp sum of sal on deptno
SQL> comp sum of sal on report
SQL> ttitle center 'EMPLOYEE
DETAILS' skip1 center '----------------'
SQL> btitle center '** THANKQ
**'
SQL> select * from emp order
by deptno;
Output:
EMPLOYEE DETAILS
-----------------------
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM DEPTNO
----------
---------- --------- ------- -------------- -------- ---------- ----------
7782 CLARK MANAGER
7839 09-JUN-81 2450 10
7839 KING
PRESIDENT 17-NOV-81 5000
7934 MILLER CLERK 7782 23-JAN-82 1300
---------- **********
8750 sum
7369 SMITH
CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100
7902 FORD
ANALYST 7566 03-DEC-81 3000
7788 SCOTT
ANALYST 7566 19-APR-87 3000
7566 JONES
MANAGER 7839 02-APR-81 2975
---------- **********
10875 sum
7499 ALLEN
SALESMAN 7698 20-FEB-81
1600 300 30
7698 BLAKE
MANAGER 7839 01-MAY-81 2850
7654 MARTIN SALESMAN 7698 28-SEP-81
1250 1400
7900 JAMES CLERK 7698 03-DEC-81
950
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0
7521 WARD
SALESMAN 7698 22-FEB-81
1250 500
---------- **********
9400 sum
----------
sum
29025
** THANKQ **
CLEAR
This will clear the existing buffers or break or computations or
columns formatting.
Syntax:
Clear or cle buffer |
bre | comp | col;
Ex:
SQL> clear buffer
Buffer cleared
SQL> clear bre
Breaks cleared
SQL> clear comp
Computes cleared
SQL> clear col
Columns cleared
CHANGE
This will be used to replace any strings in SQL statements.
Syntax:
Change or c/old_string/new_string
If the old_string
repeats many times then new_string
replaces the first string only.
Ex:
SQL> select * from det;
select * from det
*
ERROR at line 1:
ORA-00942: table or view
does not exist
SQL> c/det/dept
1* select * from dept
SQL> /
DEPTNO
DNAME LOC
---------- ---------------- -----------
10 ACCOUNTING NEW
YORK
20 RESEARCH ALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
COLUMN
This will be used to increase or decrease the width of the table
columns.
Syntax:
Column or col <column_name> format <num_format|text_format>
Ex:
SQL> col deptno format 999
SQL> col dname format a10
SAVE
This will be used to save your current SQL statement as SQL
Script file.
Syntax:
Save or sav <file_name>.[extension]
replace or rep
If you want to save the filename with existing filename the you
have to use replace option.
By default it will take sql
as the extension.
Ex:
SQL> save ss
Created file ss.sql
SQL> save ss replace
Wrote
file ss.sql
EXECUTE
This will be used to execute stored subprograms or packaged
subprograms.
Syntax:
Execute or exec <subprogram_name>
Ex:
SQL> exec sample_proc
SPOOL
This will record the data when you spool on, upto when you say
spool off. By default it will give lst
as extension.
Syntax:
Spool on | off | out |
<file_name>.[Extension]
Ex:
SQL> spool on
SQL> select * from dept;
DEPTNO DNAME LOC
---------
-------------- ----------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
SQL> spool off
SQL> ed on.lst
SQL> select * from dept;
DEPTNO DNAME LOC
---------
-------------- ----------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
SQL> spool off
LIST
This will give the current SQL statement.
Syntax:
List or li [start_line_number] [end_line_number]
Ex:
SQL> select
2 *
3 from
4 dept;
SQL> list
1 select
2 *
3 from
4* dept
SQL> list 1
1* select
SQL> list 3
3* from
SQL> list 1 3
1 select
2 *
3* from
INPUT
This will insert the new line to the current SQL statement.
Syntax:
Input or in <string>
Ex:
SQL> select *
SQL> list
1* select *
SQL> input from dept
SQL> list
1
select *
2* from dept
APPEND
This will adds a new string to the existing string in the SQL statement without any
space.
Syntax:
Append or app <string>
Ex:
SQL> select *
SQL> list
1* select *
SQL> append from dept
1* select * from dept
SQL> list
1* select * from dept
DELETE
This will delete the current SQL statement lines.
Syntax:
Delete or del <start_line_number> [<end_line_number>]
Ex:
SQL> select
2 *
3
from
4
dept
5
where
6
deptno
7
>10;
SQL> list
1
select
2 *
3
from
4
dept
5
where
6
deptno
7* >10
SQL> del 1
SQL> list
1 *
2
from
3
dept
4
where
5
deptno
6* >10
SQL> del 2
SQL> list
1 *
2
dept
3
where
4
deptno
5* >10
SQL> del 2 4
SQL> list
1 *
2* >10
SQL> del
SQL> list
1 *
VARIABLE
This will be used to declare a variable.
Syntax:
Variable or var <variable_name> <variable_type>
Ex:
SQL> var dept_name varchar(15)
SQL> select dname into
dept_name from dept where deptno = 10;
PRINT
This will be used to print the output of the variables that will
be declared at SQL level.
Syntax:
Print <variable_name>
Ex:
SQL> print dept_name
DEPT_NAME
--------------
ACCOUNTING
START
This will be used to execute SQL scripts.
Syntax:
start <filename_name>.sql
Ex:
SQL> start ss.sql
SQL> @ss.sql -- this will execute sql script files only.
HOST
This will be used to interact with the OS level from SQL.
Syntax:
Host [operation]
Ex:
SQL> host
SQL> host dir
SHOW
Using this, you can see several commands that use the set
command and status.
Syntax:
Show all | <set_command>
Ex:
SQL> show all
appinfo is OFF and set
to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator
"." (hex 2e)
btitle OFF and is the
first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version
NATIVE
concat "."
(hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&"
(hex 26)
describe DEPTH 1 LINENUM
OFF INDENT ON
echo OFF
editfile
"afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
SQL> sho verify
verify OFF
RUN
This will runs the command in the buffer.
Syntax:
Run | /
Ex:
SQL> run
SQL> /
STORE
This will save all the set command statuses in a file.
Syntax:
Store set
<filename>.[extension] [create] | [replace] | [append]
Ex:
SQL> store set
my_settings.scmd
Created file
my_settings.scmd
SQL> store set my_settings.cmd
replace
Wrote file
my_settings.cmd
SQL> store set my_settings.cmd
append
Appended file to
my_settings.cmd
FOLD_AFTER
This will fold the columns one after the other.
Syntax:
Column <column_name> fold_after [no_of_lines]
Ex:
SQL> col deptno fold_after 1
SQL> col dname fold_after 1
SQL> col loc fold_after 1
SQL> set heading off
SQL> select * from dept;
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
FOLD_BEFORE
This will fold the columns one before the other.
Syntax:
Column <column_name> fold_before [no_of_lines]
DEFINE
This will give the list of all the variables currently defined.
Syntax:
Define [variable_name]
Ex:
SQL> define
DEFINE _DATE = "16-MAY-07" (CHAR)
DEFINE
_CONNECT_IDENTIFIER = "oracle" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE
= "1001000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 –
Production
With the Partitioning, OLAP and Data Mining
options" (CHAR)
DEFINE _O_RELEASE = "1001000200" (CHAR)
SET COMMANDS
These commands does not require statement terminator and
applicable to the sessions , those will be automatically cleared when session
was closed.
LINESIZE
This will be used to set the linesize. Default linesize is 80.
Syntax:
Set linesize <value>
Ex:
SQL> set linesize 100
PAGESIZE
This will be used to set the pagesize. Default pagesize is 14.
Syntax:
Set pagesize <value>
Ex:
SQL> set pagesize 30
DESCRIBE
This will be used to see the object’s structure.
Syntax:
Describe or desc <object_name>
Ex:
SQL> desc dept
Name
Null? Type
-----------------------------------------------------------------
---------------------
DEPTNO
NOT NULL NUMBER(2)
DNAME
VARCHAR2(14)
LOC VARCHAR2(13)
PAUSE
When the displayed data
contains hundreds or thousands of lines, when you select it then it will
automatically scrolls and displays the last page data. To prevent this you can
use this pause option. By using this it will display the data correspoinding to
the pagesize with a break which will continue by hitting the return key. By
default this will be off.
Syntax:
Set pause on | off
Ex:
SQL> set pause on
FEEDBACK
This will give the
information regarding howmany rows you selected the object. By default the
feedback message will be displayed, only when the object contains more than 5
rows.
Syntax:
Set feedback <value>
Ex:
SQL> set feedback 4
SQL> select * from dept;
DEPTNO DNAME
LOC
----------
-------------- -------------
10 ACCOUNTING
NEW YORK
20 RESEARCH
DALLAS
30 SALES
CHICAGO
40 OPERATIONS
BOSTON
4 rows selected.
HEADING
If you want to display data without headings, then you can
achieve with this. By default heading is on.
Syntax:
Set heading on | off
Ex:
SQL> set heading off
SQL> select * from dept;
10 ACCOUNTING
NEW YORK
20 RESEARCH
DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON
SERVEROUTPUT
This will be used to display the output of the PL/SQL programs.
By default this will be off.
Syntax:
Set serveroutput on |
off
Ex:
SQL> set serveroutput on
TIME
This will be used to display the time. By default this will be
off.
Syntax:
Set time on | off
Ex:
SQL> set time on
19:56:33 SQL>
TIMING
This will give the time taken to execute the current SQL statement. By default
this will be off.
Syntax:
Set timing on | off
Ex:
SQL> set timing on
SQL> select * from dept;
DEPTNO DNAME
LOC
----------
-------------- -------------
10 ACCOUNTING
NEW YORK
20 RESEARCH
DALLAS
30 SALES
CHICAGO
40 OPERATIONS
BOSTON
Elapsed: 00:00:00.06
SQLPROMPT
This will be used to change the SQL prompt.
Syntax:
Set sqlprompt <prompt>
Ex:
SQL> set sqlprompt 'ORACLE>'
ORACLE>
SQLCASE
This will be used to change the case of the SQL statements. By default
the case is mixed.
Syntax:
Set sqlcase upper |
mixed | lower
Ex:
SQL> set sqlcase upper
SQLTERMINATOR
This will be used to change the terminator of the SQL statements. By default
the terminator is ;.
Syntax:
Set sqlterminator <termination_character>
Ex:
SQL> set sqlterminator :
SQL> select * from dept:
DEFINE
By default if the &
character finds then it will treat as bind variable and ask for the input.
Suppose your want to treat it as a normal character while inserting data, then
you can prevent this by using the define option. By default this will be on
Syntax:
Set define on | off
Ex:
SQL>insert into dept
values(50,'R&D','HYD');
Enter value for d:
old 1: insert into dept
values(50,'R&D','HYD')
new 1: INSERT INTO DEPT VALUES(50,'R','HYD')
SQL> set define off
SQL>insert into dept
values(50,'R&D','HYD'); -- here it won’t ask for
value
NEWPAGE
This will shows how many blank lines will be left before the
report. By default it will leave one blank line.
Syntax:
Set newpage <value>
Ex:
SQL> set newpage 10
The zero value for
newpage does not produce zero blank lines instead it switches to a special
property which produces a top-of-form character (hex 13) just before the date
on each page. Most modern printers respond to this by moving immediately to the
top of the next page, where the priting of the report will begin.
HEADSEP
This allow you to indicate where you want to break a page title
or a column heading that runs longer than one line. The default heading
separator is vertical bar (|).
Syntax:
Set headsep <separation_char>
Ex:
SQL> select * from dept;
DEPTNO DNAME
LOC
----------
-------------- -------------
10 ACCOUNTING
NEW YORK
20 RESEARCH
DALLAS
30 SALES
CHICAGO
40 OPERATIONS BOSTON
SQL> set headsetp !
SQL> col dname heading
'DEPARTMENT ! NAME'
SQL> /
DEPARTMENT
DEPTNO NAME LOC
----------
----------------- ----------
10 ACCOUNTING
NEW YORK
20 RESEARCH
DALLAS
30 SALES
CHICAGO
40 OPERATIONS
BOSTON
ECHO
When using a bind variable, the SQL statement is maintained by echo. By default this is off.
Syntax:
Set echo on | off
VERIFY
When using a bind variable, the old and new statements will be
maintained by verify. By default this is on.
Syntax:
Set verify on | off
Ex:
SQL> select * from dept where
deptno = &dno;
Enter value for dno: 10
old 1: select * from dept where deptno =
&dno
new 1: select * from dept where deptno = 10
DEPTNO DNAME LOC
---------- ---------------- -----------
10 ACCOUNTING NEW
YORK
SQL> set verify off
SQL> select * from dept where
deptno = &dno;
Enter value for dno: 20
DEPTNO DNAME
LOC
---------- ------------- -----------
20 RESEARCH
DALLAS
PNO
This will give displays the page numbers. By default the value
would be zero.
Ex:
SQL> col hiredate new_value
xtoday noprint format a1 trunc
SQL> ttitle left xtoday right
'page' sql.pno
SQL> select * from emp where
deptno = 10;
09-JUN-81
page 1
EMPNO ENAME JOB MGR SAL
COMM DEPTNO
---------- ----------
--------------- --------- ----- ---------- ----------
7782 CLARK MANAGER
7839 2450 10
7839 KING
PRESIDENT 5000 10
7934 MILLER
CLERK 7782 1300 10
In the above noprint
tells SQLPLUS not to display this
column when it prints the results of the SQL statement. Dates that have been reformatted by TO_CHAR get a default width of
about 100 characters. By changing the format to a1 trunc, you minimize this
effect. NEW_VALUE inserts contents of the column retrieved by the SQL statement
into a variable called xtoday.
