SQL*Plus Commands

SQL*Plus commands

You can view the full document at: https://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm

The following commands can be issued in SQL*Plus (in addition to the standard SQL commands.)

  @ pathname  Run an SQL Script (see START)
              @MyScript.sql parameter1 parameter2 parameter3 
              In the SQL-Script, refer to the parameters as &1, &2, and &3.

  @@ pathname Run a nested SQL Script.

  /           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

  DEFINE_EDITOR = sql*plus editor (e.g. DEFINE_EDITOR=vim.exe)

  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 (exporting)

  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 variable or passed parameter (see DEFINE)

  VARIABLE    Define a bind variable
              (Can be used in both SQLPlus and PLSQL)
              VAR[IABLE] [variable {NUMBER|CHAR|CHAR (n)}]
              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

Some of the set/show options:

show spool -- show whether output is being spooled.
show {system_variable} -- show the value in any system variables set by the SET command.
show USER -- shows the username you are currently using to access SQL*Plus.
SET HEADING {ON|OFF} -- turn on/off the display of the column headings in reports
SET PAGESIZE {24|n} -- set the number of lines in each page. You can set pagesize to zero to suppress all headings, page breaks, titles, the initital blank line, and other formatting information.
For example, "set pagesize 100" will show the column header every 100 tuples
SET LINESIZE {80|n} -- sets the total number of characters to be displayed on one line before starting a new line
SET TERMOUT {ON|OFF} -- Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.
SET FEEDBACK {ON|OFF} -- turn on/off the display of the number of records returned by a query
SET COLSEP { |text} -- Use the parameter characters as column separator to display query results
set newpage {1|n|none} -- set the number of blank lines to be printed from the top of each page to the top title
set underline {ON|OFF} -- controls whether to underline column headings in reports.

Useful sql:

select * from session_privs; -- check the current user's privilege.