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
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
SQL> list
1 *
2 from
3 dept
4 where
5 deptno
6*
>10
SQL> del
SQL> list
1 *
2 dept
3 where
4 deptno
5*
>10
SQL> del
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
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
20
RESEARCH
30
SALES
40
OPERATIONS
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
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