SQL * PLUS is an Oracle tool which accepts SQL, SQLPLUS commands and PL/SQL blocks and executes them.
Access to SQL*PLUS: Get your oracle user name/password and connect string from your DBA/Instructor. Click on SQL-plus shortcut. In the dialog box provide appropriate information like your_oracle_user_name/your_oracle_password. If the information is accurate then you will get a window with SQL> prompt. Now SQL*PLUS is up and ready to accept your commands.
Enter/Edit/Store/Retrieve/Run SQL commands:
A] To enter a SQL> command, type the command at the SQL> prompt. An SQL command can be entered continuously on a single line or it can be spread over multiple lines. In case of any typing error, use BACKSPACE or DEL key to erase and then continue typing. In the explanation given below we assume that emp table exists in our database.
SQL> select empno, hiredate from emp where hiredate > ’01-jan-82’;
Can also be entered as
SQL> SELECT empno, hiredate
FROM emp
WHERE hiredate > ’01-jan-82’;
The semicolon “;” at the end of the commands is the command terminator. The indentation is for sake of clarity and is optional.
There are many ways to terminate a command, namely
1) A semicolon at the end of the line itself.
2) A semicolon on a line by itself.
3) A slash ‘/’ on a line by itself.
4) A blank line.
Whenever an SQL command is issued, the command is stored in memory, in an area called “SQL buffer”. If a semicolon or slash is used as command terminator, then the command is executed immediately and then stored in “SQL Buffer”. When a blank line is used as command terminator, then the command is not executed immediately. It is merely stored in buffer.
Type the following command at the SQL prompt:
SQL> Select *
From emp
Where hiredate > ’01-jan-82’
B] To see the SQL command in the buffer
SQL> LIST
C] To save the buffer contents into a file
SQL> SAVE file_name
If the user does not supply an extension, then ORACLE adds sql as default extension.
D] To load the contents of a file into the memory after flushing the buffer
SQL> CLEAR BUFFER
SQL> GET file_name
E] To load the contents of a file into memory and execute the commands in the file
SQL>@ filename
Or
SQL>START file_name
G] To execute the commands in the buffer
SQL>RUN
Or
SQL>/
RUN displays the buffer content before execution whereas / will merely execute the buffer content.
H] To store/ print the queries and the output
SQL>SPOOL [file_name OFF OUT]
SPOOL file_name: will open a file to send the query and the output.
SPOOL OUT: will send the current spool file for printing and stop further spooling.
SPOOL OFF: stops spooling.
To list the columns of a table:
SQL> DESC Table_name
Using Substitution Variables:
Instead of using a table name or a column name or a value in the SQL, we can use a variable.
SELECT empno, ename, sal FROM emp where deptno =10 and job = ‘CLERK’
Can be written as
SELECT empno, ename, sal from emp where deptno= &V_deptno and Job = ‘&V_JOB’
When the query is issued SQL*PLUS will respond with values to be entered for deptno and Job.
V_deptno and V_job variables whose values will be obtained at runtime and substituted in the SQL statement before execution of the statement and the ampersand & before the variable name indicates that, what follows is a variable and not a value. Since there is no direct substitution, quotes are not required.
Pause Command: PAUSE command is used to give a message to the user. Further processing is stopped until user acknowledges the message by pressing the ENTER key. The syntax is
PAUSE Message_String
PAUSE “PRESS ENTER KEY TO PROCEED”
Press enter key to proceed (systems waits till you press the Enter key)
