Tuesday, February 7, 2017

SQL*Plus important SET Commands


set pages  or pagesize - to determine how many lines SQL*Plus will print to the screen before starting a new page and reprinting the column headings.  To turn off column headings, set pages to zero.  To have the column headings appear only once, set pages very high (pages 999). pages defaults to 14
set line or linesize - to determine how long a line is.  It defaults to 80 characters.
set feedback on/off  - This is the comment at the end of the listing that tells how many rows are returned.  If feedback off and query returns no rows, it returns to the SQL prompt.
set timing on - it times the response time for SQL and PL/SQL, used for tuning.
set verify off/on - to enable/disable feedback for substitution characters (with ampersands)
set trims or trimspool - trimspool causes SQL*Plus not to fill spaces at the end of a line when spooling the results.
help set - "help set" command to see all of the set commands

Other SET Commands
- SET ECHO {ON|OFF} lists each command in a script as the command is executed.

- SET FEED[BACK] {6|n|ON|OFF} Displays the number of records returned by a script when a script selects at least n records.

- HEADS [EP] {| | c | OFF | ON} defines a column heading.

- SET NULL controls the text displayed for all null values for all columns.

- SET NUMF[ORMAT] Sets the default format for displaying numbers.

- SET UND[ERLINE] {-|c|ON|OFF} underline the headings of the columns.

- SET VER[IFY] {ON|OFF} lists the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values.

- SET TAB {ON|OFF} formats white space in terminal output.

- SET TERM[OUT] {ON|OFF} display of output generated by commands executed from a script.

- SET TI[ME] {ON|OFF} display the current time.

- SET TRIM[OUT] {ON|OFF} ON removes blanks at the end of each line. OFF allows SQL*Plus to include trailing blanks.

- SET PAGES[IZE] {24|n} Sets the number of lines in each page.
              

- SET INSTANCE [instance_path|LOCAL] Modify the default instance for your session to the specified instance path.

- SET RECSEP {WR [APPED] | SHE [CH] | OFF} make the record separation.

- SET RECSEPCHAR {_ | c} sets the character to display or print to separate records.

- SET SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} Converts the case of SQL commands and PL/SQL blocks.

- SET SQLP[ROMPT] {SQL>|text} Sets the SQL*Plus command prompt.

- SET WRA [P] {OFF | ON} wraps the string within the column bounds (truncate the string).

Some Useful References: