1. SET APPI[NFO]{ON | OFF | text}
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
Example
To display the value of APPINFO, as it is SET OFF by default, enter
SET APPINFO ON
SHOW APPINFO
Example:
To change the default text, enter
SET APPINFO 'This is SQL*Plus'
PRINT MOD
Example:
To change APPINFO back to its default setting, enter
SET APPINFO OFF
2. SET ARRAY[SIZE] {15 | n}
Sets the number of rows that SQL*Plus will fetch from the database at one time.
Valid values are 1 to 5000.
A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory.
3. SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.
Example:
To check the status of autocommit command.
SHOW AUTOCOMMIT;
To set the status on of autocommit command.
SET AUTOCOMMIT ON;
To set indicates number of commands after which oracle will issue a commit.
SET AUTOCOMMIT 5;
4. SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
ON or OFF controls whether SQL*Plus automatically displays bind variables.
Examples
VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/
SQL> PRINT n
5. SET AUTORECOVERY [ON | OFF]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given.
Example
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON
RECOVER DATABASE
6. SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (such as SELECT, INSERT, UPDATE, DELETE or MERGE).
The report can include execution statistics and the query execution path.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
Example:
No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE OFF
The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON STATISTICS
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE ON
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
SET AUTOTRACE TRACEONLY
7. SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the character used to end PL/SQL blocks to c.
It cannot be an alphanumeric character or a whitespace.
To execute the block, you must issue a RUN or / (slash) command.
OFF means that SQL*Plus recognizes no PL/SQL block terminator.
ON changes the value of c back to the default period (.), not the most recently used character.
8. SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
ON or OFF controls whether you can enter multiple commands on a line.
ON automatically sets the command separator character to a semicolon (;).
Example
To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter
SET CMDSEP +
TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SH_CLERK';
9. SET COLSEP { | text}
Sets the column separator character printed between columns in output.
If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes.
The default value for text is a single space.
Example
To set the column separator to "|" enter
SET MARKUP HTML PREFORMAT ON
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;
10. SET CON[CAT] {. | c | ON | OFF}
Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.
SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
11. SET COPYC[OMMIT] {0 | n}
Controls the number of rows after which the COPY command commits changes to the database.
COPY commits rows to the destination database each time it copies n row batches.
Valid values are zero to 5000.
You can set the size of a batch with the ARRAYSIZE variable.
If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.
12. SET COPYTYPECHECK {ON | OFF}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.
13. SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix substitution variables to c.
ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values.
ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.
14. SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
Example
To create an object type ADDRESS, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter
CREATE TABLE EMPLOYEE
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/
To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:
SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
DESCRIBE employee
15. SET ECHO {ON | OFF}
Controls whether or not to echo commands in a script that is executed with @, @@ or START.
ON displays the commands on screen.
OFF suppresses the display.
ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.
16. SET EDITF[ILE] file_name[.ext]
Sets the default filename for the EDIT command.
The default filename for the EDIT command is afiedt.buf which is the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands.
You can include a path and/or file extension.
See SET SUF[FIX] {SQL | text} for information on changing the default extension.
The default filename and maximum filename length are operating system specific.
17. SET EMB[EDDED] {ON | OFF}
Controls where on a page each report begins.
OFF forces each report to start at the top of a new page.
ON enables a report to begin anywhere on a page.
18. SET ESC[APE] {\ | c | ON | OFF}
Defines the character used as the escape character.
OFF undefines the escape character.
ON enables the escape character.
ON changes the value of c back to the default "\".
Example
If you define the escape character as an exclamation point (!), then
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
Example:
To set the escape character back to the default value of \ (backslash), enter
SET ESCAPE ON
19. SET ESCCHAR {@ | ? | % | $ | OFF}
Specifies a character to be escaped and not interpreted when used in a file name for the SPOOL, START, @, RUN and EDIT commands.
Example
If you include the character '$' in your filename, then
SET ESCCHAR $
RUN MYFILE$
behaves normally.
20. SET EXITC[OMMIT] {ON | OFF}
Specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
The default setting is ON, which means that work is committed on exit, whether you expected it to be committed or not.
Set EXITCOMMIT OFF to rollback work on exit.
21. SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a script when a script selects at least n records.
ON or OFF turns this display on or off.
Turning feedback ON sets n to 1.
Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.
22. SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
ON prints column headings in reports;
OFF suppresses column headings.
The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.
Example
To suppress the display of column headings in a report, enter
SET HEADING OFF
If you then run a SQL SELECT command
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';
To turn the display of column headings back on, enter
SET HEADING ON
23. SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
Outputs HTML marked up text.
Example
The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:
SET MARKUP HTML ON SPOOL ON HEAD "SQL*Plus Report -
"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
START employee.sql
24. SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. The DBMS_OUTPUT line length limit is 32767 bytes.
OFF suppresses the output of DBMS_OUTPUT.PUT_LINE. ON displays the output.
Example
To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter
SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete');
END;
/
Task is complete.
PL/SQL procedure successfully completed.
25. SET SQLP[ROMPT] {SQL> | text}
Sets the SQL*Plus command prompt. SET SQLPROMPT substitute variables dynamically.
This enables the inclusion of runtime variables such as the current connection identifier.
Substitution variables used in SQLPROMPT do not have to be prefixed with '&', and they can be used and accessed like any other substitution variable.
Variable substitution is not attempted for the default prompt, "SQL> ".
Example
To change your SQL*Plus prompt to display your connection identifier, enter:
SET SQLPROMPT "_CONNECT_IDENTIFIER > "
To set the SQL*Plus command prompt to show the current user, enter
SET SQLPROMPT "_USER > "
To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:
SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
To change your SQL*Plus prompt to display a variable you have defined, enter:
DEFINE mycon = Prod1
SET SQLPROMPT "mycon> "
Prod1>
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
Example
To display the value of APPINFO, as it is SET OFF by default, enter
SET APPINFO ON
SHOW APPINFO
Example:
To change the default text, enter
SET APPINFO 'This is SQL*Plus'
PRINT MOD
Example:
To change APPINFO back to its default setting, enter
SET APPINFO OFF
2. SET ARRAY[SIZE] {15 | n}
Sets the number of rows that SQL*Plus will fetch from the database at one time.
Valid values are 1 to 5000.
A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory.
3. SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database after SQL or PL/SQL commands.
Example:
To check the status of autocommit command.
SHOW AUTOCOMMIT;
To set the status on of autocommit command.
SET AUTOCOMMIT ON;
To set indicates number of commands after which oracle will issue a commit.
SET AUTOCOMMIT 5;
4. SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
ON or OFF controls whether SQL*Plus automatically displays bind variables.
Examples
VARIABLE n NUMBER
BEGIN
:n := 1;
END;
/
SQL> PRINT n
5. SET AUTORECOVERY [ON | OFF]
ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given.
Example
To set the recovery mode to AUTOMATIC, enter
SET AUTORECOVERY ON
RECOVER DATABASE
6. SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (such as SELECT, INSERT, UPDATE, DELETE or MERGE).
The report can include execution statistics and the query execution path.
SQL*Plus report output may differ for DML if dynamic sampling is in effect.
Example:
No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE OFF
The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON STATISTICS
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE ON
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
SET AUTOTRACE TRACEONLY
7. SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the character used to end PL/SQL blocks to c.
It cannot be an alphanumeric character or a whitespace.
To execute the block, you must issue a RUN or / (slash) command.
OFF means that SQL*Plus recognizes no PL/SQL block terminator.
ON changes the value of c back to the default period (.), not the most recently used character.
8. SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
ON or OFF controls whether you can enter multiple commands on a line.
ON automatically sets the command separator character to a semicolon (;).
Example
To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter
SET CMDSEP +
TTITLE LEFT 'SALARIES' + COLUMN SALARY FORMAT $99,999
SELECT LAST_NAME, SALARY FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'SH_CLERK';
9. SET COLSEP { | text}
Sets the column separator character printed between columns in output.
If the COLSEP variable contains blanks or punctuation characters, you must enclose it with single quotes.
The default value for text is a single space.
Example
To set the column separator to "|" enter
SET MARKUP HTML PREFORMAT ON
SET COLSEP '|'
SELECT LAST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 20;
10. SET CON[CAT] {. | c | ON | OFF}
Sets the character used to terminate a substitution variable reference when SQL*Plus would otherwise interpret the next character as a part of the variable name.
SQL*Plus resets the value of CONCAT to a period when you switch CONCAT on.
11. SET COPYC[OMMIT] {0 | n}
Controls the number of rows after which the COPY command commits changes to the database.
COPY commits rows to the destination database each time it copies n row batches.
Valid values are zero to 5000.
You can set the size of a batch with the ARRAYSIZE variable.
If you set COPYCOMMIT to zero, COPY performs a commit only at the end of a copy operation.
12. SET COPYTYPECHECK {ON | OFF}
Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
This is to facilitate copying to DB2, which requires that a CHAR be copied to a DB2 DATE.
13. SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix substitution variables to c.
ON or OFF controls whether SQL*Plus will scan commands for substitution variables and replace them with their values.
ON changes the value of c back to the default '&', not the most recently used character. The setting of DEFINE to OFF overrides the setting of the SCAN variable.
14. SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
Example
To create an object type ADDRESS, enter
CREATE TYPE ADDRESS AS OBJECT
( STREET VARCHAR2(20),
CITY VARCHAR2(20)
);
/
To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter
CREATE TABLE EMPLOYEE
(LAST_NAME VARCHAR2(30),
EMPADDR ADDRESS,
JOB_ID VARCHAR2(20),
SALARY NUMBER(7,2)
);
/
To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:
SET DESCRIBE DEPTH 2 LINENUM ON INDENT ON
DESCRIBE employee
15. SET ECHO {ON | OFF}
Controls whether or not to echo commands in a script that is executed with @, @@ or START.
ON displays the commands on screen.
OFF suppresses the display.
ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.
16. SET EDITF[ILE] file_name[.ext]
Sets the default filename for the EDIT command.
The default filename for the EDIT command is afiedt.buf which is the SQL buffer.
The buffer has no command history list and does not record SQL*Plus commands.
You can include a path and/or file extension.
See SET SUF[FIX] {SQL | text} for information on changing the default extension.
The default filename and maximum filename length are operating system specific.
17. SET EMB[EDDED] {ON | OFF}
Controls where on a page each report begins.
OFF forces each report to start at the top of a new page.
ON enables a report to begin anywhere on a page.
18. SET ESC[APE] {\ | c | ON | OFF}
Defines the character used as the escape character.
OFF undefines the escape character.
ON enables the escape character.
ON changes the value of c back to the default "\".
Example
If you define the escape character as an exclamation point (!), then
SET ESCAPE !
ACCEPT v1 PROMPT 'Enter !&1:'
displays this prompt:
Enter &1:
Example:
To set the escape character back to the default value of \ (backslash), enter
SET ESCAPE ON
19. SET ESCCHAR {@ | ? | % | $ | OFF}
Specifies a character to be escaped and not interpreted when used in a file name for the SPOOL, START, @, RUN and EDIT commands.
Example
If you include the character '$' in your filename, then
SET ESCCHAR $
RUN MYFILE$
behaves normally.
20. SET EXITC[OMMIT] {ON | OFF}
Specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
The default setting is ON, which means that work is committed on exit, whether you expected it to be committed or not.
Set EXITCOMMIT OFF to rollback work on exit.
21. SET FEED[BACK] {6 | n | ON | OFF}
Displays the number of records returned by a script when a script selects at least n records.
ON or OFF turns this display on or off.
Turning feedback ON sets n to 1.
Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.
22. SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
ON prints column headings in reports;
OFF suppresses column headings.
The SET HEADING OFF command does not affect the column width displayed, it only suppresses the printing of the column header itself.
Example
To suppress the display of column headings in a report, enter
SET HEADING OFF
If you then run a SQL SELECT command
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID = 'AC_MGR';
To turn the display of column headings back on, enter
SET HEADING ON
23. SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]
Outputs HTML marked up text.
Example
The following is a script which uses the SET MARKUP HTML command to enable HTML marked up text to be spooled to a specified file:
SET MARKUP HTML ON SPOOL ON HEAD "
"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
START employee.sql
24. SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. The DBMS_OUTPUT line length limit is 32767 bytes.
OFF suppresses the output of DBMS_OUTPUT.PUT_LINE. ON displays the output.
Example
To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter
SET SERVEROUTPUT ON
The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:
BEGIN
DBMS_OUTPUT.PUT_LINE('Task is complete');
END;
/
Task is complete.
PL/SQL procedure successfully completed.
25. SET SQLP[ROMPT] {SQL> | text}
Sets the SQL*Plus command prompt. SET SQLPROMPT substitute variables dynamically.
This enables the inclusion of runtime variables such as the current connection identifier.
Substitution variables used in SQLPROMPT do not have to be prefixed with '&', and they can be used and accessed like any other substitution variable.
Variable substitution is not attempted for the default prompt, "SQL> ".
Example
To change your SQL*Plus prompt to display your connection identifier, enter:
SET SQLPROMPT "_CONNECT_IDENTIFIER > "
To set the SQL*Plus command prompt to show the current user, enter
SET SQLPROMPT "_USER > "
To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:
SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
To change your SQL*Plus prompt to display a variable you have defined, enter:
DEFINE mycon = Prod1
SET SQLPROMPT "mycon> "
Prod1>
Tags:
DBMS