Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick

Get full access to Oracle SQL*Plus: The Definitive Guide, 2nd Edition and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

The SET DEFINE command changes the prefix character used to mark substitution variables. You can use SET DEFINE to turn variable substitution off.

Is the command, which may be abbreviated SET DEF.

Disables variable substitution.

Enables variable substitution, and resets the substitution prefix character back to the default ampersand (&) character. Variable substitution is on by default.

Is the new substitution prefix character.

When you start SQL*Plus, variable substitution will be on by default, and the default prefix character is an ampersand. If you are running a script that uses ampersands in text strings, you may want to change the prefix character to something else. If your script doesn't use substitution variables, you may find it easiest to turn the feature off.

Get Oracle SQL*Plus: The Definitive Guide, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Cover of Software Architecture Patterns

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

set define off

  • February 5, 2024

Knowledge Place

Unlocking the power of oracle: what does set define off do.

  • January 24, 2024

Title: What Does Set Define Off Do? A Beginner’s Guide to Oracle Set Define Off

Have you ever tried to run a SQL script in Oracle only to be met with unexpected results or error messages? It can be frustrating, especially when you’re not sure how to fix the issue. One common solution is to use the “set define off” command. But what does it actually do? In this article, we’ll explore everything you need to know about Oracle set define off.

Table of Contents:

  • Introduction

What is Oracle Set Define Off?

How does set define off work, when should you use set define off, how to use set define off, frequently asked questions.

Oracle Set Define Off is a command that disables the special meaning of the “&” character in SQL queries. In Oracle, “&” is a substitution character that prompts the user for a value and replaces that value in the query. In some cases, this can cause issues if you’re running a script or trying to execute a query with an “&” character that shouldn’t be replaced.

When you use “set define off”, Oracle will no longer prompt you for a value when it encounters an “&” character in a query. Instead, it will treat “&” as a regular character and leave it untouched. This can be particularly useful when you’re running scripts that contain string literals with “&” characters that shouldn’t be replaced.

You should use “set define off” when you’re running a script or executing a query that contains “&” characters that shouldn’t be replaced. This can include queries with string literals that contain “&” characters, or scripts that use “&” characters as part of a variable or function name.

The “set define off” command is simple to use. Just open an Oracle SQL command prompt and type “set define off” at the prompt. Press Enter to execute the command, and you’re ready to go.

Q: What is the difference between “set define off” and “set define on”?

A: “Set define off” disables the substitution character “&” in SQL queries, while “set define on” enables it. Use “set define off” when you don’t want to substitute “&” characters in a query.

Q: How can I tell if “set define off” is enabled?

A: You can check the current status of “set define” by typing “show define” at the SQL prompt. This will show you whether “set define” is currently on or off.

Q: Can I use “set define off” in a script?

A: Yes, you can use “set define off” in a script by adding it to the top of your script. This will disable “&” substitution for the duration of the script.

Q: What if I need to substitute “&” characters in certain queries?

A: You can still use substitution characters in queries even if “set define off” is enabled. To do this, simply add a backslash () before the “&” character to tell Oracle to treat it as a substitution character.

Oracle set define off is a useful command for disabling the substitution character “&” in SQL queries. This can be particularly helpful when you’re running scripts or executing queries that contain “&” characters that shouldn’t be replaced. By using “set define off”, you can ensure that your queries execute as intended without any unexpected prompts or errors.

  • Recent Posts
  • Discover the Shockingly Affordable: What’s the Cheapest Thing in the World? - February 5, 2024
  • Unraveling the Mystery: Do Easter Cactus Flowers Close at Night? - February 4, 2024
  • Get Ready to Squeal: How to Remove Hair from Pig’s Feet Like a Pro - February 4, 2024

The Enigma of Nathaniel’s Demise: Unraveling the Mystery of How Nathaniel Died

Unraveling the Mystery: Why are Pyramids Triangular in Shape?

  • Skip to content
  • Accessibility Policy
  • Oracle blogs
  • Lorem ipsum dolor

SQL*Plus Substitution Variables - DEFINE variables and parameters in SQL Queries

set define off

This post shows how substitution variables can replace hard-coded text in Oracle SQL and SQL*Plus statements.

Jump straight to the Substitution Variable Examples  if you don't have time to read this whole post, and are trying to solve a specific problem using variables in SQL statements in SQL*Plus.  Also check  Using Bind Variables  since bind variables are important for security and scalability.

1 Introduction

2.1 creating, showing and deleting substitution variables, 2.2 referencing substitution variables, 2.3 prompting for undefined variables, 2.4 difference between "&" and "&&" prefixes, 2.5 storing a query column value in a substitution variable, 2.6 predefined substitution variables, 2.7 script parameters, 2.8 more on substitution variables, 3.1 assigning substitution variables to bind variables, 3.2 assigning bind variables to substitution variables, 4.1.1 set concat, 4.1.2 set define, 4.1.3 set escape, 4.1.4 set null, 4.1.5 set numformat, 4.1.6 set numwidth, 4.1.7 set sqlprompt, 4.1.8 set verify, 4.2 system variables in titles and exit, 5.1 accept command, 5.2 column command, 5.3 define command, 5.4 edit command, 5.5 exit command, 5.6 host command, 5.7.1 using "&" prefixes with title variables, 5.7.2 variables and text spacing in titles, 5.8 undefine command, 5.9 whenever command, 6.1 substitution variable namespace, 6.2 substitution variable types, 6.3 substitution variable formats, 6.4 substitution variable limits.

  • 7.1 i SQL*Plus 9 and SQL*Plus Substitution Variable Compatibility

7.2 i SQL*Plus Parameters

8 substitution variable summary, 9.1 setting a substitution variable's value, 9.2 using a substitution variable, 9.3 finding all defined substitution variables, 9.4 inserting data containing "&" without being prompted, 9.5 putting the current date in a spool file name, 9.6 appending alphanumeric characters immediately after a substitution variable, 9.7 putting a period after a substitution variable, 9.8 using a fixed value variable in a ttitle, btitle, repheader or repfooter, 9.9 using a changing value variable in a ttitle, btitle, repheader or repfooter, 9.10 using the value of a bind variable in a sql*plus command like spool, 9.11 passing parameters to sql*plus substitution variables, 9.12 passing operating system variables to sql*plus, 9.13 passing a value to a pl/sql procedure from the command line, 9.14 allowing script parameters to be optional and have a default value, 9.15 passing a value to an i sql*plus dynamic report for the web, 9.16 customizing parameter prompts for an i sql*plus dynamic report for the web, 9.17 using a variable for the sql*plus return status, 9.18 putting the username and database in the prompt.

