• Install App

SQL & PL/SQL

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

how to execute a function and return the result into a bind variable

oracle assign function result to variable

Assigning Set Operation Results to Nested Table Variable

  • Script Name Assigning Set Operation Results to Nested Table Variable
  • Description This example assigns the results of several MULTISET operations and one SET function invocation of the nested table variable answer, using the procedure print_nested_table to print answer after each assignment.
  • Area PL/SQL General
  • Referenced In Database PL/SQL Language Reference
  • Contributor Sarah Hirschfeld (Oracle)
  • Created Thursday February 02, 2017
  • Statement 1 The procedure uses the collection methods FIRST and LAST. DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer nested_typ; PROCEDURE print_nested_table (nt nested_typ) IS output VARCHAR2(128); BEGIN IF nt IS NULL THEN DBMS_OUTPUT.PUT_LINE('Result: null set'); ELSIF nt.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Result: empty set'); ELSE FOR i IN nt.FIRST .. nt.LAST LOOP -- For first to last element output := output || nt(i) || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE('Result: ' || output); END IF; END print_nested_table; BEGIN answer := nt1 MULTISET UNION nt4; print_nested_table(answer); answer := nt1 MULTISET UNION nt3; print_nested_table(answer); answer := nt1 MULTISET UNION DISTINCT nt3; print_nested_table(answer); answer := nt2 MULTISET INTERSECT nt3; print_nested_table(answer); answer := nt2 MULTISET INTERSECT DISTINCT nt3; print_nested_table(answer); answer := SET(nt3); print_nested_table(answer); answer := nt3 MULTISET EXCEPT nt2; print_nested_table(answer); answer := nt3 MULTISET EXCEPT DISTINCT nt2; print_nested_table(answer); END; Result: 1 2 3 1 2 4 Result: 1 2 3 2 3 1 3 Result: 1 2 3 Result: 3 2 1 Result: 3 2 1 Result: 2 3 1 Result: 3 Result: empty set

Additional Information

  • Database on OTN SQL and PL/SQL Discussion forums Oracle Database Download Oracle Database
  • Skip to content
  • Accessibility Policy
  • Oracle blogs
  • Lorem ipsum dolor

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

oracle assign function result to variable

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
  • Site Feedback

Assign return value to Unix Variable

Question and answer.

Connor McDonald

Thanks for the question, Bhaskara.

Asked: July 14, 2000 - 10:07 am UTC

Last updated: October 27, 2021 - 2:16 am UTC

Version: 8.1.5

oracle assign function result to variable

and Tom said...

A reader, December 23, 2003 - 2:22 pm UTC

Tom Kyte

if I rememeber right

A reader, December 23, 2003 - 5:54 pm UTC

How to assign value from SQL Plus to Unix Shell Variable

D. Sasi kumar, June 21, 2005 - 12:31 am UTC

A reader, June 21, 2005 - 8:32 am UTC

EOF=END OF FILE

Paul, June 21, 2005 - 10:25 am UTC

Getting more than one value

John, June 21, 2005 - 10:51 am UTC

calling functions; procs (in out params) in packages

cosmin, June 21, 2005 - 11:32 am UTC

Using arrays (KSH)

andrew, June 21, 2005 - 1:26 pm UTC

Playing around with arrays, stored procs and host variables

andrew, June 21, 2005 - 1:30 pm UTC

To "A reader" and Tom...

Dan Kefford, June 21, 2005 - 8:52 pm UTC

cosmin, June 23, 2005 - 11:23 am UTC

Being a SYBASE guy needing to know how to do it with ORACLE

Damian, July 14, 2005 - 10:50 am UTC

Shell Programing

Reader, April 06, 2006 - 10:26 am UTC

Alexander the ok, February 23, 2007 - 12:24 pm UTC

Alexander the ok, February 27, 2007 - 10:10 am UTC

Alexander the ok, February 27, 2007 - 11:45 am UTC

Here one way

Michael, February 27, 2007 - 2:18 pm UTC

Alexander the ok, February 27, 2007 - 2:37 pm UTC

Arrays in Unix and Oracle

Aravind, May 28, 2007 - 4:13 am UTC

Chandru, June 05, 2009 - 2:13 pm UTC

omv, September 06, 2010 - 8:35 am UTC

varma, September 06, 2010 - 1:05 pm UTC

Unix using count

anima, February 21, 2012 - 9:31 am UTC

Getting error while passing shell variable to utl.mail.send

Ashish, April 23, 2012 - 2:35 am UTC

Ashish, April 23, 2012 - 3:32 am UTC

Ashish, April 23, 2012 - 11:53 pm UTC

Shell variable is not working

ashish, April 30, 2012 - 2:32 am UTC

reader, July 10, 2012 - 12:06 pm UTC

