SQL*Plus commands

zhufeng518

　　The following commands can be issued in SQL*Plus (in addition to the standardSQL 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.
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.
　　

　　source:http://ss64.com/ora/syntax-sqlplus.html