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)
No comments:
Post a Comment