Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 3 April 2016

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)
Share this article :

0 comments:

Post a Comment