René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

SQL*Plus

SQL*PLUS is a rudimentary tool to execute sql statements against Oracle.

Starting SQL*Plus

sqlplus username/password@net_service_name
sqlplus username/password

sqlplus sys/password@net_service_name as sysdba
sqlplus sys/password as sysdba

sqlplus / as sysdba
Only users that have been granted the SYSDBA or SYSOPER privilege can connect as sysdba or as sysoper.
When SQL*Plus is started, it reads (and executes) the commands found in glogin.sql and login.sql. This mechanism allows to set one's preferred settings.

Executing SQL Commands

In order to execute an SQL command with SQL*Plus, it must be entered, followed by a semicolon (;), followed by pressing enter;

Editing the most recent SQL comand

The most recent SQL command can be edited with edit.

Interaction with the user

prompt, accept and pause are three commands of SQL*Plus that allow to interact with the SQL*Plus user.

History function

Unfortunately, there is no built in history functionality in SQL*PLUS that would allow to go back a number of commands issued in SQL*PLUS. However, there is gqlplus (on sourceforge) that enhances sql plus with a history functionality.
With cygwin (as apparently also with linux and possibly other Unix variants), rlwrap can also be used. SQL*Plus is then called like $ rlwrap sqlplus user/password@sid. rlwrap is a wrapper around readline.
On HP-UX, one can call sql*plus from ied (something like ied sqlplus usr/pass@srv
There seems also to be an ied equivalent in perl: feb.tar.gz
On windows, the function key F7 gets a list of previously executed commands. Also, if a letter is already entered, it gets the list with commands starting with that letter. Also the arrow keys work. Note: This works only with the command line sqlplus (sqlplus.exe), not with the gui version (sqlplusw.exe).
On emacs, M-x sql-oracle does the trick.
On 9i, iSQL Plus seems capable of exactly this.

Misc


archive_log
With autotrace on, sqlplus automatically explains the plan for an SQL statement entered and displays the relevant statistics.
beautifying sqlplus output

Commands

Other stuff