SQL* plus prompt, SQL Buffer, Using Spool file


For more details on this topic please visit the link : https://youtu.be/40CJQvr-98A

Connecting to Oracle using SQL* plus prompt

In windows environment:

START -> cmd prompt > -> SQLPLUS -> user_name – > Password
START -> cmd prompt > -> SQLPLUS -> user_name / Password
START -> cmd prompt > -> SQLPLUS -> user_name / Password @connection_string
Go to installation directory of oracle (inside BIN directory):
Search for “SQLPLUS” utility -> Double-click
Search for “SQLPLUS” utility -> Right-click -> send to desktop(create shortcut) – > Desktop – > Double-click on SQLPLUS shortcut.
If the shortcut is created on desktop then we can get the installation directory of Oracle by 
rightclick on shortcut – > Open file location.

SQL Buffer:

Once we execute a query in SQL* plus prompt and we got the result or error on the screen. next time we can re-execute the same query by pressing “/” (forward slash).
Now one can argue that from where that previously executed query is fetching from ?? Well, thats because the database server will keep a copy of the recently executed query is some temporary memory area which conceptually called as “SQL Buffer” in Oracle database. 


With the help of “SQL Buffer” we can perform below task:

  • We can re-execute the query using “/”.

Ex:   
SQL > SELECT * FROM tab1;
c1
—-
abc

SQL >  /
c1
—-
abc
  • We can re-execute the old query by pressing (^ up arrow) button, till we can fetch the line, but we wrote the query in multiple line, then we need to arrange multiple lines accordingly as per syntax.
Ex: 
SQL > SELECT c1, c2
        > FROM tab2;
c1     c2   
—–  ——-
1       ab
2       pq


             ^
SQL >   |  (Uparrow)  |  (Downarrow)
                                       v
  • We can save SQL Buffer text as SQL Script.
  • SAVE complete/file/path/file_name.extension  OR 
  • SAVE complete/file/path/file_name.extension  REPLACE.
  • Later we can re-execute the query by using the name of the script.

Ex:

    SQL > SELECT * FROM tab1;
    c1
    —-
    abc

    SQL > SAVE tab1_qry.sql           
    OR             
    SQL>  SAVE D:qry_backup/tab1_qry.sql
    OR
    SQL>  SAVE D:qry_backup/tab1_qry.sql REPLACE

    SQL > @tab1_qry                      
     OR            
    SQL >  @tab1_qry.sql
     OR
    SQL >  @D:qry_backup/tab1_qry.sql

    Using Spool file:

    The SPOOL is a utility provided by Oracle to keep track of the executed query.
    Ex:
    SPOOL ON 
    SPOOL D:qry_backup/spool_10112018_spool.sql  [ APPEND ]   [ APPEND  is optional, it is used for appending the new scripts into existing SPOOL file. ]
    query1
    result1
    query2
    result2
    query3
    result3
    SPOOL OFF  
    Using Column format:
    COL column_name FORMAT size_specification
    Ex:
    SELECT * FROM tab;
    desc tab;
    COL tname    FORMAT A15
    COL tabtype  FORMAT A15
    COL clusterid FORMAT 999
    SELECT * FROM Emp_tab;
    • If column value is longer then the column format then it will spilt the values into multiple lines as below.

    COL job FORMAT A5
            COL sal FORMAT 99999
    /
    job                  sal
    —–                ——
    SALES               9000
    MAN
    MANAG          30000
    ER
    • If we want to show all the numeric value with same size of length as 8000 as 008000 to match the length 6, we can change the column format as 099999 as below.

    COL job FORMAT A10
            COL sal FORMAT 099999
    /

    job                   sal
    ———-          ——-
    SALESMAN    009000
    MANAGER     030000

    For more details on this topic please visit the link : 

    Connecting to Oracle using SQL* plus prompt: https://youtu.be/40CJQvr-98A

    Using Spool file and COlumn format:  https://youtu.be/4OdYDjP-ezU

    1 thought on “SQL* plus prompt, SQL Buffer, Using Spool file”

    Leave a Comment

    Your email address will not be published. Required fields are marked *

    Twitter
    YouTube
    Pinterest
    LinkedIn