[This document was on OTN for many years but wasn't flagged for saving during an infrastructure migration in 2010. It is still useful because SQL*Plus is fundamentally unchanged. However check the SQL*Plus manual for changes and new features.]

This document explains how SQL*Plus substitution variables work and where they can be used. It shows the relationship between the three types of variable (substitution, bind, and system) used in SQL*Plus.

Substitution variables can replace SQL*Plus command options or other hard-coded text. They can be used to customize SQL*Plus script output. Substitution variable references in a statement are pre-processed and replaced before SQL*Plus executes the statement. Variable values can be pre-defined, prompted for, or set to script parameters. Variables can also hold values returned from queries. Sometimes substitution variables are known as user variables or define variables .

Bind variables store data values for SQL and PL/SQL statements executed in the RDBMS. They can hold single values or complete result sets.

System variables contain values directly controlling SQL*Plus, such as the line size and page size of reports. Some system variables affect how substitution variables are processed. System variables are sometimes called SET variables .

This document is a complete reference but you can understand and use substitution variables in a few minutes by reading the overview in Section 2 and looking at the examples in the last section.

Section 3 and Section 4 discuss bind and system variables respectively, and how they interact with substitution variables.

Section 5 discusses in detail all SQL*Plus commands that interact with substitution variables.

Section 6 gives substitution variable limits and some finer points.

Section 7 covers substitution variables in the i SQL*Plus web based interface.

Section 8 is a brief summary of substitution variables.

Section 9 gives a number examples of using substitution variables.

This document was updated in February 2004 for SQL*Plus Version 10.1. However all examples in this document apply to previous versions of SQL*Plus, except where noted.

Small sections were borrowed from the SQL*Plus User's Guide and Reference and the SQL*Plus FAQ. Some examples were derived from Support Notes and from questions posted on the i SQL*Plus Forum.

Thanks to all reviewers, especially Glenn Stokol, Sharon Castledine and the SQL*Plus Team.

2 Using Substitution Variables

Substitution variables can be explicitly created with the DEFINE command. Defining a variable means storing a value for future use:

This creates a variable called "myv" containing the text "King".

Another way to create substitution variables is with the ACCEPT command. This can be used to prompt for a value:

This command causes SQL*Plus to stop and prompt you to enter a character string:

What you enter is stored in the variable "myv2".

The DEFINE command can also be used to display known variables. It shows the variable name, value and type. Any variable that DEFINE lists is said to be defined :

All variables that are currently defined can be shown by executing the DEFINE command with no arguments:

Any variable not listed is undefined :

Substitution variables can be removed with the UNDEFINE command:

Variables can be referenced by prefixing their name with an ampersand (&):

SQL*Plus lists the statement line number and line containing the substitution variable "myv" before and after substitution:

Lines verifying substitution are displayed for SQL or PL/SQL statements. The lines can be hidden with SET VERIFY OFF . Verification never occurs for variables in SQL*Plus commands (e.g. SPOOL and SET).

A more practical use of substitution variables is to prompt for a value before referencing the variable:

If these two commands are stored in a SQL*Plus script, a different last name can be entered each time the script is run.

If a variable is referenced using an "&" prefix, but the variable value is not yet defined, SQL*Plus prompts you for a value:

After you enter a value, SQL*Plus substitutes the variable and executes the query.

The Oracle Globalization Language setting (e.g. the language component of the NLS_LANG environment variable) determines the exact language used for the "Enter value for" prompt. The prompt text cannot otherwise be changed.

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the variable is discarded and remains undefined. If the variable is referenced twice, even in the same statement, then you are prompted twice. Different values can be entered at each prompt:

If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value (i.e. the value is stored until you exit). Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again:

Data stored in the database can be put into substitution variables:

The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called "mynv". The variable is not physically created until a query references the column LAST_NAME. When the query finishes, the variable "mynv" holds the last retrieved value from column "last_name":

The predefined substitution variables created when you start SQL*Plus can be seen by entering DEFINE with no arguments. Each predefined variable is prefixed with an underscore. The predefined variables can be undefined or redefined just like user defined substitution variables.

In SQL*Plus the predefined variables are:

The variables _DATE, _PRIVILEGE, and _USER were introduced in SQL*Plus 10.1. The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2. The variable _SQL_ID was introduced in SQL*Plus 18.

_CONNECT_IDENTIFIER: The variable _CONNECT_IDENTIFIER contains the connection identifier used to start SQL*Plus. For example, if the SQL*Plus connection string is "hr/ my_password @MYSID" then the variable contains MYSID. If you use a complete Oracle Net connection string like "hr/ my_password @(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then _CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not explicitly specified then _CONNECT_IDENTIFIER contains the default connect identifier Oracle uses for connection. For example, on UNIX it will contain the value in the environment variable TWO_TASK or ORACLE_SID. If SQL*Plus is not connected then the variable is defined as an empty string.

_DATE: The variable _DATE can be either dynamic, showing the current date or it can be set to a fixed string. The date is formatted using the value of NLS_DATE_FORMAT and may show time information. By default a DEFINE or dereference using &_DATE will give the date at the time of use. _DATE can be UNDEFINED, or set to a fixed string with an explicit DEFINE command. Dynamic date behavior is re-enabled by defining _DATE to an empty string.

_EDITOR: The variable _EDITOR contains the external text editor executable name. See 5.4 EDIT Command.

_O_RELEASE: The variable _O_RELEASE contains contains a string representation of the Oracle database version number. If your Oracle database version is 9.2.0.3.0 then the variable contains "902000300". The Oracle version may be different from the SQL*Plus version if you use Oracle Net to connect to a remote database.

_O_VERSION: The variable _O_VERSION contains a text string showing the database version and available options.

_PRIVILEGE: When SQL*Plus is connected as a privileged user the variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS SYSOPER". If SQL*Plus is connected as a normal user the variable is defined as an empty string.

_SQLPLUS_RELEASE: The variable _SQLPLUS_RELEASE contains the SQL*Plus version number in a similar format to _O_RELEASE.

_USER: The variable _USER contains the current username given by SHOW USER. If SQL*Plus is not connected, the variable is defined as an empty string.

_SQL_ID: A SQL ID is a unique value that identifies a SQL statement for use in database monitoring. The SQL ID will be assigned to the predefined SQL*Plus variable _SQL_ID.

Parameters can be passed to SQL*Plus scripts. For example, from the command line:

You can also pass parameters when calling a SQL*Plus script from within a SQL*Plus session, for example:

Script parameters become defined substitution variables. The variable name for the first parameter is "1", the second is "2", etc. The effect is the same as starting SQL*Plus and typing:

Commands in myscript.sql can reference "&1" to get the value "King". A DEFINE command shows the parameter variable:

Script parameter variables have type CHAR, similar to variables explicitly created with DEFINE.

Quoting parameters with single or double quotes is allowed. This lets whitespace be used within parameters. Operating systems and scripting languages that call SQL*Plus handle quotes in different ways. They may or may not pass quotes to the SQL*Plus executable. For example, in a standard Bourne shell on UNIX, quotes around parameters are stripped before the parameters are passed to SQL*Plus, and SQL*Plus never sees the quotes.

It is recommended to check how quoted parameters are handled on your operating system with your patch level of SQL*Plus. For portability between UNIX and Windows environments use double quotes around parameters containing whitespace.

SQL*Plus Releases 8.1.7, 9.2.0.3 (and other 9.x versions patched for bug 2471872) and 10.1 onwards remove an outer set of single or double quotes from parameters passed on the SQL*Plus command line. This makes SQL*Plus behave the same way on operating systems that do not themselves strip quotes as it does when the operating system strips the quotes before calling SQL*Plus.

As an example of passing parameters, when SQL*Plus 10.1 is called in the UNIX shell script:

only one program parameter is defined. References in myscript.sql to "&1" are replaced with "Jack and Jill" (without quotes - because the shell script does not pass quotes to SQL*Plus).

From SQL*Plus Release 9.0 onwards, an empty string can be passed as a parameter.

Substitution variable references are pre-processed and substituted before the command is otherwise parsed and executed. For each statement SQL*Plus will:

Step 1 happens inside the SQL*Plus client tool. SQL*Plus then sends the final statement to the database engine where step 2 occurs.

It is not possible to repeatedly prompt in a PL/SQL loop. This example prompts once and the entered value is substituted in the script text. The resulting script is then sent to the database engine for execution. The same entered value is stored five times in the table:

Substitution variables are not recursively expanded. If the value of a referenced variable contains an ampersand, then the ampersand is used literally and is not treated as a second variable prefix:

You cannot use a substitution variable as the first token of a command. Each command name must be hard coded text otherwise an error is displayed. For example:

Substitution variables cannot be used in buffer editing commands like APPEND, CHANGE, DEL, and INPUT. Ampersands (&) in these commands are treated literally.

If you wish to use alphanumeric characters immediately after a substitution variable name, put the value of SET CONCAT - by default a period (.) - to separate the variable name from the following characters. For example, if "mycity" is defined as "Melbourne" then:

is the same as:

If you want to append a period immediately after a substitution variable name then use two periods together. For example, if "myfile" is defined as "reports" then the command:

Text in ANSI "/* */" or "--" comments that looks like a substitution variable may be treated as one, for example:

Here the text "& loc" in the comment is interpreted as a variable reference. SQL*Plus prompts you for a value for the variable "loc".

3 Using Bind Variables

Bind variables are used in SQL and PL/SQL statements for holding data or result sets. They are commonly used in SQL statements to optimize statement performance. A statement with a bind variable may be re-executed multiple times without needing to be re-parsed. Their values can be set and referenced in PL/SQL blocks. They can be referenced in SQL statements e.g. SELECT. Except in the VARIABLE and PRINT commands, bind variable references should be prefixed with a colon.

Bind variables are created with the VARIABLE command. The following PL/SQL block sets a bind variable:

Typically this is abbreviated as:

In SQL*Plus 12.2 you can do this shortcut to set a value:

Once a value is set, you can show it with the PRINT command.

Numeric bind variables can be used in the EXIT command to return a value to the operating system:

Other SQL*Plus commands do not recognize bind variables.

There is no way to undefine or delete a bind variable in a SQL*Plus session. However, bind variables are not remembered when you exit SQL*Plus.

For information about automatically displaying values and using REFCURSOR bind variables for whole result sets, see About Using Bind Variables and VARIABLE , in the SQL*Plus User's Guide and Reference .

You can assign a substitution variable to a bind variable:

SQL*Plus executes the PL/SQL assignment statement after it substitutes the value of "mysubv". If "mysubv" was not already defined, you would be prompted for a value.

The bind variable can be used in subsequent SQL or PL/SQL commands.

Sometimes it is useful to make the value of a bind variable available to SQL*Plus commands like TTITLE or SPOOL. For example, you might want to call a PL/SQL function that returns a string and use the value for a SQL*Plus spool file name. The SPOOL command does not understand bind variable syntax so the bind variable value needs to be assigned to a substitution variable first.

This is done using COLUMN NEW_VALUE and SELECT commands. For example, declare a bind variable in SQL*Plus and instantiate it in a PL/SQL block. Its value can be returned from a PL/SQL function, or like here, set by a direct assignment:

Pass the bind variable's value to a new substitution variable "nv" by using a query:

Now you can use the substitution variable in a SPOOL command:

The SPOOL command executes as if you had typed

4 Using System Variables

Most system variables are the SET command options used to control the behavior of the SQL*Plus system. For example, to set the output line size from SQL*Plus:

The current status of each system variable can be displayed with the SHOW command.

System variables are sometimes known as SET variables .

Some system variables contain values that cannot be set. For example, RELEASE (a string representation of the SQL*Plus version) can only be shown.

See SET and SHOW in the SQL*Plus User's Guide and Reference .

4.1 System Variables Influencing Substitution Variables

Several system variables influence substitution variables.

Use SET CONCAT to define the character that separates the name of a substitution variable from alphanumeric characters that immediately follow the variable name. By default it is a single period (.).

For example, if "mycity" is defined as "Melbourne" then the command:

See SET CONCAT in the SQL*Plus User's Guide and Reference .

Use SET DEFINE OFF to stop SQL*Plus performing any variable substitution. This makes SQL*Plus treat all ampersands (&) as literal characters and prevents SQL*Plus prompting you for values:

The default substitution variable prefix is an ampersand (&). The SET DEFINE command can be used to change the variable-name prefix character. SET DEFINE ON turns variable substitution back on and resets the prefix character to "&"

Sometimes in SQL*Plus literature you may see references to the SET SCAN command. This is an obsolete alternative for SET DEFINE. To ensure maximum portability of scripts use SET DEFINE.

See SET DEFINE in the SQL*Plus User's Guide and Reference .

Use SET ESCAPE to prevent isolated occurrences of "&" from being treated as the substitution variable prefix:

Any "&" without the escape character is treated as a variable prefix.

See SET ESCAPE in the SQL*Plus User's Guide and Reference .

SET NULL sets the text that SQL*Plus displays when a NULL data value is printed.

A substitution variable may take the value of the SET NULL text if a COLUMN NEW_VALUE (or COLUMN OLD_VALUE) command associated the variable with a selected column and the current row contains a NULL value. The type of the substitution variable temporarily changes to CHAR while it contains NULL.

See SET NULL in the SQL*Plus User's Guide and Reference .

SET NUMFORMAT and SET NUMWIDTH interact. Use SET NUMFORMAT to change the default display format of a numeric variable. Use SET NUMFORMAT "" to remove the format. When there is no format, the default number formatting uses the SET NUMWIDTH option:

See SET NUMFORMAT in the SQL*Plus User's Guide and Reference .

SQL*Plus uses the value of SET NUMWIDTH only if there is no value for SET NUMFORMAT. Use SET NUMWIDTH to change the display width of a numeric variable:

Note the value is right justified within the field width and the number of leading spaces changes in each example.

SQL*Plus displays hash signs (#) if the format or field width for a numeric substitution variable is too small for the value.

See SET NUMWIDTH in the SQL*Plus User's Guide and Reference .

In SQL*Plus 10 g , substitution variables in the prompt are dynamically substituted each time the prompt is printed. Like variables used in TTITLE , they should not be prefixed with '&' else they are substituted only once when the SET SQLPROMPT command is executed.

This example shows the HR user setting the prompt and re-connecting to the OE schema. The predefined substitution variables _USER and _CONNECT_IDENTIFIER are used in the prompt to give the current username and database:

Each time the prompt is printed, SQL*Plus checks each word to see if it is a defined substitution variable. If it is, it will have its value printed. Otherwise it is displayed verbatim. Text in nested quotes will never be substituted. For performance reasons, the word SQL in the default prompt "SQL> " is never treated as a substitution variable.

Variables in the prompt are dynamically substituted only when SET SQLPLUSCOMPATIBILITY is 10.1 or greater. Otherwise, and for SQL*Plus versions 9.2 and earlier, "&"-prefixed variables can be substituted once when the SET SQLPROMPT command is executed.

Use SET VERIFY to control whether SQL*Plus echoes the old and new statement text when it substitutes a variable's value. SET VERIFY only has an effect on substitution variables used in SQL and PL/SQL statements:

Variables used in SQL*Plus commands (like SET and TTITLE) are not verified.

See SET VERIFY in the SQL*Plus User's Guide and Reference .

There is a special syntax to reference system variables in TTITLE, BTITLE, REPHEADER, REPFOOTER, and EXIT commands. The name of each special variable is the same as the SHOW option prefixed with "SQL.".

The special variables that can be referenced include:

  • SQL.PNO - page number
  • SQL.LNO - line number
  • SQL.USER - current username
  • SQL.RELEASE - SQL*Plus version
  • SQL.SQLCODE - last Oracle "ORA" error number

For example:

System variables of numeric type e.g. SQL.SQLCODE are formatted using the same rules as numeric substitution variables.

The variables cannot be prefixed with an "&" (see 5.7.1 Using "&" Prefixes With Title Variables ).

These variables are not substitution variables. The DEFINE command does not show them. They cannot be referenced in general commands. The system variables are not affected if you create substitution variables with the same name. For example, SQL.USER is not affected if you create a substitution variable called USER. The system variable SQL.RELEASE is not affected if the predefined substitution variable _O_RELEASE is changed.

5 SQL*Plus Substitution Variable Commands

Substitution variables can be used to replace options and values in almost all SQL*Plus commands. Several of the commands have special significance for substitution variables. These are discussed below.

The ACCEPT command always prompts for a variable's value, creating a new variable or replacing an existing one. ACCEPT has advantages over a double ampersand (&&) variable reference that causes a prompt. ACCEPT allows the prompting text to be customized and allows a default value to be specified. ACCEPT does type and format checking.

In this example, if you enter alphabetic characters then an error is shown and you are re-prompted. If you press Enter without typing anything then the variable takes the value "10".

The ACCEPT command understands numbers, strings and dates. If a FORMAT clause is used, SQL*Plus validates the input against the given format. If the input is not valid, you are re-prompted for a value. For a list of format models, see Format Models in the Oracle Database SQL Language Reference .

If a FORMAT specifier such as "A10" is used for a CHAR variable, the entered value may be any length up to and including 10 characters.

If a FORMAT specifier is used for a NUMBER type, the allowed inputs depend on the specifier used. For example, a specifier of "9EEEE" (for exponential notation) allows "3e2" but not "300" or "12e2". A format specifier of "999.99" allows both "123.45" and "67" to be entered.

DATE variables are validated against an explicitly supplied FORMAT or against the default session date format (like "DD-MON-YYYY").

After successful validation against the format model, variables are stored in the appropriate variable type. See 6.2 Substitution Variable Types . In particular, substitution variables created with an ACCEPT ... DATE command are stored with type CHAR.

The ACCEPT ... HIDE option can be used to prevent the value you enter from being displayed on the screen. This can be useful for scripts that need to prompt for passwords. Note some operating systems cannot redirect batch program script output into an ACCEPT ... HIDE command.

See ACCEPT in the SQL*Plus User's Guide and Reference for the full ACCEPT command syntax.

The COLUMN NEW_VALUE and COLUMN OLD_VALUE commands can be used to associate a substitution variable with a SELECT column's data.

When column "DEPARTMENT_ID" selected, a substitution variable "dnv" is created to hold each row of the column in turn. The variable remains defined after the query completes:

COLUMN OLD_VALUE and NEW_VALUE substitution variables can be used for basic numeric and text column types. They are used for any query executed that has a column of the same name as the COLUMN command. Their current value can be displayed in report headings and titles during query execution.

COLUMN NEW_VALUE variables should be used in TTITLE and REPHEADER titles at start of a page. They hold data from the new row about to be printed on the page. After the query finishes the variable has value of the last row.

COLUMN OLD_VALUE variables are used in BTITLE and REPFOOTER titles at the end of a page. They contain data from the old row most recently printed on the page. See 5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands for discussion and examples.

Variables change type as required. If another query with the same column name is run, the variable may take on a new type. Also if a number column contains null values, a substitution variable on the column changes from type NUMBER to CHAR for that row. This lets it hold the current string for the SET NULL option. The variable changes back to NUMBER when the next numeric value is fetched.

If no rows are selected by a query, and the substitution variable does not already exist, then a zero length CHAR variable is created. If the substitution variable exists then its value and type are not changed.

Similar to the DATE option for the ACCEPT command, a variable on a DATE column is stored as type CHAR.

COLUMN NEW_VALUE and OLD_VALUE can be used to transfer a value from a bind variable to a substitution variable. See 3.2 Assigning Bind Variables to Substitution Variables .

The COLUMN option NOPRINT can be used to suppress query results and stop them appearing in a final report.

See COLUMN in the SQL*Plus User's Guide and Reference for the command syntax.

Use the DEFINE command to explicitly create substitution variables:

The DEFINE command can also be used to display the value of a known variable. It shows the variable name, value and type:

Using DEFINE with no arguments lists all defined substitution variables. Any variable that DEFINE lists is said to be defined .

A variable may be redefined by repeating the DEFINE command with a different value.

The DEFINE command only ever creates variables with type CHAR.

See DEFINE in the SQL*Plus User's Guide and Reference for the command syntax.

The EDIT command starts an external editor such as Notepad or Vi. On most operating systems SQL*Plus has a predefined substitution variable called _EDITOR set to the default editor's executable:

EDIT can edit a named file. It can also edit the current SQL buffer (which holds the most recently executed SQL statement). When the external editor is closed the changed statement is loaded back into the SQL buffer. EDIT writes the SQL buffer to a temporary file called afiedt.buf . The temporary file name can be changed with the SET EDITFILE command.

You can redefine the value of the _EDITOR substitution variable to any editor.

On Windows _EDITOR can be set to "write.exe" to invoke WordPad. However, if the SQL buffer is being edited, the buffer is not automatically updated with the modified script. This is because SQL*Plus cannot tell when the WordPad editor has been closed.

A recommended way to create SQL*Plus scripts is to explicitly specify a file name for EDIT and then use the START or "@" commands to run this file.

See EDIT and SET EDITFILE , both in the SQL*Plus User's Guide and Reference .

On many operating systems the EXIT command can pass the value of a numeric bind variable or substitution variable to the operating system environment. On UNIX, the return status from SQL*Plus can be displayed with the command "echo $?" in the Bourne, Korn and Bash shells, or with "echo $status" in the C shell.

To return a substitution variable, it is recommended not to use an ampersand prefix before its name. If you use "&" or "&&", the command preprocessor does the substitution using default number formatting rules (see 6.3 Substitution Variable Formats ) before the EXIT command is finally parsed and executed. This is normal pre-processing of a numeric substitution variable in a command. Since the Oracle format specifier may include decimal and group separators which are not digits, or the number may overflow the format and be substituted as hash signs (#), there may be problems doing the final conversion from the resulting formatted string to the operating system return status. For example, if the formatting rules return exponential format and the number is formatted as "4E+05", then only the value "4" is returned to the operating system by the EXIT command.

When "&" does not prefix the substitution variable name, e.g. "EXIT myv", SQL*Plus internally uses the more practical format specifier "9999999990" to convert from the internal number format to the string used as the EXIT command parameter.

Note some operating systems limit the number range that can be returned from a program. On such systems the returned value may overflow and contain an unexpected number. This commonly limits the use of the system variable SQL.SQLCODE which contains the last Oracle error number. Typically this number is larger than an operating system supports as an exit return status.

If a non numeric variable is referenced in an EXIT statement the EXIT command exits but reports an error. The operating system return value is the same as for EXIT FAILURE.

Substitution variables are not saved when SQL*Plus exits. Only the predefined substitution variables and any variables set in the site and user profiles (e.g. glogin.sql and login.sql ) are defined when you next start SQL*Plus.

See EXIT in the SQL*Plus User's Guide and Reference .

The HOST command runs a specified operating system command or opens a command window. On some operating systems a character like "!" or "$" is a synonym for HOST.

After a HOST command finishes then the substitution variable _RC is defined. Its value is port specific and may contain a text message. On UNIX it is defined as "0" if the command is successful, or "1" if not. It may also be the operating exit status returned from the host program. On other platforms the value of _RC is not well defined and its value should not be relied on.

See HOST in the SQL*Plus User's Guide and Reference .

5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands

Variables are used in report titles to make each page relate to the data on that page, for example to give the product item that the report page describes. Any substitution variable can be used in a title command. However, the COLUMN NEW_VALUE or OLD_VALUE commands are often used to associate variables with column values in a report query.

Use NEW_VALUE variables in TTITLE and REPHEADER commands. Use OLD_VALUE variables in BTITLE and REPFOOTER commands. For example, the script:

gives the output:

See TTITLE , BTITLE , REPHEADER , and REPFOOTER in the SQL*Plus User's Guide and Reference .

The title commands (TTITLE, BTITLE, REPHEADER and REPFOOTER) substitute variables differently to most other commands. (The exceptions are the EXIT and SET SQLPROMPT commands, which are similar to the title commands). In general you do not need, and will not want, to put an "&" prefix before a variable name in a title command. For example, if your TTITLE command is:

you should possibly change it to:

The guidelines for variables in titles are:

If you want the same value for a variable to be printed on every page then use an "&" prefix and put the variable inside a quoted string:

If you want each title to have data from the query that is unique to each report page then do not use an "&" prefix for the variable and do not put the variable inside quotes.

SQL*Plus substitution variables are expanded before each command is executed. After this happens in a title command, the resulting string is stored as the title text. What makes variables in titles special is that they need to be re-substituted for each page of query results. This is so the current COLUMN NEW_VALUE and OLD_VALUE substitution variable values are displayed on each page, customizing each title for the results displayed on its page. If "&" is used inadvertently or incorrectly to prefix title variables, it is possible to get double substitution. This is dependent on the variable's value and is easily overlooked when you write scripts.

Any non-quoted, non-keyword in a title is checked when the page is printed to see if it is a variable. If it is, its value is printed. If not, then the word is printed verbatim. This means that if you use "&myvar" in a title command, and the text substituted for it can itself be interpreted as another variable name then you get double variable substitution. For example, the script:

causes the text "left scottsvar" to be stored as the title. When the title is printed on each page of the query this string is re-evaluated. The word "scottsvar" in the title is itself treated as a variable reference and substituted. The query output is:

Using "&" in titles most commonly causes a problem with the numeric variable names of the SQL*Plus script parameters . If the value of an arbitrary "&"-prefixed title variable is the same as a script parameter variable name, then double substitution will occur.

To display an "&" in a title, prefix it with the SET ESCAPE character. The ampersand (&) is stored as the title text and is not substituted when page titles are printed.

Unquoted whitespace in titles is removed. Use whitespace instead of the SET CONCAT character to separate variables from text that should appear immediately adjacent. Use whitespace inside quotes to display a space. For example, the script:

gives a title of:

Use UNDEFINE to remove a defined substitution variable:

Any variable not listed by DEFINE is said to be undefined .

Undefining unused substitution variables may help improve SQL*Plus performance because SQL*Plus can look up variables faster. This is especially true when variables are used in the SQLPROMPT .

See UNDEFINE in the SQL*Plus User's Guide and Reference for the command syntax.

Substitution variables used for return statuses in WHENEVER OSERROR EXIT or WHENEVER SQLERROR EXIT commands follow the same general guidelines as variables in EXIT commands . Specifically, no ampersand (&) prefix is required, for example:

Be careful of using an ampersand (&) prefix for substitution variables in WHENEVER ... EXIT commands. Using an ampersand causes the current value of the variable at the time the WHENEVER command is run to be used, not the value that is in effect when the program later exits. For example, in the script:

the operating system return status is 5. This is because the WHENEVER statement is pre-processed and executed as if you typed:

However, if you remove the ampersand:

the return status is "10" which is the value of "myv" at the time of exit.

See WHENEVER OSERROR and WHENEVER SQLERROR in the SQL*Plus User's Guide and Reference .

6 Substitution Variable Namespace, Types, Formats and Limits

In a SQL*Plus session there is just one global name space for substitution variables. If you reconnect using CONNECT, or run subscripts using "@", all variables ever defined are available for use and may be overridden or undefined.

When a child script finishes, all substitution variables it defined or changed are visible to the calling script. This is particularly noticeable when a subscript executed with "@" or START is given script parameters . The parameters "&1" etc. get redefined and the parent script sees the new values.

To minimize problems, and for general readability, use symbolic variable names for command parameters. All other references should use the new variable name instead of "&1". For example:

The call to myscript.sql changes the value of "&1" to "King". By saving the original value of "&1" in "myuser" and using "&myuser" instead of "&1" in the SELECT, the query executes correctly.

The substitution variable types stored by SQL*Plus are:

  • BINARY_FLOAT
  • BINARY_DOUBLE

The CHAR type is a generic text format similar to the database table VARCHAR2 column type. All variables created:

with DEFINE

  • from prompts for "&" variables
  • from script parameters

are of type CHAR. This ensures that values entered are substituted verbatim with no conversion loss.

Variables created by COLUMN NEW_VALUE or OLD_VALUE for the columns in Oracle number format will have the type NUMBER. These substitution variables are stored in Oracle's internal number representation as they are in the database. This allows display formats to be altered without any internal value loss. Substitution variables of BINARY_FLOAT and BINARY_DOUBLE types are similarly created for Oracle BINARY_FLOAT and BINARY_DOUBLE columns. These variables are stored in native machine representation. The CHAR type is used for NEW_VALUE and OLD_VALUE variables with all other column types.

There is no explicit DATE type. The DATE keyword in the ACCEPT command is used solely to allow correct format validation against a date format. Substitution variables created by ACCEPT ... DATE, or by COLUMN NEW_VALUE on a date column, are stored as type CHAR. For example:

If a variable already exists and is redefined, its old type is discarded and the new type used.

The type of a substitution variable is generally transparent. Substitution variables are weakly typed. For example, a COLUMN NEW_VALUE variable takes on the particular type of the named column in each new query. It may also change type during a query. For example, the type of a substitution variable used on a NUMBER column changes from NUMBER to CHAR when a NULL value is fetched. It changes back to NUMBER when the next numeric value is fetched.

No type comparison semantics are defined for any type since there is no direct comparison of variables. All variables are textually substituted before any SQL or PL/SQL statement that could do a comparison is executed.

When a variable is substituted, or its value is shown by a DEFINE command, it is formatted as text before the command referencing the variable is finally executed.

CHAR variables are substituted verbatim.

NUMBER variables are formatted according to SET NUMWIDTH (by default) or SET NUMFORMAT (if you have explicitly set one):

The display format of a number can be changed even after the variable is created. To show this, first create a NUMBER variable. You cannot use DEFINE to do this because it makes the type of all new variables CHAR. Instead use a COLUMN NEW_VALUE command which inherits the NUMBER type from a NUMBER column:

Changing the format affects the display of the number but not the stored value:

For a list of format models, see Format Models in the Oracle Database SQL Language Reference .

The maximum number of substitution variables allowed is 2048. SQL*Plus gives an error an attempt is made to create more. The limit includes the predefined variables, however these can be undefined if necessary. Leaving a large number of unnecessarily defined variables can reduce the performance of SQL*Plus because variable lookups are slower.

A character substitution variable can be up to 240 bytes long.

A numeric substitution variable holds the full range of Oracle numbers. See NUMBER Datatype in the Oracle Database SQL Language Reference .

When a command line undergoes variable substitution, the resulting line length can be no more than:

  • 3000 bytes if it is a line of SQL (like SELECT or INSERT) or PL/SQL text (like BEGIN or CREATE PROCEDURE)
  • 2499 bytes if it a line of a SQL*Plus command (like TTITLE or COLUMN)

Otherwise an error is displayed.

These limits may be lower in old versions of SQL*Plus.

7 i SQL*Plus and Substitution Variables

i SQL*Plus was a web based version of SQL*Plus that was was obsoleted by Oracle APEX and is no longer used.

i SQL*Plus Release 10.1 interactively prompts for substitution values as the statement referencing them is executed. Each undefined variable is prompted for on its own HTML page. This is similar to command-line SQL*Plus.

Sometimes it is convenient to prompt for more than one value at a time. A separate HTML form can be used to prompt for all input. The values can be passed to i SQL*Plus as script parameters and referenced as substitution variables. See 7.2 i SQL*Plus Parameters .

7.1 i SQL*Plus 9 i and SQL*Plus Substitution Variable Compatibility

The prompting model is different in i SQL*Plus 9i. When a script is run in i SQL*Plus Release 9.0 or 9.2, a single HTML page for undefined substitution variables is displayed. After you enter a value for each variable, the script executes and generates its results.

Some differences may be noticed between command-line SQL*Plus and i SQL*Plus Releases 9.0 or 9.2 when your script does one of the following:

  • contains DEFINE
  • contains UNDEFINE
  • contains SET DEFINE
  • uses "&" and "&&" prefixes for the same variable

These differences include being unnecessarily prompted in i SQL*Plus for values, and an empty string being used instead of the expected value. These problems do not occur in i SQL*Plus 10.1.

i SQL*Plus Releases 9.0 and 9.2 cannot prompt for input in the middle of an executing script. This is due to the way the i SQL*Plus server interacts with the SQL*Plus engine. (The engine is the same statement-executing code used by command-line SQL*Plus. The i SQL*Plus server generates the i SQL*Plus HTML interface and handles HTTP requests). If a script explicitly changes variable definitions, for example by undefining a variable, then i SQL*Plus cannot subsequently prompt for a new value. In this example an empty string is used instead.

In i SQL*Plus, parsing for "&" is performed twice, once by the i SQL*Plus server and once by the SQL*Plus engine. The i SQL*Plus server scans each script for "&" and "&&" references and creates a page with entry fields for undefined variables. When you have given values for the variables, they are transparently sent to the engine as DEFINE commands at the start of your script.

For example, if you start i SQL*Plus Release 9.x and enter:

the i SQL*Plus server:

  • Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. By default these are "&", OFF and "." respectively. (The values of SET ESCAPE and SET CONCAT are not relevant for this example.)
  • Parses the script as if it were a single stream of arbitrary words. Since SET DEFINE is not OFF, all "&" variables that were undefined prior to the script being started are recorded. In this example these are "sortcol" and "mytable". No SQL*Plus statements are recognized or processed so the "define mytable" is ignored and the i SQL*Plus server records that an unknown variable "mytable" was referenced in the line "from &mytable".
  • Creates a page prompting for values of "sortcol" and "mytable". Each variable name occurs only once on the page.
  • After you enter a value for each variable on the page and click "OK", i SQL*Plus prepends explicit DEFINE commands for the variables and their values to your script. Because only single "&" prefixes were used in this example, i SQL*Plus also appends matching UNDEFINE commands at the end of the script. All extra commands are removed when the script finishes and do not display in the i SQL*Plus Work screen.

The modified script is then sent to the SQL*Plus engine for processing. The engine:

  • Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. These are still "&", OFF and "." respectively. Again, only the value of SET DEFINE is relevant to this example.

When the script in this example is run then the two variables get defined by the new, transparently added, DEFINE commands. The script's original define of "mytable" runs next and its value "employees" is the one finally used by the rest of the script.

Since SET DEFINE is not OFF, when "&sortcol" and "&mytable" are seen by the engine's preprocessor, the defined values are used.

At the conclusion of the script, the two variables are undefined in the session by the explicit UNDEFINE commands of the transparently modified script.

The undesired behavior in this example is being prompted for a value for "mytable" despite the script explicitly defining it. However, the prompted value is not used and the correct results are displayed.

The i SQL*Plus server creates the page to enter variable values unless SET DEFINE is OFF before the script is submitted to the SQL*Plus engine for execution. To stop i SQL*Plus prompting for "&" values, make sure DEFINE is OFF. In i SQL*Plus Release 9.2 go to the System Variable page and change the DEFINE radio button to OFF. Then execute your script. In i SQL*Plus Release 9.0 you need to execute an explicit second script containing "SET DEFINE OFF" prior to entering and executing the main script.

If you want to use "&" prefixed variables in a script but know all values are generated in the script (using DEFINE, COLUMN NEW_VALUE or OLD_VALUE) then make sure SET DEFINE is OFF prior to executing the script (to stop the variable entry page being displayed). Also make the first line of the script "SET DEFINE ON" (to allow the SQL*Plus engine to do variable substitution using the script-created variables) and make the last line "SET DEFINE OFF" (to prevent subsequent re-executions of the script from unnecessarily creating the variable entry page).

If you want the main script to prompt for some values, but not others, explicitly define these latter variables before running your main script. The variables you explicitly define do not generate prompts when the main script is later executed. These variables can be defined by executing an initial script containing DEFINE commands. This initial script does not have to give valid values to the variables if the main script is later going to provide them. When the main script is run, i SQL*Plus sees that the variables have already been defined in the current session and does not include them on the variable entry page.

If variables you do want to be prompted for in a script are prefixed with "&&" then make sure the script undefines them at its end. Otherwise the variables become defined in the session. When the script is re-run i SQL*Plus sees the variables have a value and does not include them on the variable entry page. Explicitly undefining the variables allows the script to re-prompt for values each time it is run.

Variables can be passed to i SQL*Plus dynamic reports using the URL syntax. These become defined as if they are named parameters. For example, the i SQL*Plus 10.1 URL:

would define "myv" as "emp" and then run mys.sql . The script can reference "&myv". In SQL*Plus 9.2, the equivalent URL is:

For compatibility with command-line SQL*Plus scripts, you can use numeric names for parameters, e.g. "1=employees"

The "&" in the URL is the character for separating URL variables. It is only coincidentally the same as the default SQL*Plus substitution variable prefix and cannot be changed using SET DEFINE.

SQL*Plus substitution variables can be used to customize reports and can be used instead of hard-coded text. Substitution variables can interact with bind and system variables. Substitution variables that have values stored are said to be defined .

Substitution variables can be defined explicitly:

with ACCEPT

by passing parameters to SQL*Plus or i SQL*Plus scripts

Substitution variables can be defined implicitly:

with a COLUMN NEW_VALUE or COLUMN OLD_VALUE command

by using a double ampersand (&&) prefix on an undefined variable

Substitution variables references have an "&" or "&&" prefix.

If a variable is referenced but is not defined, SQL*Plus stops and prompts for a value.

Substitution variable references are pre-processed and substituted before the command is otherwise parsed and executed.

Substitution variables have a current type, such as CHAR. Substitution variables are weakly typed and change type as necessary.

Values are substituted as text. So even if the type of a variable is NUMBER, its value is formatted as a text string, substituted, and then the command executed.

In titles, EXIT and SET SQLPROMPT, substitution variables do not have to be prefixed with "&" or "&&".

The predefined substitution variables are named with a leading underscore (_). They can be undefined or redefined.

9 Substitution Variable Examples

A substitution variable can be set in several ways. The common ways are given below.

The DEFINE command sets an explicit value:

This creates a character variable "myv" set to the value "King".

The ACCEPT command:

prompts you for a value and creates a character variable "myv" set to the text you enter.

Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.

Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

This creates a substitution variable "mynv" set to the value in the "last_name" column.

Once a substitution variable has a value, it can be referenced by prefixing the variable name with an ampersand (&).

If the variable "myv" is already defined it can be used like:

The DEFINE command with no parameters shows all defined substitution variables, their values, and their types. For example:

might give:

There are two ways to make an "&" be treated as text and not cause a prompt. The first turns all variable substitution off:

The INSERT statement stores the text "thick & thin" in the table.

The second method is useful for ignoring individual occurrences of "&" while allowing others to prefix substitution variables:

The first INSERT statement in this method stores the text "thick & thin" in the table. The second INSERT causes SQL*Plus to prompt you for a value, which is then stored.

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

In this example the first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "20030120" from the first query then the spool file name would be "20030120report.txt".

You can use this technique to build up any string for the file name.

The period is the default value of SET CONCAT. If you have assigned another character then use it instead of a period to end the substitution variable name.

If you wish to append alphanumeric characters immediately after a substitution variable, use the value of SET CONCAT to separate the variable name from the following text. The default value of SET CONCAT is a single period (.). For example:

creates a file with the name "MelbourneAustralia.txt"

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

This example makes every page of a report have exactly the same heading. It can be used for TTITLE, BTITLE, REPHEADER or REPFOOTER commands.

In a TTITLE command prefix the variable name "dept" with "&" and place it inside a quoted string:

This example uses a different title on every page of a report. Each title contains a value derived from query results shown on that particular page.

In a TTITLE command do not put an "&" before the variable name "dv". Put the variable name outside a quoted string:

In a BTITLE or REPFOOTER command use a COLUMN OLD_VALUE variable instead of a COLUMN NEW_VALUE variable.

If you want to use the value of a bind variable in a SQL*Plus command it must first be copied to a substitution variable.

SQL*Plus commands like SPOOL, SET and TTITLE are executed in the SQL*Plus program and are not passed to the database for execution. Because of this they do not understand bind variables.

To use a bind variable's value as the name of a spool file:

You can pass parameters on the command line to a SQL*Plus script:

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

Here the "SET VERIFY OFF" command stops SQL*Plus from echoing the SQL statement before and after the variables are substituted. The query returns the employee identifier for the employee "De Haan" from the "employees" table.

Parameters can also be passed to scripts called within SQL*Plus:

You can pass an operating system variable to a SQL*Plus script as a command line parameter. For example, on UNIX:

or in a Windows command window:

The script myscript.sql could reference the substitution variable "&1" to see the passed name.

If you create a procedure "myproc":

and myscript.sql contains:

then calling:

executes the script as if it is:

This method does not work if the parameter "p1" to "myproc" is "IN OUT". The variable reference is pre-processed and is effectively a hard coded value which cannot contain an OUT value. To get around this you can assign the substitution variable to a bind variable. The script myscript.sql becomes:

The goal is to create a script which accepts an optional parameter. If a parameter is passed from the command line then its value should be used. However, if there is no parameter, then SQL*Plus should ask for a value with a customized prompt.

Perhaps the closest solution is with a PROMPT/DEFINE sequence like this. If myscript.sql is:

you can call the script with or without a parameter. If you enter "12" at the prompt your screen looks like:

or if you call it with a parameter "8":

Note when you pass a parameter the PROMPT text is still displayed, but you do not enter a value. The PROMPT command is the SQL*Plus "echo" or "print" statement. (It does not read input).

The only occurrence of "&1" should be where "mypar" is defined. All other references to the parameter should use "&mypar" or "&&mypar".

Variables can be passed as URL parameters to an i SQL*Plus report. For example with i SQL*Plus 10.1:

or in i SQL*Plus 9i:

These define the substitution variable "&myv" as "emp" and the substitution variable "v2" as "dept" before running the script mys.sql . The script can use "&myv" and "&v2" anywhere substitution variables are allowed.

Note that i SQL*Plus prompts for a username and password before defining the variables and running the script.

A customized HTML form can be used to enter and validate variables to be passed to an i SQL*Plus Release 9.2 dynamic report.

Create and save a SQL*Plus script employee_name.sql on your Oracle HTTP Server. Check it can be loaded into a web browser to verify i SQL*Plus is able to access the file over HTTP. The script is:

Create an HTML file myreport.html on your Oracle HTTP Server. The file is:

Replace "http://machine/" with the appropriate host name, domain name and port number of your Oracle HTTP Server, for example, "http://machine.oracle.com:7777/".

The name of the INPUT TYPE should be the same as the substitution variable name in the SQL*Plus script. For example, the input field:

maps to the substitution variable "&last_name" in employee_name.sql .

Load myreport.html in your web browser. Enter a name or partial name in the text field, for example, "Fay". Click the Run Report button. i SQL*Plus will prompt for database connection details and then execute the script. The Employee Details report is displayed in your web browser.

You could add Javascript to the HTML form to do any desired browser-side validation of the values entered.

To use the value of a substitution variable called "myv" as the SQL*Plus return status, use:

No ampersand (&) prefix is required before the substitution variable name.

A numeric bind variable requires a colon (:) prefix:

In SQL*Plus 10 g add this to your glogin.sql or login.sql:

For customized prompts that query the database make sure to explicitly DEFINE any referenced substitution variables. Glogin.sql and login.sql can get run when there is no database connection. Defining variables prevents the user being prompted for values when the query fails and the variables do not get defined by it:

SQL*Plus 9.2 and earlier don't re-execute glogin.sql and login.sql after CONNECT commands. Also variables in the SQLPROMPT are not dynamically substituted. It is possible to use the query script given above, but beware that the prompt will only be valid for the original connection.

Christopher Jones

Senior principal product manager.

Christopher is a Senior Principal Product Manager working with the Oracle Database Data Access team primarily on APIs for scripting languages including Node.js, Python, PHP and the newly launched ODPI-C library for Oracle Database driver writers.  He has responsibilities for Oracle Call Interface (OCI) and related Oracle Database APIs; for Oracle Database clients such as Oracle Instant Client; and for general database adoption by developers, including championing Oracle Database XE.  He remains a lead maintainer of PHP's OCI8 extension for Oracle Database.  He co-released the popular book "The Underground PHP and Oracle Manual" and is the author of a number of technical articles.  Christopher has presented at big and small conferences around the world including the O'Reilly Open Source Convention and Oracle OpenWorld.  

Twitter: @ghrd

Email:  [email protected]

Blog: http://blogs.oracle.com/opal/

Previous Post

Oracle 11gR2 XE Beta is now available

Configuring php gd on ubuntu 11.04 "natty".

  • Analyst Reports
  • Cloud Economics
  • Corporate Responsibility
  • Diversity and Inclusion
  • Security Practices
  • What is Customer Service?
  • What is ERP?
  • What is Marketing Automation?
  • What is Procurement?
  • What is Talent Management?
  • What is VM?
  • Try Oracle Cloud Free Tier
  • Oracle Sustainability
  • Oracle COVID-19 Response
  • Oracle and SailGP
  • Oracle and Premier League
  • Oracle and Red Bull Racing Honda
  • US Sales 1.800.633.0738
  • How can we help?
  • Subscribe to Oracle Content
  • © 2024 Oracle
  • Privacy / Do Not Sell My Info
  • User's Guide and Reference
  • SQL*Plus Reference
  • SQL*Plus Command Reference
  • SET System Variable Summary

12.41 SET System Variable Summary

12.41.1 set appi[nfo]{on | off | text }.

Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.

This enables the performance and resource usage of each script to be monitored by your DBA. The registered name appears in the MODULE column of the V$SESSION and V$SQLAREA virtual tables. You can also read the registered name using the DBMS_APPLICATION_INFO.READ_MODULE procedure.

ON registers scripts invoked by the @, @@ or START commands. OFF disables registering of scripts. Instead, the current value of text is registered. text specifies the text to register when no script is being run or when APPINFO is OFF, which is the default. The default for text is "SQL*Plus". If you enter multiple words for text , you must enclose them in quotes. The maximum length for text is limited by the DBMS_APPLICATION_INFO package.

The registered name has the format nn @ xfilename where: nn is the depth level of script; x is '<' when the script name is truncated, otherwise, it is blank; and filename is the script name, possibly truncated to the length allowed by the DBMS_APPLICATION_INFO package interface.

To display the value of APPINFO, as it is SET OFF by default, enter

To change the default text, enter

To make sure that registration has taken place, enter

To change APPINFO back to its default setting, enter

12.41.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. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency.

12.41.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 .

ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses automatic committing so that you must commit changes manually (for example, with the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle Database executes n successful SQL INSERT, UPDATE, or DELETE commands, or PL/SQL blocks. n cannot be less than zero or greater than 2,000,000,000. The statement counter is reset to zero after successful completion of n INSERT, UPDATE or DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT command.

SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.

For this feature, a PL/SQL block is considered one transaction, regardless of the actual number of SQL commands contained within it.

12.41.4 SET AUTOP[RINT] {ON | OFF }

Sets the automatic printing of bind variables.

ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

See PRINT for more information about displaying bind variables.

12.41.5 SET AUTORECOVERY [ON | OFF ]

ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.

No interaction is needed, provided the necessary files are in the expected locations with the expected names. The filenames used are derived from the values of the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT.

OFF, the default option, requires that you enter the filenames manually or accept the suggested default filename given. See RECOVER for more information about database recovery.

To set the recovery mode to AUTOMATIC, enter

12.41.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.

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. See EXPLAIN PLAN for more information about EXPLAIN PLAN.

Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

The TRACEONLY option may be useful to suppress the query data of large queries. If STATISTICS is specified, SQL*Plus still fetches the query data from the server, however, the data is not displayed.

The AUTOTRACE report is printed after the statement has successfully completed.

When SQL*Plus produces a STATISTICS report, a second connection to the database is automatically created. This connection is closed when the STATISTICS option is set to OFF, or you log out of SQL*Plus.

The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server. The additional information and tabular output of AUTOTRACE PLAN is supported when connecting to Oracle Database 10 g (Release 10.1) or later. When you connect to an earlier database, the older form or AUTOTRACE reporting is used.

AUTOTRACE is not available when FIPS flagging is enabled.

See About Tracing Statements for more information on AUTOTRACE.

12.41.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.

12.41.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 (;).

To specify a title with TTITLE and format a column with COLUMN, both on the same line, enter

12.41.9 SET COLINVI[SIBLE] [ON | OFF ]

ON sets the DESCRIBE command to enable the display of information about an invisible column.

SET COLINVISIBLE has no effect on query statments that contain invisible columns. To retrieve data in an invisible column, explicitly specify the column in your query.

To view information about an invisible column with the DESCRIBE command.

Create a table with an invisible column.

Use the DESCRIBE command to list the table columns.

Note that with the default SET COLINVISIBLE OFF, the invisible column does not appear in the result. Change the default setting of SET COLINVISIBLE to ON.

Now use the DESCRIBE command again to list the table columns. The invisible column now appears in the output.

12.41.10 SET COLSEP { | text }

S ets 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.

In multi-line rows, the column separator does not print between columns that begin on different lines. The column separator does not appear on blank lines produced by BREAK ... SKIP n and does not overwrite the record separator. See SET RECSEP {WR[APPED] | EA[CH] | OFF} for more information.

To set the column separator to "|" enter

12.41.11 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.

12.41.12 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.41.13 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.

12.41.14 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.

See SET SCAN {ON|OFF} (obsolete) for more information on the SCAN variable.

12.41.15 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.

The valid range of the DEPTH clause is from 1 to 50. If you SET DESCRIBE DEPTH ALL, then the depth will be set to 50, which is the maximum level allowed. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. Use the SET LINESIZE command to control the width of the data displayed.

See DESCRIBE for more information about describing objects.

To create an object type ADDRESS, enter

To create the table EMPLOYEE that contains a nested object, EMPADDR, of type ADDRESS, enter

To describe the table EMPLOYEE to a depth of two levels, and to indent the output and display line numbers, enter:

12.41.16 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.

12.41.17 SET EDITF[ILE] file_name [. ext ]

Sets the default filename for the EDIT command. See EDIT for more information about the EDIT command. The default filename for the EDIT command is afiedt.buf which is the SQL buffer.

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.

12.41.18 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. Set EMBEDDED to ON when you want a report to begin printing immediately following the end of the previously run report.

12.41.19 SET ERRORL[OGGING] {ON | OFF } [TABLE [ schema. ] tablename ] [TRUNCATE] [IDENTIFIER identifier ]

Turns SQL*Plus error logging ON or OFF. Error logging records SQL, PL/SQL and SQL*Plus errors and associated parameters in an error log table. You can then query the log table to review errors resulting from a query. When error logging is ON, errors are recorded whether the query is run interactively or from a script. This is particularly useful for capturing errors generated from long running queries and avoids capturing all output using the SPOOL command, or having to remain present during the run.

By default, errors are written to a the table SPERRORLOG in your schema. If this table does not exist, it is created automatically. You can also use the TABLE schema.tablename option to specify other tables to use. When using a table other than SPERRORLOG, it must already exist, and you must have access to it. See Creating a User Defined Error Log Table .

If an internal error occurs, to avoid recursion errors caused by the errorlog calling itself, errorlogging is automatically set OFF.

Error logging is set OFF by default.

Writes ORA, PLS and SP2 errors to the default table, SPERRORLOG.

Disables error .

Specifies a user defined table to use instead of the default, SPERRORLOG. If you omit schema. the table is created in the current schema. The table you specify must exist, and you must have access permissions.

If the table specified does not exist, or you do not have access, an error message is displayed and the default table, SPERRORLOG, is used.

Clears all existing rows in the error log table and begins recording errors from the current session.

A user defined string to identify errors. You can use it to identify errors from a particular session or from a particular version of a query.

Creating a User Defined Error Log Table

You can create one or more error log tables to use other than the default. Before specifying a user defined error log table with the TABLE schema.tablename option, you must create it and ensure that you have permissions to access it. The error log table has the following column definitions:

Table 12-4 SQL*Plus Error Log Column Definitions

Using User Defined Error Log Tables

To use a user defined log table, you must have permission to access the table, and you must issue the SET ERRORLOGGING command with the TABLE schema.tablename option to identify the error log table and the schema if applicable.

Querying Error Log Tables

To view the records recorded in an error log table, you query the columns you want to view as for any table. The columns available are shown in Table 12-4 .

To use the default error log table to record query errors from a script, myfile.sql , which contains the following:

Enter the following:

which produces the following output:

To view the errror log written to the default error log table, SPERRORLOG, enter:

To use a user defined error log table to record query errors from a script, myfile.sql , which contains the following:

To use an error log table other than the default:

Create the error log table you want to use

Specify the table you want to use with the TABLE option of the SET ERRORLOGGING ON command.

The error log table must have the column definitions defined in Table 12-4 .

John wants to use an error log table named john_sperrorlog . John would run the following SQL statements to create the new error log table:

Removed Commit from previous example from user comment giridhar123 14Feb08

John then issues the following SET command to enable error logging using the newly created error log table

All error logging for John is now recorded to john_sperrorlog , and not to the default error log table, SPERRORLOG.

Access privileges for the error log table are handled in the same way as for any user table.

12.41.20 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 "\".

You can use the escape character before the substitution character (set through SET DEFINE) to indicate that SQL*Plus should treat the substitution character as an ordinary character rather than as a request for variable substitution.

If you define the escape character as an exclamation point (!), then

displays this prompt:

To set the escape character back to the default value of \ (backslash), enter

12.41.21 SET ESCCHAR

Specifies a character to be escaped and not interpreted when used in a file name for the SPOOL, START, @, RUN and EDIT commands. These special characters are translated to the following:

@ in a filename will be translated to Oracle SID

? is translated to Oracle Home in Unix

% is translated to Oracle Home in Windows

While it is not recommended that these characters are used in filenames, if you have legacy files that do use them, it might be useful to include a SET ESCCHAR command in your GLogin file to implement it across your site.

If not escaped, the characters @, ? and % have significance when interpreted and cause errors for the SPOOL, START, @, RUN and EDIT commands.

SET ESCCHAR is set OFF by default.

Starting from Oracle Database release 19c, version 19.3, file names with the $ character will no longer run on Windows.

12.41.22 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.

Table 12-5 shows the exit action associated with combinations of SET commands (AUTOCOMMIT & EXITCOMMIT) and the EXIT command.

Table 12-5 Exit Behavior: AUTOCOMMIT, EXITCOMMIT, EXIT

12.41.23 SET FEED[BACK] { 6 | n | ON | OFF | ONLY} [SQL_ID]

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. The feedback message is not displayed while the data is displayed.

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.

ONLY returns the number of rows selected by a query without displaying data.

SQL_ID returns the sql_id for the SQL or PL/SQL statements that are executed. The sql_id will be assigned to the predefined variable _SQL_ID. You can use this predefined variable to debug the SQL statement that was executed. The variable can be used like any other predefined variable, such as _USER and _DATE.

When the SQL_ID option is specified and feedback is ON, you see the sql id displayed along with the feedback message. When feedback is OFF, only the sql id is displayed.

To enable SET FEEDBACK ONLY, enter

To enable SET_FEEDBACK and SQL_ID enter

12.41.24 SET FLAGGER { OFF | ENTRY | INTERMED[IATE] | FULL}

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.

If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.

You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus.

When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.

12.41.25 SET FLU[SH] { ON | OFF}

Controls when output is sent to the user's display device. OFF enables the operating system to buffer output. ON disables buffering. FLUSH only affects display output, it does not affect spooled output.

Use OFF only when you run a script non-interactively (that is, when you do not need to see output and/or prompts until the script finishes running). The use of FLUSH OFF may improve performance by reducing the amount of program I/O.

12.41.26 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.

To suppress the display of column headings in a report, enter

If you then run a SQL SELECT command

the following output results:

To turn the display of column headings back on, enter

12.41.27 SET HEADS[EP] { | c | ON | OFF}

Defines the character used as a line break in column headings.

The heading separator character cannot be alphanumeric or white space. You can use the heading separator character in the COLUMN command and in the old forms of BTITLE and TTITLE to divide a column heading or title onto more than one line. ON or OFF turns heading separation on or off. When heading separation is OFF, SQL*Plus prints a heading separator character like any other character. ON changes the value of c back to the default "|".

12.41.28 SET HIST[ORY] {ON | OFF | n }

Enables or disables the history of commands. When enabled, SQL*Plus, SQL and PL/SQL statements are stored in the command history list. You can recall SQL*Plus, SQL and PL/SQL statements by using the HISTORY command.

To enable command history and store 200 entries in the list, enter

If you do not specify a value for n , the default is 100.

12.41.29 SET INSTANCE [ instance_path | LOCAL ]

Changes the default instance for your session to the specified instance path.

Using the SET INSTANCE command does not connect to a database. The default instance is used for commands when no instance is specified. Any commands preceding the first use of SET INSTANCE communicate with the default instance.

To reset the instance to the default value for your operating system, you can either enter SET INSTANCE with no instance_path or SET INSTANCE LOCAL.

Note, you can only change the instance when you are not currently connected to any instance. That is, you must first make sure that you have disconnected from the current instance, then set or change the instance, and reconnect to an instance in order for the new setting to be enabled.

This command may only be issued when Oracle Net is running. You can use any valid Oracle Net connect identifier as the specified instance path. See your operating system-specific Oracle Database documentation for a complete description of how your operating system specifies Oracle Net connect identifiers. The maximum length of the instance path is 64 characters.

To set the default instance to "PROD1" enter

To set the instance back to the default of local, enter

You must disconnect from any connected instances to change the instance.

12.41.30 SET LIN[ESIZE] { 80 | n | WINDOW}

Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

It also controls the position of centered and right-aligned text in TTITLE, BTITLE, REPHEADER and REPFOOTER. Changing the linesize setting can affect text wrapping in output from the DESCRIBE command. DESCRIBE output columns are typically allocated a proportion of the linesize. Decreasing or increasing the linesize may give unexpected text wrapping in your display. You can define LINESIZE as a value from 1 to a maximum that is system dependent.

WINDOW adjusts the linesize and pagesize for the formatted output according to the width and height of the screen. If the output is longer than the screen width, then the output is wrapped accordingly.

If the screen size is manually set by using the SET LINESIZE n command, the subsequent output will be displayed to fit the new linesize.

The SET LINESIZE WINDOW command is ignored in a script and the output will not be displayed according to the screen size.

To set the linesize for the output to 20, enter

To dynamically change the output display after manually resizing the screen, enter

12.41.31 SET LOBOF[FSET] { 1 | n }

Sets the starting position from which BLOB , BFILE, CLOB and NCLOB data is retrieved and displayed.

To set the starting position from which a CLOB column's data is retrieved to the 22nd position, enter

The CLOB data will wrap on your screen; SQL*Plus will not truncate until the 23rd character.

12.41.32 SET LOBPREFETCH { 0 | n }

Sets the amount of LOB data (in bytes) that SQL*Plus will prefetch from the database at one time.

To set the amount of prefetched LOB data to 8000 bytes, enter

If you do not specify a value for n , the default is 0. This means that LOB data prefetching is off.

To show the current setting for the amount of LOB data that SQL*Plus will prefetch from the database at one time, enter

12.41.33 SET LOGSOURCE [ pathname ]

Specifies the location from which archive logs are retrieved during recovery.

The default value is set by the LOG_ARCHIVE_DEST initialization parameter in the Oracle Database initialization file, init.ora. Using the SET LOGSOURCE command without a pathname restores the default location.

To set the default location of log files for recovery to the directory "/usr/oracle10/dbs/arch" enter

12.41.34 SET LONG { 80 | n }

Sets maximum width (in bytes) for displaying BLOB , BFILE, CLOB , LONG , NCLOB and XMLType values; and for copying LONG values.

Querying LONG columns requires enough local memory to store the amount of data specified by SET LONG, irrespective of the value of the SET LONGCHUNKSIZE command. This requirement does not apply when querying LOBs.

It is recommended that you do not create tables with LONG columns. LONG columns are supported only for backward compatibility. Use LOB columns (BLOB, BFILE, CLOB, NCLOB) instead. LOB columns have fewer restrictions than LONG columns and are still being enhanced.

The maximum value of n is 2,000,000,000 bytes. It is important to check that the memory required by your SET LONG command is available on your machine, for example:

assumes that available RAM (random access memory) on the machine exceeds 2 gigabytes.

To set the maximum number of bytes to fetch for displaying and copying LONG values, to 500, enter

The LONG data will wrap on your screen; SQL*Plus will not truncate until the 501st byte. The default for LONG is 80 bytes.

12.41.35 SET LONGC[HUNKSIZE] { 80 | n }

Sets the size (in bytes) of the increments SQL*Plus uses to retrieve a BLOB , BFILE, CLOB , LONG , NCLOB or XMLType value.

LONGCHUNKSIZE is not used for object relational queries such as CLOB , or NCLOB .

To set the size of the increments in which SQL*Plus retrieves LONG values to 100 bytes, enter

The LONG data will be retrieved in increments of 100 bytes until the entire value is retrieved or the value of SET LONG is reached, whichever is the smaller.

12.41.36 SET MARK[UP]

where markup_option consists of:

html_option

where csv_option has the following syntax:

where html_option has the following syntax:

Outputs reports in CSV format.

To be effective, SET MARKUP commands that change values in dynamic report output must be issued before the statement that produces the query output. The first statement that produces the query output triggers the output of CSV data that reflects the DELIMITER and QUOTE settings.

CSV is a mandatory SET MARKUP argument which specifies the type of output to be generated is CSV. The optional CSV arguments, ON and OFF, specify whether or not to generate CSV output. The default is OFF.

You can turn CSV output ON and OFF as required during a session.

The DELIMI[TER] character option enables you to specify a column separator character.

The QUOTE {ON|OFF} option enables you to turn text quoting on or off. The default is OFF.

QUOTE ON generates CSV output with all text quoted. Double quotes (“ ”) embedded within text are escaped.

You can turn quoted text ON and OFF as required during a session.

Supported Commands when SET MARKUP CSV is Enabled

If enabled, the following COLUMN commands will remain effective when SET MARKUP CSV is enabled:

COLUMN FORMAT

COLUMN HEADING

COLUMN NULL

Unsupported Commands when SET MARKUP CSV is Enabled

When SET MARKUP CSV is enabled, the following SQL*Plus commands will have no effect on the output:

When SET MARKUP CSV is enabled, the following SET commands will have no effect on the output:

SET HEADSEP

SET LINESIZE

SET NEWPAGE

SET PAGESIZE

SET SHIFTINOUT

SET TRIMOUT

SET TRIMSPOOL

SET UNDERLINE

When SET MARKUP CSV is enabled, the following COLUMN commands will have no effect on the output:

COLUMN ENTMAP

COLUMN FOLD_AFTER

COLUMN FOLD_BEFORE

COLUMN JUSTIFY

COLUMN NEWLINE

COLUMN NEW_VALUE

COLUMN NOPRINT

COLUMN OLD_VALUE

COLUMN WRAP

Use the SHOW MARKUP command to view the status of MARKUP options.

The following example illustrates the output when SET MARKUP CSV is enabled:

The following example illustrates how to extract all records from the Employee table of the database, with text strings unquoted:

The following example illustrates the output with the pipe ( | ) character specified as the delimiter:

Outputs HTML marked up text.

To be effective, SET MARKUP commands that change values in dynamic report output must occur before statements that produce query output. The first statement that produces query output triggers the output of information affected by SET MARKUP such as HEAD and TABLE settings. Subsequent SET MARKUP commands have no effect on the information already sent to the report.

SET MARKUP only specifies that SQL*Plus output will be HTML encoded. You must use SET MARKUP HTML ON SPOOL ON and the SQL*Plus SPOOL command to create and name a spool file, and to begin writing HMTL output to it. SET MARKUP has the same options and behavior as SQLPLUS -MARKUP.

See MARKUP Options for detailed information. For examples of usage, see Generating HTML Reports from SQL*Plus .

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:

The SET MARKUP example command is laid out for readability using line continuation characters "–" and spacing. Command options are concatenated in normal entry.

Use your favorite text editor to enter the commands necessary to set up the HTML options and the query you want for your report.

As this script contains SQL*Plus commands, do not attempt to run it with / (slash) from the buffer because it will fail. Save the script in your text editor and use START to execute it:

As well as writing the HTML spool file, employee.htm, the output is also displayed on screen because SET TERMOUT defaults to ON. You can view the spool file, employee.htm, in your web browser. It should appear something like the following:

Description of markup-gif-guid-3f48884c-3523-4847-8780-5bbb08d774d2.png follows

12.41.37 SET NEWP[AGE] { 1 | n | NONE}

Sets the number of blank lines to be printed from the top of each page to the top title . A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.

12.41.38 SET NULL text

Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.

Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank ("").

12.41.39 SET NUMF[ORMAT] format

Sets the default format for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions. Enter a number format for format . To use the default field width and formatting model specified by SET NUMWIDTH, enter

12.41.40 SET NUM[WIDTH] { 10 | n }

Sets the default width for displaying numbers. See the FORMAT clause of the COLUMN command for number format descriptions.

COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.

12.41.41 SET PAGES[IZE] { 14 | n }

Sets the number of lines on each page of output. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

12.41.42 SET PAU[SE] {ON | OFF | text }

Enables you to control scrolling of your terminal when running reports. You need to first, SET PAUSE text , and then SET PAUSE ON if you want text to appear each time SQL*Plus pauses.

SET PAUSE ON pauses output at the beginning of each PAGESIZE number of lines of report output . Press Return to view more output. SET PAUSE text specifies the text to be displayed each time SQL*Plus pauses. Multiple words in text must be enclosed in single quotes.

You can embed terminal-dependent escape sequences in the PAUSE command. These sequences allow you to create inverse video messages or other effects on terminals that support such characteristics.

12.41.43 SET RECSEP { WR[APPED] | EA[CH] | OFF}

RECSEP tells SQL*Plus where to make the record separation.

For example, if you set RECSEP to WRAPPED, SQL*Plus prints a record separator only after wrapped lines. If you set RECSEP to EACH, SQL*Plus prints a record separator following every row. If you set RECSEP to OFF, SQL*Plus does not print a record separator.

12.41.44 SET RECSEPCHAR { | c }

Defines the character to display or print to separate records.

A record separator consists of a single line of the RECSEPCHAR (record separating character) repeated LINESIZE times. The default is a single space.

12.41.45 SET ROWLIMIT { n | OFF }

Sets a limit for the number of rows to display for a query. By default, ROWLIMIT is OFF.

n can be any number between 1 and 2,000,000,000. An error is displayed when the value that is entered is outside this range.

OFF displays all the rows in the output.

SET ROWLIMIT ignores the value set by the SET FEEDBACK command.

The SET ROWLIMIT command is useful for queries that return a large number of rows, where users want to limit the number of rows to display without changing the query.

The purpose of the SET ROWLIMIT command is not to improve the performance of a query but to limit the rows to display. The number of rows fetched from the database is determined by the SET ARRAYSIZE value. Therefore, the server may return more rows than the value set in ROWLIMIT. When that happens, the query will finish and will not fetch any more data from the server. For example, a SQL statement to query a table contains 100 rows, but ROWLIMIT is set to 10 and ARRAYSIZE is set to 15. When the query is executed, 15 rows will first be fetched from the database. Since the rows returned satisfy the ROWLIMIT setting, the query will finish without fetching any more rows. However, if the ROWLIMIT is set to 20, then a second round trip is required to fetch the next 15 rows to satisfy the ROWLIMIT setting.

To set the number of rows to display as 2, enter:

By default, the FEEDBACK message is displayed only after 10 rows are displayed, but if ROWLIMIT is set to 2, the FEEDBACK message will be displayed for two rows even if the FEEDBACK is set to 10.

12.41.46 SET ROWPREFETCH { 1 | n }

Sets the number of rows that SQL*Plus will prefetch from the database at one time.

The default value is 1.

To set the number of prefetched rows to 200, enter

If you do not specify a value for n , the default is 1 row. This means that row prefetching is off.

To show the current setting for the number of rows that SQL*Plus will prefetch from the database at one time, enter

12.41.47 SET SECUREDCOL { OFF | ON} [UNAUTH[ORIZED] text ] [UNK[NOWN] text ]

Sets how secure column values are displayed in SQLPLUS output for users without permission to view a column and for columns with unknown security. You can choose either the default text or specify the text that is displayed. The default is OFF.

When column level security is enabled, and SET SECUREDCOL is set ON, output from SQLPLUS for secured columns or columns of unknown security level is replaced with either your customized text or the default indicators. This only applies to scalar data types. Complex Object data output is not affected.

ON displays the default indicator "*****" in place of column values for users without authorisation, and displays "?????" in place of column values where the security level is unknown. The indicators "*" and "?" are filled to the defined column length or the column length defined by a current COLUMN command.

OFF displays null values in place of column values for users without authorization, and in place of column values where the security level is unknown.

UNAUTH[ORIZED] text enables you to specify the text to be displayed in a secured column for users without authorization. This text appears instead of the default "*****"

You can specify any alpha numeric text up to the column length or a maximum of 30 characters. Longer text is truncated. Text containing spaces must be quoted.

UNK[NOWN] text enables you to specify the text to be displayed in a column of unknown security level. This text appears instead of the default "??????"

12.41.48 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.

ON uses the SIZE and FORMAT of the previous SET SERVEROUTPUT ON SIZE n FORMAT f , or uses default values if no SET SERVEROUTPUT command was previously issued in the current connection.

SIZE sets the number of bytes of the output that can be buffered within the Oracle Database server. The default is UNLIMITED. n cannot be less than 2000 or greater than 1,000,000.

Resources are not pre-allocated when SERVEROUTPUT is set. As there is no performance penalty, use UNLIMITED unless you want to conserve physical memory.

Every server output line begins on a new output line.

When WRAPPED is enabled SQL*Plus wraps the server output within the line size specified by SET LINESIZE, beginning new lines when required.

When WORD_WRAPPED is enabled, each line of server output is wrapped within the line size specified by SET LINESIZE. Lines are broken on word boundaries. SQL*Plus left justifies each line, skipping all leading whitespace.

When TRUNCATED is enabled, each line of server output is truncated to the line size specified by SET LINESIZE.

For detailed information about using UTL_FILE and associated utilities, see the Oracle Database PL/SQL Packages and Types Reference .

For more information on DBMS_OUTPUT.PUT_LINE, see Developing Applications with Oracle XA .

To enable text display in a PL/SQL block using DBMS_OUTPUT.PUT_LINE, enter

The following example shows what happens when you execute an anonymous procedure with SET SERVEROUTPUT ON:

The following example shows what happens when you create a trigger with SET SERVEROUTPUT ON:

To set the output to WORD_WRAPPED, enter

To set the output to TRUNCATED, enter

12.41.49 SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE] }

Enables correct alignment for terminals that display shift characters. The SET SHIFTINOUT command is useful for terminals which display shift characters together with data (for example, IBM 3270 terminals). You can only use this command with shift sensitive character sets (for example, JA16DBCS).

Use VISIBLE for terminals that display shift characters as a visible character (for example, a space or a colon). INVISIBLE is the opposite and does not display any shift characters.

To enable the display of shift characters on a terminal that supports them, enter

where ":" = visible shift character uppercase represents multibyte characters

lowercase represents singlebyte characters

12.41.50 SET SHOW[MODE] {ON | OFF }

Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET. ON lists the settings; OFF suppresses the listing. SHOWMODE ON has the same behavior as the obsolete SHOWMODE BOTH.

12.41.51 SET SQLBL[ANKLINES] {ON | OFF }

Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement.

To allow blank lines in a SQL statement, enter

The following output results:

12.41.52 SET SQLC[ASE] { MIX[ED] | LO[WER] | UP[PER]}

Converts the case of SQL commands and PL/SQL blocks just prior to execution.

SQL*Plus converts all text within the command, including quoted literals and identifiers, to uppercase if SQLCASE equals UPPER, to lowercase if SQLCASE equals LOWER, and makes no changes if SQLCASE equals MIXED.

SQLCASE does not change the SQL buffer itself.

12.41.53 SET SQLCO[NTINUE] { > | text }

Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).

To set the SQL*Plus command continuation prompt to an exclamation point followed by a space, enter

SQL*Plus will prompt for continuation as follows:

The default continuation prompt is "> ".

12.41.54 SET SQLN[UMBER] { ON | OFF}

Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block. ON sets the prompt to be the line number. OFF sets the prompt to the value of SQLPROMPT.

12.41.55 SET SQLPLUSCOMPAT[IBILITY] { x . y [. z ]}

Sets the behavior to that of the release or version specified by x . y [. z ].

Where x is the version number, y is the release number, and z is the update number. For example, 8.1.7, 9.0.1 or 10.2. The features affected by SQLPLUSCOMPATIBILITY are tabulated in the SQL*Plus Compatibility Matrix shown. You can also set the value of SQLPLUSCOMPATIBILITY using the -C[OMPATIBILITY] argument of the SQLPLUS command when starting SQL*Plus from the command line.

The default setting for SQLPLUSCOMPATIBILITY is the value of the SQL*Plus client.

It is recommended that you add SET SQLPLUSCOMPATIBILITY 12.2 to your scripts to maximize their compatibility with future versions of SQL*Plus.

12.41.55.1 SQL*Plus Compatibility Matrix

The SQL*Plus Compatibility Matrix tabulates behavior affected by each SQL*Plus compatibility setting. SQL*Plus compatibility modes can be set in three ways:

You can include a SET SQLPLUSCOMPATIBILITY command in your site or user profile. On installation, there is no SET SQLPLUSCOMPATIBILITY setting in glogin.sql. Therefore the default compatibility is 12.2.

You can use the SQLPLUS -C[OMPATIBILITY] { x . y [. z ]} command argument at startup to set the compatibility mode of that session.

You can use the SET SQLPLUSCOMPATIBILITY { x . y [. z ]} command during a session to set the SQL*Plus behavior you want for that session.

The following table shows the release of SQL*Plus which introduced the behavior change, and hence the minimum value of SQLPLUSCOMPATIBILITY to obtain that behavior. For example, to obtain the earlier behavior of the VARIABLE command, you must either use a version of SQL*Plus earlier than 9.0.1, or you must use a SQLPLUSCOMPATIBILITY value of less than 9.0.1. The lowest value that can be set for SQLPLUSCOMPATIBILITY is 7.3.4

Table 12-6 Compatibility Matrix

12.41.56 SET SQLPRE[FIX] { # | c }

Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.

12.41.57 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> ".

Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql, then substitution variables in SQLPROMPT are refreshed with each login or connect.

To change your SQL*Plus prompt to display your connection identifier, enter:

To set the SQL*Plus command prompt to show the current user, enter

To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:

To change your SQL*Plus prompt to display a variable you have defined, enter:

Text in nested quotes is not parsed for substitution. To have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter:

12.41.58 SET SQLT[ERMINATOR] { ; | c | ON | OFF}

Sets the character used to end script or data entry for PL/SQL blocks or SQL statements, to execute the script, and to load it into the buffer.

It cannot be an alphanumeric character or a whitespace. OFF means that SQL*Plus recognizes no command terminator; you terminate a SQL command by entering an empty line or a slash (/). If SQLBLANKLINES is set ON, you must use the BLOCKTERMINATOR to terminate a SQL command. ON resets the terminator to the default semicolon (;).

12.41.59 SET STATEMENTC[ACHE] { 0 | n }

Sets the statement cache size. The cache value allows caching of repeated statements. Therefore these statements do not need to be parsed again, resulting in performance improvement.

To set the statement cache size to 15, enter

If you do not specify a value for n , the default is 0. This means that statement caching is off.

The statement cache size can be any value between 0 and 32767.

Specify a cache size that is less than the value specified for the open cursor parameter in the Oracle Database initialization file, init.ora. Specifying a value of 0 will switch off statement caching. The <statement_cache> setting in the oraaccess.xml file can override the SET STATEMENTCACHE setting in SQL*Plus. For more information about oraaccess.xml , see the Oracle Call Interface Programmer's Guide .

To show the current setting for the statement cache size, enter

12.41.60 SET SUF[FIX] { SQL | text }

Sets the default file extension that SQL*Plus uses in commands that refer to scripts. SUFFIX does not control extensions for spool files.

To change the default command-file extension from the default, .SQL to .TXT, enter

If you then enter

SQL*Plus will look for a file named EXAMPLE.TXT instead of EXAMPLE.SQL.

12.41.61 SET TAB { ON | OFF}

Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.

12.41.62 SET TERM[OUT] { ON | OFF}

Controls the display of output generated by commands in a script that is executed with @, @@ or START. OFF suppresses the display so that you can spool output to a file without displaying the output on screen. ON displays the output on screen. TERMOUT OFF does not affect output from commands you enter interactively or redirect to SQL*Plus from the operating system.

12.41.63 SET TI[ME] {ON | OFF }

Controls the display of the current time. ON displays the current time before each command prompt. OFF suppresses the time display.

12.41.64 SET TIMI[NG] {ON | OFF }

Controls the display of timing statistics.

ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command.

See TIMING for information on timing multiple commands.

The format of timing statistics is dependent on the operating system. In Linux and Windows, the timing statistics are in 24 hour format displaying hours, minutes, seconds and hundredths of seconds

If you enter

SQL*Plus displays output like

12.41.65 SET TRIM[OUT] { ON | OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each displayed line. ON removes blanks at the end of each line, improving performance especially when you access SQL*Plus from a slow communications device. OFF enables SQL*Plus to display trailing blanks. TRIMOUT ON does not affect spooled output.

12.41.66 SET TRIMS[POOL] {ON | OFF }

Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF enables SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output.

12.41.67 SET UND[ERLINE] { - | c | ON | OFF}

Sets the character used to underline column headings in reports. The underline character cannot be an alphanumeric character or a white space. ON or OFF turns underlining on or off. ON changes the value of c back to the default "-".

12.41.68 SET VER[IFY] { ON | OFF}

Controls whether to list the text of a SQL statement or PL/SQL command before and after replacing substitution variables with values. ON lists the text; OFF suppresses the listing.

12.41.69 SET WRA[P] { ON | OFF}

Controls whether to truncate the display of a selected row if it is too long for the current line width. OFF truncates the selected row; ON enables the selected row to wrap to the next line.

Use the WRAPPED and TRUNCATED clauses of the COLUMN command to override the setting of WRAP for specific columns.

12.41.70 SET XMLOPT[IMIZATIONCHECK] [ON| OFF ]

Controls whether only XML queries and DML operations that are fully optimized are executed. ON prevents the execution of any XML query or DML operation that cannot be fully optimized and writes the reason in the trace file. OFF does not prevent the execution of such queries and operations. OFF is the default.

SET XMLOPT[IMIZATIONCHECK] ON is only to assist during development and debugging an XML query or DML operation.

12.41.71 SET XQUERY BASEURI { text }

Specifies the base URI used to resolve relative URIs in functions. It enables the prefix of the file accessed by an XQuery to be changed.

To unset the BASEURI, set an empty string, for example:

Take care to enter valid values as values are checked only when an XQUERY command is issued.

This is equivalent to:

12.41.72 SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}

Sets the ordering of output from an XQuery. There are three values:

UNORDERED specifies that results are sorted in the order they are retrieved from the database.

ORDERED specifies that results are sorted as defined by the XQuery.

DEFAULT specifies the database default. In Oracle Database 10 g the default is UNORDERED.

When SET XQUERY ORDERING is not set, the default is DEFAULT (UNORDERED).

12.41.73 SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}

Sets the node identity preservation mode. The preservation mode applies to all expressions that either create a new node (such as element constructors) or return an item or sequence containing nodes (such as path expressions). There are three values:

BYVALUE specifies that the node identity need not be preserved. This means that any node operation such as creation, or that is returned as a result of an expression is deep copied and loses its context from the original tree. Subsequent operations on this node that test for node identity, parent or sibling axes or ordering will be undefined.

BYREFERENCE specifies that node identities are to be preserved. Subsequent operations on this node preserve the node's context and definition.

DEFAULT specifies the database default. In Oracle Database 10 g the default is BYVALUE.

When SET XQUERY NODE is not set, the default is DEFAULT (BYVALUE).

12.41.74 SET XQUERY CONTEXT { text }

Specifies an XQuery context item expression. A context item expression evaluates to the context item, which may be either a node (as in the expression fn:doc("bib.xml")//book[fn:count(./author)>1]), or an atomic value (as in the expression (1 to 100)[. mod 5 eq 0]).

To unset the XQUERY CONTEXT, set an empty string, for example:

Cambridge Dictionary

  • Cambridge Dictionary +Plus

Meaning of set something off in English

Set something off.

Your browser doesn't support HTML5 audio

  • be in the first flush of idiom
  • be/get in on the ground floor idiom
  • get down to business idiom
  • here goes! idiom
  • hit the ground running idiom
  • start on something
  • start/set/get the ball rolling idiom
  • the ground floor

You can also find related words, phrases, and synonyms in the topics:

( MAKE NOTICEABLE )

  • accessorize
  • beautification
  • ornamentation
  • over-glamorize
  • overdecorate
  • overdecoration
  • undecorated
  • unembellished
  • window dressing
  • yarn bombing
  • set someone off

set off | American Dictionary

  • set off something

set sth off | Business English

Set sth off, examples of set something off, translations of set something off.

Get a quick, free translation!

{{randomImageQuizHook.quizId}}

Word of the Day

an animal that eats only plants

World-famous and newfound fame (Collocations of ‘famous’ and ‘fame’)

World-famous and newfound fame (Collocations of ‘famous’ and ‘fame’)

set define off

Learn more with +Plus

  • Recent and Recommended {{#preferredDictionaries}} {{name}} {{/preferredDictionaries}}
  • Definitions Clear explanations of natural written and spoken English English Learner’s Dictionary Essential British English Essential American English
  • Grammar and thesaurus Usage explanations of natural written and spoken English Grammar Thesaurus
  • Pronunciation British and American pronunciations with audio English Pronunciation
  • English–Chinese (Simplified) Chinese (Simplified)–English
  • English–Chinese (Traditional) Chinese (Traditional)–English
  • English–Dutch Dutch–English
  • English–French French–English
  • English–German German–English
  • English–Indonesian Indonesian–English
  • English–Italian Italian–English
  • English–Japanese Japanese–English
  • English–Norwegian Norwegian–English
  • English–Polish Polish–English
  • English–Portuguese Portuguese–English
  • English–Spanish Spanish–English
  • English–Swedish Swedish–English
  • Dictionary +Plus Word Lists
  • set something off (CAUSE)
  • set something off (MAKE NOTICEABLE)
  • Business    Phrasal verb Noun
  • Translations
  • All translations

Add set something off to one of your lists below, or create a new one.

{{message}}

Something went wrong.

There was a problem sending your report.

  • Site Feedback

escape ON|OFF

Question and answer.

Tom Kyte

Thanks for the question, Beth.

Asked: October 15, 2001 - 8:01 pm UTC

Last updated: October 02, 2009 - 7:47 am UTC

Version: 8.1.6

set define off

and Tom said...

A reader, October 23, 2001 - 10:16 am UTC

A reader, July 05, 2005 - 6:43 pm UTC

Helped me fix my insert

Jim, July 20, 2006 - 3:01 pm UTC

special characters while building insert statement

Ravi, September 28, 2009 - 4:58 pm UTC

Ravi, September 28, 2009 - 4:59 pm UTC

A reader, October 02, 2009 - 8:00 am UTC

You are the best!

Ravi, October 02, 2009 - 11:17 am UTC

more

More to Explore

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Make Me Engineer

When or Why to use a “SET DEFINE OFF” in Oracle Database

By default, SQL Plus treats ‘&’ as a special character that begins a substitution string. This can cause problems when running scripts that happen to include ‘&’ for other reasons:

If you know your script includes (or may include) data containing ‘&’ characters, and you do not want the substitution behaviour as above, then use set define off to switch off the behaviour while running the script:

You might want to add set define on at the end of the script to restore the default behaviour.

Related Contents:

  • Determining location of relevant tnsnames.ora file
  • ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  • Oracle: Import CSV file
  • PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  • Sql*plus always returns exit code 0?
  • Convert epoch to date in sqlplus / Oracle
  • SQL*Plus how to accept text variable from prompt?
  • How can I issue a single command from the command line through sql plus?
  • Calculate difference between 2 date / times in Oracle SQL
  • How to put more than 1000 values into an Oracle IN clause [duplicate]
  • How do I ignore ampersands in a SQL script running from SQL Plus?
  • Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  • List of foreign keys and the tables they reference in Oracle DB
  • ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
  • Default row ordering for select query in oracle
  • oracle convert unix epoch time to date
  • Using date in a check constraint, Oracle
  • how to export data from log table to email body in oracle
  • What is the dual table in Oracle?
  • How to handle Day Light Saving in Oracle database
  • Fetch Oracle table type from stored procedure using JDBC
  • Passing an array of data as an input parameter to an Oracle procedure
  • Can We use threading in PL/SQL?
  • No more data to read from socket error
  • How to generate entire DDL of an Oracle schema (scriptable)?
  • ORA-01882: timezone region not found
  • What is the difference between “AS” and “IS” in an Oracle stored procedure?
  • Boolean Field in Oracle
  • Oracle client ORA-12541: TNS:no listener [closed]
  • Oracle insert if not exists statement
  • How to get Oracle create table statement in SQL*Plus
  • Get resultset from oracle stored procedure
  • Does Oracle have an equivalent of SQL Server’s table variables?
  • Auto-increment in Oracle without using a trigger
  • Deploying and Configuring ODP.NET to work without installation with Entity Framework
  • ORA-06508: PL/SQL: could not find program unit being called
  • Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  • Using Alias in query resulting in “command not properly ended”
  • How to generate an entity-relationship (ER) diagram using Oracle SQL Developer
  • How to create a user in Oracle 11g and grant permissions
  • Oracle: how to INSERT if a row doesn’t exist
  • sqlplus statement from command line
  • Create Pivot view in SQL from a SQL table
  • How to get primary key column in Oracle?
  • ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying
  • Faster alternative in Oracle to SELECT COUNT(*) FROM sometable
  • Find out the history of SQL queries
  • How to drop multiple interval partitions based on date?
  • Why are Oracle table/column/index names limited to 30 characters?
  • What is the difference between precision and scale?

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

  • More from M-W
  • To save this word, you'll need to log in. Log In

Definition of setoff

 (Entry 1 of 2)

Definition of set off  (Entry 2 of 2)

transitive verb

intransitive verb

  • embellisher
  • embellishment
  • garnishment
  • ornamentation

Examples of setoff in a Sentence

These examples are programmatically compiled from various online sources to illustrate current usage of the word 'setoff.' Any opinions expressed in the examples do not represent those of Merriam-Webster or its editors. Send us feedback about these examples.

Word History

1621, in the meaning defined at sense 1

circa 1598, in the meaning defined at transitive sense 1a

Dictionary Entries Near setoff

Cite this entry.

“Setoff.” Merriam-Webster.com Dictionary , Merriam-Webster, https://www.merriam-webster.com/dictionary/setoff. Accessed 4 Mar. 2024.

Kids Definition

Kids definition of set off, legal definition, legal definition of set-off.

Legal Definition of set off  (Entry 2 of 2)

More from Merriam-Webster on setoff

Nglish: Translation of setoff for Spanish Speakers

Subscribe to America's largest dictionary and get thousands more definitions and advanced search—ad free!

Play Quordle: Guess all four words in a limited number of tries.  Each of your guesses must be a real 5-letter word.

Can you solve 4 words at once?

Word of the day.

See Definitions and Examples »

Get Word of the Day daily email!

Popular in Grammar & Usage

8 grammar terms you used to know, but forgot, homophones, homographs, and homonyms, your vs. you're: how to use them correctly, every letter is silent, sometimes: a-z list of examples, more commonly mispronounced words, popular in wordplay, the words of the week - mar. 1, 'blue moon,' 'wolf moon,' and other moons to look for throughout the year, 10 scrabble words without any vowels, 12 more bird names that sound like insults (and sometimes are), 8 uncommon words related to love, games & quizzes.

Play Blossom: Solve today's spelling word game by finding as many words as you can using just 7 letters. Longer words score more points.

  • Install App

SQL & PL/SQL

For appeals, questions and feedback, please email [email protected]

SET DEFINE OFF

set define off

[Oracle] Set Define Off; 을 통해 & 특수문자를 저장하기

Set Define Off; 을 통해 & 특수문자를 저장하기

개발도구에서 Insert 문장을 백업 할 때 보이는 Set Define Off;가 무엇을 뜻하지 알아보겠습니다.

SQLPLUS & 입력하기

테이블 TB_TEST 내에 있는 컬럼 V 에 대하여, 'You & I' 라는 문자를 입력해 보았을 때를 가정해 봅시다.

set define off

i 의 값을 입력하라고 합니다. 원래 의도는 'You & I'를 입력하려고 하였으나, i가 & 때문에 입력하는 값으로 인식이 되어 버린 것이랍니다.

set define off

U 를 입력하고 엔터를 눌러보겠습니다.

set define off

데이터 'You & I' 를 인서트 하려고 시도하였으나, 실제로 데이터는 'You U'가 인서트 되어 행이 만들어진 것을 확인할 수 있습니다. 결국, '& I'가 'U'로 대체된 것이랍니다.

set define off

실제로도 'You & I' 가 아니라 'You U' 로 행이 생성됨을 확인할 수 있습니다.

set define off

Show Define; 을 입력하면 define "&" (hex 26) 을 확인할 수 있습니다. & 문자는 기본적으로 변수설정문자입니다! 해당 게시물의 핵심 주제이기도 하답니다.

set define off

오늘의 주제, Set Define Off; 를 입력하면 define OFF 가 되는 것을 확인할 수 있습니다.

set define off

Insert into TB_TEST(v) values ('You & I'); 가 바로 입력이 됩니다. 즉, 특수문자 & 을 단순문자로 입력합니다.

set define off

Set Define On; 하시면 다시 & 문자를 변수설정자로 지정합니다.

set define off

You U (Set Define On;) You & I (Set Define Off;) 행을 확인할 수 있습니다.

결과적으로 Set Define Off는 &과 같은 특수문자를 문자 그대로 보도록 하는 이스케이프 역할을 하는 것을 확인할 수 있습니다.

Hiptoro

NFL News: Kansas City Chiefs Andy Reid Breaks the Mold, Why He's Scouting Future Stars at NFL Combine 2024

I n the ever-evolving landscape of the NFL, the Kansas City Chiefs, under the seasoned guidance of Andy Reid, continue to set benchmarks that not only define their dominance but also underscore their commitment to long-term success.

Fresh off their victory in Sin City, where they defended their title and shattered a two-decade-long drought, the Chiefs are eyeing an unprecedented three-peat in league history. However, amidst celebrations and preparations for another run at glory, Andy Reid’s recent appearance at the 2024 NFL Combine in Indianapolis has sparked discussions and admiration alike.

The Essence of Andy Reid’s Presence in Indianapolis

While the trend among NFL coaches seems to lean towards skipping the Combine, Andy Reid’s decision to attend personally speaks volumes about his approach and the values he instills within the Chiefs’ framework. In an insightful encounter with NFL Network’s James Palmer, Reid shared his perspective on the importance of the event.

Unlike others who may prioritize athletic drills, Reid places a significant emphasis on personal interaction, assessing how players handle themselves in challenging situations and under the scrutiny of potential future coaches.

“It’s the first time that you have a chance to kinda meet the guys. Sitting in front of them, talk to them, see how they handle you. How they handle your coaches,” Reid remarked, underlining his focus on character and mental fortitude over physical prowess alone.

This approach not only differentiates Reid but also highlights his dedication to building a team that excels both on and off the field.

Building for the Future: The Kansas City Chiefs’ Strategy

The Chiefs’ ability to maintain continuity and adapt has been a cornerstone of their success. Reid attributes this to the seamless communication and vision shared with GM Brett Veach, emphasizing the collective effort in steering the team toward sustained excellence.

Despite facing challenges , such as the offensive front’s struggles, Reid’s optimism and forward-thinking are evident in his acknowledgment of areas needing improvement and his praise for players like Travis Kelce and Rashee Rice who have stepped up significantly.

Patrick Mahomes, the Chiefs’ luminary quarterback, remains a central figure in Reid’s scheme. His leadership, patience, and positive attitude are pivotal, in setting a standard for the team. Reid’s belief in Mahomes and the team’s core values underscores a culture of resilience and ambition.

A Future Secured: Andy Reid’s Anticipated Extension

The subject of Andy Reid’s contract extension, while delicately handled , is met with anticipation and confidence by Reid himself. Expected to sign a lucrative deal, Reid’s commitment to Kansas City is unwavering. He expresses profound appreciation for the organization and the environment fostered by key figures like Clark Hunt, Mark Donovan, and Brett Veach, emphasizing a culture of respect and freedom of expression.

A Testament to Leadership and Vision

Andy Reid’s engagement with the NFL Combine and his strategic outlook for the Kansas City Chiefs exemplify a leadership style that prioritizes depth, character, and a collective pursuit of excellence. As the Chiefs prepare for another potential historic run, Reid’s methods and philosophy remain integral to their success .

In a league defined by constant change and fierce competition, Reid’s commitment to personal engagement and team-building sets a benchmark for others to follow, ensuring the Chiefs remain at the forefront of the NFL’s elite.

Source: Andy Reid

In the ever-evolving landscape of the NFL, the Kansas City Chiefs, under the seasoned guidance of Andy Reid, continue to set benchmarks that not only define their dominance but also underscore their commitment to long-term success. Fresh off their victory in Sin City, where they defended their title and shattered a two-decade-long drought, the Chiefs are eyeing an unprecedented three-peat in league history. However, amidst celebrations and preparations for another run at glory, Andy Reid’s recent appearance at the 2024 NFL Combine in Indianapolis has sparked discussions and admiration alike. The Essence of Andy Reid’s Presence in Indianapolis While the […]

IMAGES

  1. A Guide to 25+ Phrasal Verbs with Set • 7ESL

    set define off

  2. [Solved] When or Why to use a "SET DEFINE OFF" in Oracle

    set define off

  3. sql

    set define off

  4. [Oracle] Set Define Off; 을 통해 & 특수문자를 저장하기

    set define off

  5. Phrasal Verb SET OFF

    set define off

  6. set define off verify off feedback offwhenever sqlerror exit sql.docx

    set define off

VIDEO

  1. Define Universal Set #shorts

  2. Set clear goals Define specific and achievable goals for your trading journey. #trading #income

COMMENTS

  1. When or Why to use a "SET DEFINE OFF" in Oracle Database

    Learn how to use SET DEFINE OFF to disable the substitution of variables in SQL*Plus scripts. See examples, explanations and answers from Oracle experts and users.

  2. SET DEFINE

    Learn how to use SET DEFINE to change or disable the prefix character for substitution variables in SQL*Plus. See the syntax, parameters, and examples from the book Oracle SQL*Plus: The Definitive Guide, 2nd Edition.

  3. Tuning SQL*Plus

    SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values. 9.5.5 SET FLUSH OFF SET FLUSH OFF enables the operating system to buffer output. ON disables buffering and flushes output to the screen. Any benefit from setting FLUSH either ON or OFF depends on your operating system and data.

  4. sql

    4. Create a text file on your PC which contains set define off and any other set-up instructions you might want. Then in SQL developer, go to the Tools menu and pick the Preferences... option. In the panel on the left click on Database. Then either type the path to the file you created, or click Browse and navigate to the file in the 'Select ...

  5. Using Substitution Variables

    Learn how to use substitution variables to repeat or change values in SQL*Plus commands. Substitution variables are preceded by one or two ampersands and can be defined, referenced, or deleted with SQL*Plus commands.

  6. Unlocking the Power of Oracle: What Does Set Define Off Do?

    Oracle Set Define Off is a command that disables the special meaning of the "&" character in SQL queries. In Oracle, "&" is a substitution character that prompts the user for a value and replaces that value in the query. In some cases, this can cause issues if you're running a script or trying to execute a query with an ...

  7. SQL*Plus Substitution Variables

    Learn how to use the DEFINE command to create and manage substitution variables in SQL*Plus. Substitution variables can replace hard-coded text in SQL and SQL*Plus statements, and can be set to script parameters, query values, or user input.

  8. Quick Question for Oracle Set define off query

    Quick Question for Oracle Set define off query Hi All, I just want to know that if i turned off the feature i.e. 'SET DEFINE OFF' from my oracle database, will there by any consequences / disadvantages with my data or database, as i really need to turn it off for some queries. Please advise if it will not break anything.

  9. Set Define OFF

    Learn how to use SET DEFINE to change or turn off the prefix character for substitution variables in SQL*Plus. See examples, references and tips for using SET DEFINE in scripts.

  10. How to allow special characters to store in DB

    SQL> set define off '&' is only a special character to sqlplus, you just need to tell sqlplus to stop thinking it is special. set define off does that. Comment: Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog.

  11. SET System Variable Summary

    SET DEFINE sets the character used to prefix variables to c. See the full list of SQL*Plus system variables and their descriptions in the User's Guide and Reference.

  12. SET SOMETHING OFF

    SET SOMETHING OFF definition: 1. to cause an activity or event, often a series of events, to begin or happen: 2. to cause a loud…. Learn more.

  13. escape ON|OFF

    Learn how to use the SET ESCAPE option in SQL*Plus to define the character for variable substitution. See examples of how the escape character affects the query and the SQL escape character.

  14. When or Why to use a "SET DEFINE OFF" in Oracle Database

    SQL> set define off SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd'); 1 row created. SQL> select customer_name from customers; CUSTOMER_NAME ----- Marks & Spencers Ltd You might want to add set define on at the end of the script to restore the default behaviour.

  15. Setoff Definition & Meaning

    set off: [verb] to put in relief : show up by contrast. adorn, embellish. to set apart : make distinct or outstanding.

  16. How can I disable the @ symbol in SQL scripts for sqlplus?

    Concatenation might do the trick. That would keep the CSS clean. SET DEFINE OFF SET SQLBLANKLINES ON SET SQLTERMINATOR OFF SET SQLPREFIX '%' SET SCAN OFF DECLARE loginId NUMBER; ncount NUMBER; css VARCHAR2(32767) DEFAULT ' ' || '@media only screen and (min-width: 980px) { .footer-column-copy { margin: 0; padding-top: 0; padding-bottom: 80px; background-size: 55px; background-position: top ...

  17. Why is SET DEFINE OFF not working for the colon (:) character?

    1. Set Define Off is a SQLPlus command and it prevents SQLPlus from treating & as a special character. & is also specific to SQLPlus and have no meaning outside it. When define is on, the SQLPlus replaces the substitution variables with actual values and then pass it to the Oracle SQL engine. On the other hand, : are real bind variable that ...

  18. SET DEFINE OFF

    SET DEFINE OFF. blueora Feb 20 2010 — edited Mar 1 2012. I had an insert script with data strings having '&' character. While executing it on sqlplus it prompted for a value at each occurrence of '&' . Simple solution was to put a "SET DEFINE OFF" at the beginning of the script. It suppressed the prompt for value.

  19. [Oracle] Set Define Off; 을 통해 & 특수문자를 저장하기

    오늘의 주제, Set Define Off; 를 입력하면 define OFF 가 되는 것을 확인할 수 있습니다. Insert into TB_TEST (v) values ('You & I'); 가 바로 입력이 됩니다. 즉, 특수문자 & 을 단순문자로 입력합니다. Set Define On; 하시면 다시 & 문자를 변수설정자로 지정합니다. You U (Set Define On ...

  20. NFL News: Kansas City Chiefs Andy Reid Breaks the Mold, Why He's ...

    In the ever-evolving landscape of the NFL, the Kansas City Chiefs, under the seasoned guidance of Andy Reid, continue to set benchmarks that not only define their dominance but also underscore ...

  21. sql

    A question and answers about how to insert a value with '&' in TOAD without getting a substitution variable prompt. One answer suggests using set define off at the beginning of the script.

  22. oracle

    The SET DEFINE [ON|OFF] command is specific to SQLPlus; it's processed by the SQLPlus client, it's not sent to the database.That statement is not a valid Oracle SQL statement. Some SQLPlus commands are accepted and honored by TOAD if you run the statements as a script; I've used SQLPlus style commands for column formatting, but I've not tested the SET DEFINE command though.