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.
- CSE database students: follow the instructions on the Academic Oracle Service (AOS) page.
SQL*Plus does not support your Backspace and Delete keys. Workarounds:
- To erase single characters, press CTRL + BACKSPACE
- To erase entire words, press CTRL + W
- Change your password:
SQL> ALTER USER <username> IDENTIFIED BY "<new password>";
- Show all your tables:
SQL> SELECT * FROM cat;
- Show all rows (entries) in table tablename:
SQL> SELECT * FROM tablename;
- Show all column names (field names) in table tablename:
SQL> DESCRIBE tablename;
- 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
- 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
- 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
- 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:
- Find the number of rows returned by an unconstrained SELECT:
SQL> select count(*) from fin.spo_award_hist where entity='1079';
COUNT(*)
----------
471
- 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
- http://en.wikipedia.org/wiki/SQL*Plus
- http://www.orafaq.com/wiki/SQL*Plus
- http://wings.buffalo.edu/computing/documentation/unix/oracle.html