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.
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 “/”.
SQL > SELECT * FROM tab1;
SQL > /
- 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.
SQL > SELECT c1, c2
> FROM tab2;
SQL > | (Uparrow) | (Downarrow)
- 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.
SQL > SELECT * FROM tab1;
SQL > SAVE tab1_qry.sql
SQL> SAVE D:qry_backup/tab1_qry.sql
SQL> SAVE D:qry_backup/tab1_qry.sql REPLACE
SQL > @tab1_qry
SQL > @tab1_qry.sql
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.
SPOOL D:qry_backup/spool_10112018_spool.sql [ APPEND ] [ APPEND is optional, it is used for appending the new scripts into existing SPOOL file. ]
Using Column format:
COL column_name FORMAT size_specification
SELECT * FROM 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
- 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
For more details on this topic please visit the link :
Connecting to Oracle using SQL* plus prompt: https://youtu.be/40CJQvr-98A