reader, July 13, 2012 - 9:08 am UTC

Return Variable Replacing Comma with Space

Manas, October 18, 2012 - 7:06 am UTC

Use functions

A reader, October 19, 2012 - 7:56 am UTC

Datafile adding script

Mohan Gosu, November 30, 2013 - 2:04 pm UTC

Found another way to pass data to the shell

A reader, March 09, 2015 - 2:03 pm UTC

Another way to get the result into unix variable

Ullas gupta, March 19, 2015 - 9:27 am UTC

Can't pass the exitcode variable from sql to unix

shax, June 23, 2015 - 5:28 pm UTC

test, June 23, 2015 - 5:31 pm UTC

Returning exit code from sqlplus in UNIX

Ken Bonacci, October 26, 2021 - 4:44 pm 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!

Assign function result in array variable then display in table

Summary: I am using js function to split the comma-separated string values( O278219,PRW01022N-0890-SAFSA,Q1085,L10397257,MM20C24013,N38756 )into array how would i assign the result into array result could be dynamic length either 6,5..

then display the data in a table.

Content (required):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

  • Category 218
  • Composite Component
  • Custom Code
  • Category 218-2

Howdy, Stranger!

To view full details, sign in.

Don't have an account? Click here to get started!

  • Developing Applications with Oracle Visual Builder in Oracle Integration Generation 2
  • Develop Applications
  • Work with JSON Action Chains
  • Built-in Actions

Add a Call Variable Action

You add a Call Variable action to an action chain to call a method on an InstanceFactory variable defined for the current container (flow, page, or application). You can use this action to call any method on the current instance associated with the InstanceFactory variable, including asynchronous ones.

Before you use a Call Variable action in an action chain, make sure an InstanceFactory type variable is already defined for the application. See Create a Type From Code .

To add a Call Variable action to an action chain:

  • Open the Actions editor for the application.
  • Create an action chain, or open an existing action chain to add the action in the editor.

Add icon

  • Update the ID field in the Properties pane to make the action more easily identifiable.
  • From the Variables drop-down list, select an InstanceFactory type variable defined for the application.

Description of actionchaineditor-callvariable.png follows

The method's return value will be part of the outcome passed to the subsequent chain.

IMAGES

  1. Oracle Analytic Functions

    oracle assign function result to variable

  2. How do I assign a variable?

    oracle assign function result to variable

  3. Function in Oracle

    oracle assign function result to variable

  4. How do I assign a variable?

    oracle assign function result to variable

  5. Oracle Analytic Functions

    oracle assign function result to variable

  6. Analytic Functions in Oracle

    oracle assign function result to variable

VIDEO

  1. Oracle Function Part 03

  2. Oracle Database #03

  3. Oracle Function Part 03

  4. Oracle

  5. assign variable in c program||#cprogramming #coding #cprogrammingvideo #tutorial #shorts #trending

  6. UNISTR Function Oracle

