Introduction

SQL*Plus (commonly known as sqlplus) is an Oracle database client that can run SQL and PL/SQL commands and display their results. SQL*Plus is the default, the simplest and the most basic Oracle utility, with a basic command-line interface, commonly used by users, administrators, and programmers.

Usage

Notes

1. CSE database students: follow the instructions on the Academic Oracle Service (AOS) page.

SQL*Plus Annoyances

SQL*Plus does not support your Backspace and Delete keys. Workarounds:

1. To erase single characters, press CTRL + BACKSPACE
2. To erase entire words, press CTRL + W

SQL*Plus Syntax Examples

1. Change your password:

   
   SQL> ALTER USER <username> IDENTIFIED BY "<new password>";
   
   

2. Show all your tables:

   
   SQL> SELECT * FROM cat;
   
   

3. Show all rows (entries) in table tablename:

   
   SQL> SELECT * FROM tablename;
   
   

4. Show all column names (field names) in table tablename:

   
   SQL> DESCRIBE tablename;
   
   

5. Select data to an outfile (turn spooling on, issue a SELECT statement, turn spooling off):

   
   SQL>SPOOL /tmp/mydata.dat
   SQL>select myfield from userDB where IMSI like '22803%' and rownum < 11;
   SQL>SPOOL OFF
   

6. Suspend the display of search results at each page, requiring you to hit the 'enter' key to view the next page (set pause on, issue SELECT statement, set pause off):

   
   SQL> SET PAUSE ON
   SQL> SELECT * FROM tablename
   SQL> SET PAUSE OFF
   
   

7. When entering long SQL statements into SQL Plus, it is convenient to first type the statement(s) into a text file. Then issue all ORACLE statements within the file with:

   
   SQL> @myfile.sql
   
   

8. Oracle's CONCAT function only permits you to concatenate two strings. But if you want to concatenate more than three strings, use the pipe (||) operator. This is useful for finding a table's primary key:
   1. Find the number of rows returned by an unconstrained SELECT:

      
      SQL> select count(*) from fin.spo_award_hist where entity='1079';
      
        COUNT(*)
      ----------
             471
      
      

   2. Find the set of distinct fields that yields the same number of results:

      
      SQL> select count(distinct application_number ||award_dt ||curr_period_total)
        2  from fin.spo_award_hist
        3  where entity='1079';
      
      COUNT(DISTINCTAPPLICATION_NUMBER||AWARD_DT||CURR_PERIOD_TOTAL)
      --------------------------------------------------------------
                                                                 471
      
      

References

1. http://en.wikipedia.org/wiki/SQL*Plus
2. http://www.orafaq.com/wiki/SQL*Plus
3. http://wings.buffalo.edu/computing/documentation/unix/oracle.html