SQLPLUS Hints

- SQL PLUS format 
# rpm -Uvh http://linuxdownload.adobe.com/linux/x86_64//flash-player-npapi-27.0.0.130-release.x86_64.rpm
# rpm -Uvh http://dl.fedoraproject.org/pub/epel/7/x86_64/r/rlwrap-0.42-1.el7.x86_64.rpm
$ echo "alias sqlplus='rlwrap sqlplus' " >> /home/oracle/.bash_profile

$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
SET SERVEROUTPUT ON
SET HEADING ON
SET ECHO ON
SET PAGESIZE 50000
SET PAGES 0
SET LINESIZE 80
DEFINE _EDITOR = gedit
SET COLSEP "|"
SET CONCAT "."
SET CONCAT "!"
SET NEWPAGE NONE
SET TAB OFF
SET NULL **NULL**

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

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)