Miscellaneous Commands

•  ACCEPT variable number or char PROMPT text ACCEPT receives input from the terminal and places the contents in variable. This variable can already have been defined with the DEFINE command. If the PROMPT option is specified, the text will be displayed after skipping a line. The variable attributes of number or char can be defined at this time. The variable will be a char if not otherwise defined.

•  DEFINE variable DEFINE creates a user-defined variable and assigns it to be of char (character) format. This variable can be assigned a default value at this time.

---

Tip:  
I find these DEFINE statements handy for assigning a variable_name to the input parameters coming into the SQL*Plus command file. Example: DEFINE SYSTEM_NAME = &1. This would create a character variable SYSTEM_NAME and assign it the text associated with the first input parameter. This makes SQL*Plus command file code easier to follow.

---

•  DESC or DESCRIBE database object Displays the columns associated with a table, view, or synonym.

•  PAUSE text PAUSE prints the contents of text after skipping a line, and then waits for the Return or Enter key to be pressed.

•  PROMPT text PROMPT simply skips a line and prints the contents of text.

•  REM or REMARKSQL*Plus will ignore the contents of this line when used in SQL*Plus command files. REMARK allows for documentation or other comments to be contained in these SQL*Plus command files.

•  SET SQL*Plus System variableThe SET command controls the default settings for the SQL*Plus environment. These settings can be altered automatically for each SQL*Plus session by including them in the LOGIN.SQL file, discussed earlier in this chapter. See Chapter 6 of Oracle’s SQL*Plus User’s Guide and Reference for a complete listing of the SET options.
Some common SET options utilized for reporting are:

SET LINESIZE 80 Controls the width of the output report line

SET PAGESIZE 55 Controls the number of lines per page

Some common SET options to suppress various SQL*Plus output are:

SET FEEDBACK OFF Suppresses the number of rows returning messages

SET VERIFY OFF Suppresses the substitution text when using &variables

SET TERMOUT OFF Suppresses all terminal output, particularly useful in conjunction with the SPOOL command

SET ECHO OFF Supresses the display of SQL*Plus commands

•  SPOOL filename or optionsOpens, closes, or prints an operating system- dependent file. Specifying SPOOL filename will create an operating system- dependent file where filename can contain the full path name of the file and extension. If no file extension is given, the contents of system variable SUFFIX will be appended (filename.SQL). Options include OFF or OUT. If OFF is specified, the operating system-dependent file is simply closed. If OUT is specified, the operating system- dependent file is closed and sent to the operating system-dependent printer assigned as the default printer to the user’s operating system environment.

---

Note:  
If SPOOL filename is issued without issuing a SPOOL OFF or SPOOL OUT, the current operating system-dependent file is closed and the new one as specified by the SPOOL command is opened.

---

---

Tip:  
I prefer to write SQL*Plus-based reports utilizing the SET variables mentioned above in conjuction with the SPOOL command. I create the output report in the file specified by the SPOOL command and then control whether it is visually displayed to the terminal, optionally printed, or both by the use of operating system-dependent command language.

---

•  UNDEFINE variable UNDEFINE removes the previously DEFINEd variable from the SQL*Plus environment.