The following commands can be issued in SQL*Plus (in addition to the standard SQL commands.)
@pathname Run (START) an SQL Script @MyScript.sql parameter1 parameter2 parameter3 In the SQL-Script, refer to the parameters as &1, &2, and &3. @ScriptName.sql will call sub-scripts from the current working directory of SQL*Plus. @C:\work\oracle\ScriptName.sql will call a sub-script from a specific directory. @@pathname Run (START) an SQL Script @@ will call a sub-script from the same directory as the main script. &variable A substitution variable &&variable A substitution variable valid for the session. / Execute (or re-execute) commands in the SQL*Plus buffer does not list commands before running. ACCEPT User input ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FORMAT format] [DEFAULT default] [PROMPT text|NOPROMPT] [HIDE] APPEND Add text to the end of the current line in the buffer. A[PPEND] text_to_add BREAK Specify where and how formatting will change. BREAK ON {column|expr|ROW|REPORT} action BTITLE Place and format a title at the bottom of each page. BTITLE printspec [text|variable] BTITLE [OFF|ON] CHANGE Change text on the current line (change what you just typed.) C /oldval/newval CLEAR Clear the SQL*Plus screen and the screen buffer. CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING} COLUMN Change display width of a column. COMPUTE Calculate and display totals. CONNECT Connect to a database as a specified user: connect username/password@SID COPY Copy data from a query into a table (local or remote) DEFINE User variables: DEFINE varName = String Display a user variable: DEFINE varName Display all variables: DEFINE DEL Delete the current line in the SQL buffer. DESC[RIBE] Describe a table, column, view, synonym, function procedure, package or package contents. DISCONNECT Logoff (but don’t exit) EDIT Load the SQL*Plus buffer into an editor. By default, saves the file to AFIEDT.BUF EXECUTE Run a single PLSQL statement EXEC :answer := EMP_PAY.BONUS('SMITH') EXIT [n] Commit, logoff and exit (n = error code) EXIT SQL.SQLCODE GET file Retrieve a previously stored command file. HELP topic Topic is an SQL PLUS command or HELP COMMANDS HOST Execute a host Operating System command. HOST CD scripts INPUT Edit sql buffer - add line(s) to the buffer. LIST n m Edit sql buffer - display buffer lines n to m For all lines - specify m as LAST PAUSE message Wait for the user to hit RETURN. PRINT variable List the value of a bind variable or REF Cursor (see VARIABLE / SHOW) PROMPT message Echo a message to the screen. REMARK REMARK comment or --comment-- or /* comment */ RUN Execute (or re-execute) commands in the SQL*Plus buffer Lists the commands before running. RUNFORM Run a SQL*Forms application. SAVE file Save the contents of the SQL*Plus buffer in a command file. SAVE file [CRE[ATE] | REP[LACE] | APP[END]] SET Display or change SQL*Plus settings. SHOW List the value of a system variable (see PRINT) SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL] SPOOL file Store query results in file SPOOL OFF Turn off spooling SPOOL OUT sends file to printer SQLPLUS Start SQL*Plus and connect to a database. STA[RT] Run an SQL Script (see @) STARTUP [NoMOUNT|MOUNT|OPEN] TIMING Record timing data TIMING {START | SHOW | STOP} see CLEAR TIMING TTITLE Define a page title UNDEFINE Delete a user/substitution variable UNDEFINE varName (see DEFINE) VARIABLE Define a bind variable (Can be used in both SQLPlus and PL/SQL) VAR[IABLE] [variable {NUMBER|CHAR|CHAR(n)|REFCURSOR}] A RefCursor bind variable can be used to reference PL/SQL cursor variables in stored procedures. PRINT myRefCursor EXECUTE somePackage.someProcedure(:myRefCursor) VARIABLE on its own will display the definitions made. WHENEVER OSERROR Exit if an OS error occurs WHENEVER SQLERROR Exit if an SQL or PLSQL error occurs
SQL*Plus Prompt:
To display the currently connected UserName and SID, instead of just SQL>
SET sqlprompt '&_user:&_connect_identifier > '
Add the line above to the file: $ORACLE_SID/sqlplus/admin/glogin.sql
(this tip requires Oracle 10g or greater)
“Client Servers were a tremendous mistake and we are sorry that we sold it to you. Instead of applications running on the desktop and data sitting on the server, everything will be Internet based” ~ Larry Ellison, CEO, Oracle Corp.
Related Oracle Commands:
Editing SQL scripts in SQL*Plus
SQL-Plus.com - SQL*Plus Tutorial