SQL*PLUS Commands

SQL*PLUS COMMNANDS

These commands do 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)

Leave a Reply