SQL*PLUS COMMNANDS
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