Saturday, 17 September 2011

Oracle SQL*Plus Essentials

The fundamental interface to an Oracle database is an Oracle command-line utility program called SQL*Plus. This utility is available at every Oracle site and it is a critical tool for virtually every Oracle user. Despite its wide use, few developers and DBAs know how powerful this tool can be. Below are the most popular SQL*Plus commands that one should dare to learn.

sqlplus <username>[/password]@<net_service_name> [as sysdba | as sysoper]  
Start an SQL*Plus session with SYSDBA or SYSOPER privileges connected to the Oracle Net database alias (@net_service_name). 

The password is also optional in the first command and if not written here it will not be echoed back to the user in clear text. 
exit or quit
Disconnect from the database and terminate SQL*Plus.

Execute a stored procedure
Runs the SQL*Plus statements in the specified script.
Similar to the previous statement but it is useful for running nested scripts because it looks for the specified script in the same path as the script which is was called.
This retrieves the contents of a file and places it in the SQL buffer.
Save the commands in the SQL buffer to a file.
spo[ol][<path_and_file_name>] [CREATE | REPLACE | APPEND ]] |OFF|OUT]    
Save SQL statements together with their output to a file or optionally sends the file to a printer.

E.g.: spool c:\test.txt
    select * from dual;
    spo off

    spo c:\test.txt app
    select object_id
      from user_objects
    spo off

run (short: /)
List and run the command stored in the SQL buffer
Edit the last command
Issue an operating system command