COMMENTS

  1. plsql

    Oracle DB: how to store function result into variable inside procedure Ask Question Asked 7 years, 1 month ago Modified 7 years, 1 month ago Viewed 8k times 3 Good day. I have a function: create function get_n (search tt.pp%type) return number is rc number; begin select count (*) into rc from tt where tt.pp=search; return (rc); end; /

  2. How to store single result in variable and reuse it in a query (Oracle

    So is it possible to tell Oracle to store this single result in a variable and use this variable to build up new queries - explicitly to use in in the conditional clause of other queries? If so, perhaps somebody could post a simple example - most simple perhaps would be sth. like. SELECT :VARIABLE FROM DUAL

  3. oracle

    4 Answers Sorted by: 4 You can create it like this: CREATE OR REPLACE PROCEDURE procedurename (param1 NUMBER, param2 varchar (20), returnvalue OUT NUMBER); IS BEGIN ... your code END; And then use it like this: returnvalue NUMBER; procedurename (0, 'xxx', returnvalue); dbms_output.putline (returnvalue);

  4. Store a query result in a variable and use in another query

    create table t as select rownum id from dual connect by level <= 10; VARIABLE v_name number; BEGIN SELECT count (*) INTO :v_name FROM T; END; / spool out.log SELECT ID, :v_name FROM T; spool off Out.log will hold the results of your query. Of course, the real question here is: Why do you need two statements?

  5. PL/SQL Language Elements

    You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR. Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. You can assign the result of a comparison or other test to a Boolean variable. You can assign the value of an expression to a specific field in a ...

  6. Assign result of PL/SQL function to DEFINE variable

    Assign result of PL/SQL function to DEFINE variable 592acd6d-75c8-4d67-be32-6da67672485d May 22 2020 — edited May 22 2020 Hello, I have written the following SQL function for generating a random UUID: CREATE OR REPLACE FUNCTION GENERATE_UUID RETURN VARCHAR2 IS uuid VARCHAR2 (255 BYTE); BEGIN

  7. How do I declare and use variables in Oracle?

    using a bind variable: var startdate number; begin select 20110501 into :startdate from dual; end; / PL/SQL procedure successfully completed. SQL> print startdate STARTDATE ---------- 20110501

  8. PL/SQL Collections and Records

    Assigning Values to Collection Variables Multidimensional Collections Collection Comparisons Collection Methods Collection Types Defined in Package Specifications Oracle Database SQL Language Reference for information about the CREATE DATABASE LINK statement "Querying a Collection"

  9. how to execute a function and return the result into a bind variable

    I am trying to calculate the sum of salaries of all persons with a particular JOB_ID using a function TOTAL_INCOME(v_job_id). create or replace function total_income +(v_job_id IN varchar2)+ RETURN number IS v_total number(6); cursor get_sal is select salary from employees where job_id = v_job_id; BEGIN v_total := 0; for emp in get_sal loop

  10. Working with Strings in PL/SQL

    Using built-in functions with strings. Once you assign a string to a variable, you most likely need to analyze the contents of that string, change its value in some way, or combine it with other strings. Oracle Database offers a wide array of built-in functions to help you with all such requirements. Here are some of the most commonly used ...

  11. Assigning Set Operation Results to Nested Table Variable

    Assigning Set Operation Results to Nested Table Variable. Description This example assigns the results of several MULTISET operations and one SET function invocation of the nested table variable answer, using the procedure print_nested_table to print answer after each assignment. The procedure uses the collection methods FIRST and LAST.

  12. Assigning the output of a SQL query to variable

    Assigning the output of a SQL query to variable Ask Question Asked 10 years, 6 months ago Modified 4 years, 1 month ago Viewed 144k times 10 I am connecting to oracle database and firing a query and assigning the output to variable But when I echo the value of the variable it doesn't get printed correctly.

  13. SQL*Plus Substitution Variables

    1 Introduction 2 Using Substitution Variables 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

  14. plsql

    1 Answer. You do not need PL/SQL for that, you can use plain SQL after initializing a bind variable, for example. variable v_pNBr number; exec :v_pNBr := 100150; select * from TARGETTABLE where PROCESSNBR = :v_pNbr; And SQL Developer will prompt for the value of the variable. PL/SQL code is executed on the database server and it does not ...

  15. PL/SQL Variables and Constants

    You can assign a value to a variable in the following ways. With the assignment operator (:=). By selecting or fetching values into it. By passing the variable as an OUT or IN OUT parameter to a subprogram (procedure or function) and then assigning the value inside the subprogram.

  16. Assign return value to Unix Variable

    The answer shown above works fine if you are trying to retrieve a single value from sqlplus. If you have a sqlplus script that returns multiple columns, you could read them into shell variables like this: sqlplus / @myscript.sql | read var1 var2 var3. This will read 3 columns into var1, var2, and var3.

  17. Assign function result in array variable then display in table

    Assign function result in array variable then display in table. Received Response 22. ... Summary: I am using js function to split the comma-separated string values(O278219,PRW01022N-0890-SAFSA,Q1085,L10397257,MM20C24013,N38756)into array how would i assign the result into array result could be dynamic length either 6,5..

  18. How to store selection result in to variable in Oracle procedure

    DECLARE CURSOR v_employeeRecords IS SELECT * FROM Employee WHERE Salary > 10; v_employeeRecord employee%ROWTYPE; BEGIN FOR v_employeeRecord IN v_employeeRecords LOOP /* Do something with v_employeeRecord */ END LOOP; END; Or, you can create a TABLE variable:

  19. Application of a Variable Weight Time Function Combined Model in

    To attain precise forecasts of surface displacements and deformations in goaf areas (a void or cavity that remains underground after the extraction of mineral resources) following coal extraction, this study based on the limitations of individual time function models, conducted a thorough analysis of how the parameters of the model impact subsidence curves. Parameter estimation was conducted ...

  20. Add a Call Variable Action

    See Create a Type From Code . To add a Call Variable action to an action chain: Open the Actions editor for the application. Create an action chain, or open an existing action chain to add the action in the editor. Drag Call Variable from the Actions palette into the action chain. You can drag the action onto the Add icon ( ) in the action ...

  21. How to Select a Variable As Query Result in Oracle PLSQL

    How to Select a Variable As Query Result in Oracle PLSQL Ask Question Asked 4 years, 7 months ago Modified 4 years, 7 months ago Viewed 2k times 0 I've looked high and low but have not been able to figure this out. I have this function defined in Oracle: FUNCTION MY_FUNCTION ( INPUTVAR1 IN OUT NUMBER, INPUTVAR2 VARCHAR2 ) RETURN NUMBER;