|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
SQL*PLUS is a rudimentary tool to execute sql statements against Oracle.
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.
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
On HP-UX, one can call sql*plus from ied (something like
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.
With autotrace on, sqlplus automatically explains the plan for an SQL statement entered and displays the relevant statistics.
beautifying sqlplus output
@, @@ and start
Using bind variables in SQL plus