CREATE VARIABLE statement

The CREATE VARIABLE statement defines a session global variable.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the variable does not exist
  • CREATEIN privilege on the schema, if the schema name of the variable refers to an existing schema
  • SCHEMAADM authority on the schema, if the schema name of the variable refers to an existing schema
  • DBADM authority
and any privileges that are necessary to execute the default expression.

To execute this statement with a cursor-value-constructor that uses a select-statement, the privileges held by the authorization ID of the statement must include the privileges necessary to execute the select-statement. See the Authorization section in SQL queries.

Group privileges are not considered when checking authorization for objects referenced in the statement

To replace an existing variable, the authorization ID of the statement must be the owner of the existing variable (SQLSTATE 42501).

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACE VARIABLEvariable-namedata-type1 DEFAULT NULLCONSTANT NULLDEFAULTCONSTANTconstantspecial-registerglobal-variable(cursor-value-constructor)(expression)1
data-type1
Read syntax diagramSkip visual syntax diagrambuilt-in-typeanchored-variable-data-typearray-type-namecursor-type-namedistinct-type-nameREF( type-name)row-type-name
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)( integer,0, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integerOCTETSCODEUNITS32)VARCHARCHARACTERCHARVARYING( integerOCTETSCODEUNITS32)FOR BIT DATA2CLOBCHARACTERCHARLARGE OBJECT(1M)( integerKMGOCTETSCODEUNITS32)GRAPHIC(1)( integerCODEUNITS16CODEUNITS32)VARGRAPHIC( integerCODEUNITS16CODEUNITS32)DBCLOB(1M)( integerKMGCODEUNITS16CODEUNITS32)BINARY(1)( integer)VARBINARYBINARY VARYING(integer)BLOBBINARY LARGE OBJECT(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)XML3BOOLEANCURSOR
anchored-variable-data-type
Read syntax diagramSkip visual syntax diagramANCHORDATA TYPE TO variable-name2table-name.column-nameROWOFtable-nameview-namecursor-variable-name
cursor-value-constructor
Read syntax diagramSkip visual syntax diagram ASENSITIVEINSENSITIVE CURSOR (,parameter-declaration) holdabilityFOR select-statement
parameter-declaration
Read syntax diagramSkip visual syntax diagramparameter-namedata-type2
data-type2
Read syntax diagramSkip visual syntax diagrambuilt-in-typeanchored-parameter-data-typedistinct-type-name
anchored-parameter-data-type
Read syntax diagramSkip visual syntax diagramANCHORDATA TYPE TO variable-nametable-name.column-name
holdability
Read syntax diagramSkip visual syntax diagramWITHOUT HOLDWITH HOLD
Notes:
  • 1 If data-type1 specifies a CURSOR built-in type or cursor-type-name, only NULL or cursor-value-constructor can be specified. Only DEFAULT NULL can be explicitly specified for array-type-name or row-type-name.
  • 2 The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
  • 3 For version 10.1, you can use the XML data type only as a parameter data type in a cursor value constructor. For version 10.1 Fix Pack 1 or later fix pack releases, you can also use the XML data type to create global variables.

Description

OR REPLACE
Specifies to replace the definition for the variable if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the variable are not affected. This option is ignored if a definition for the variable does not exist at the current server. This option can be specified only by the owner of the object.
variable-name
Names the global variable. The name, including an implicit or explicit qualifier, must not identify a global variable that already exists at the current server (SQLSTATE 42710). If a qualifier is not specified, the current schema is implicitly assigned. If the global variable name is explicitly qualified with a schema name, the schema name must not begin with the characters SYS (SQLSTATE 42939).
data-type1
Specifies the data type of the global variable. A structured type cannot be specified (SQLSTATE 42611).
built-in-type
Specifies a built-in data type. BOOLEAN and CURSOR cannot be specified for a table. For version 10.1, an XML data type cannot be specified (SQLSTATE 42611). The XML data type support starts in version 10.1 Fix Pack 1. For a more complete description of each built-in data type, see CREATE TABLE.

FOR BIT DATA can be specified as part of character string data types.

BOOLEAN
For a Boolean.
CURSOR
For a reference to an underlying cursor.
anchored-variable-data-type
Identifies another object used to determine the data type of the global variable. The data type of the anchor object has the same limitations that apply to specifying the data type directly, or in the case of a row, to creating a row type.
ANCHOR DATA TYPE TO
Indicates an anchored data type is used to specify the data type.
variable-name2
Identifies a global variable. The data type of the referenced variable is used as the data type for the global variable.
table-name.column-name
Identifies a column name of an existing table or view. The data type of the column is used as the data type for the global variable.
ROW OF table-name or view-name
Specifies that the global variable is a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data type of the global variable is an unnamed row type.
ROW OF cursor-variable-name
Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following elements (SQLSTATE 428HS):
  • A global variable with a strongly typed cursor data type
  • A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.
If the cursor type of the cursor variable is not strongly-typed using a named row type, the data type of the global variable is an unnamed row type.
array-type-name
Specifies the name of a user-defined array type. If array-type-name is specified without a schema name, the array type is resolved by searching the schemas in the SQL path.
cursor-type-name
Specifies the name of a cursor type. If cursor-type-name is specified without a schema name, the cursor type is resolved by searching the schemas in the SQL path.
distinct-type-name
Specifies the name of a distinct type. The length, precision, and scale of the declared variable are, respectively, the length, precision, and scale of the source type of the distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.
REF (type-name)
Specifies a reference type. If a type name is specified without a schema name, the type-name is resolved by searching the schemas in the SQL path.
row-type-name
Specifies the name of a user-defined row type. The fields of the variable are the fields of the row type. If row-type-name is specified without a schema name, the row type is resolved by searching the schemas in the SQL path.
DEFAULT or CONSTANT
Specifies a value for the global variable when it is first referenced. The DEFAULT or CONSTANT clause value is determined on this first reference. If neither is specified, the default for the global variable is the null value. Only DEFAULT NULL can be explicitly specified if array-type-name or row-type-name is specified.
DEFAULT
Defines the default for the global variable. The default value must be assignment-compatible with the data type of the variable.
CONSTANT
Specifies that the global variable has a fixed value that cannot be changed. A global variable that is defined using CONSTANT cannot be used as the target of any assignment operation. The fixed value must be assignment-compatible with the data type of the variable.
NULL
Specifies NULL as the default for the global variable. If row-type-name is specified, the value for the global variable is a row where each field has the null value.
constant
Specifies the value of a constant as the default for the global variable. If data-type1 specifies a CURSOR built-in type or cursor-type-name, constant cannot be specified (SQLSTATE 42601).
special-register
Specifies the value of a special register as the default for the global variable. If data-type1 specifies a CURSOR built-in type or cursor-type-name, special-register cannot be specified (SQLSTATE 42601).
global-variable
Specifies the value of a global variable as the default for the global variable. If data-type1 specifies a CURSOR built-in type or cursor-type-name, global-variable cannot be specified (SQLSTATE 42601).
cursor-value-constructor
A cursor-value-constructor specifies the select-statement that is associated with the global variable. The assignment of a cursor-value-constructor to a cursor variable defines the underlying cursor of that cursor variable.
ASENSITIVE or INSENSITIVE
Specifies whether the cursor is asensitive or insensitive to changes. See DECLARE CURSOR for more information. The default is ASENSITIVE.
ASENSITIVE
Specifies that the cursor should be as sensitive as possible to insert, update, or delete operations made to the rows underlying the result table, depending on how the select-statement is optimized. This option is the default.
INSENSITIVE
Specifies that the cursor does not have sensitivity to insert, update, or delete operations that are made to the rows underlying the result table. If INSENSITIVE is specified, the cursor is read-only and the result table is materialized when the cursor is opened. As a result, the size of the result table, the order of the rows, and the values for each row do not change after the cursor is opened. The SELECT statement cannot contain a FOR UPDATE clause, and the cursor cannot be used for positioned updates or deletes.
parameter-declaration, )
Specifies the input parameters of the cursor, including the name and the data type of each parameter.
parameter-name
Names the parameter for use as an SQL variable within select-statement. The name cannot be the same as any other parameter name for the cursor. Names should also be chosen to avoid any column names that could be used in select-statement, since column names are resolved before parameter names.
data-type2
Specifies the data type of the cursor parameter used within select-statement.
built-in-type
Specifies a built-in data type. For a more complete description of each built-in data type, see CREATE TABLE. The BOOLEAN and CURSOR built-in types cannot be specified (SQLSTATE 429BB).
anchored-parameter-data-type
Identifies another object used to determine the data type of the cursor parameter. The data type of the anchor object is bound by the same limitations that apply when specifying the data type directly.
ANCHOR DATA TYPE TO
Indicates an anchored data type is used to specify the data type.
variable-name
Identifies a global variable. The data type of the referenced variable is used as the data type for the cursor parameter.
table-name.column-name
Identifies a column name of an existing table or view. The data type of the column is used as the data type for the cursor parameter.
distinct-type-name
Specifies the name of a distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.
holdability
Specifies whether the cursor is prevented from being closed as a consequence of a commit operation. See DECLARE CURSOR for more information. The default is WITHOUT HOLD.
WITHOUT HOLD
Does not prevent the cursor from being closed as a consequence of a commit operation.
WITH HOLD
Maintains resources across multiple units of work. Prevents the cursor from being closed as a consequence of a commit operation.
select-statement
Specifies the SELECT statement of the cursor. See select-statement for more information.
statement-name
Specifies the prepared select-statement of the cursor. See PREPARE for an explanation of prepared statements. The target cursor variable must not have a data type that is a strongly-typed user-defined cursor type (SQLSTATE 428HU).
expression
Specifies the value of an expression as the default for the global variable. The expression can be any expression of the type described in Expressions. The expression must be assignment-compatible with the data type of the variable. The maximum size of the expression is 64K. The default expression must not modify SQL data (SQLSTATE 428FL) or perform external action (SQLSTATE 42845). If data-type1 specifies a CURSOR built-in type or cursor-type-name, expression cannot be specified (SQLSTATE 42601).

Rules

  • Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.

Notes

  • Session global variables have a session scope. This means that, although they are available to all sessions that are active on the database, their value is private for each session.
  • Contexts for array, Boolean, cursor, and row global variables: Global variables that are array variables, Boolean variables, or row variables can only be used in compound SQL (compiled) statements or SET variable statements. Global variables that are cursor variables can only be used in compound SQL (compiled) statements.
  • Create with errors: If an object referenced in the default expression does not exist or is marked invalid, or the definer temporarily doesn't have privileges to access the object, and if the database configuration parameter auto_reval is not set to DISABLED, then the variable will still be created successfully. The variable will be marked invalid and will be revalidated the next time it is invoked.
  • Scope of global variable values: The values for session global variables persist until they are updated in the current session, the global variable is dropped or altered, or the application session ends. The value is unaffected by COMMIT or ROLLBACK statements. The default value for a global variable can be not deterministic and dependent on when the default value is calculated for the global variable (for example, a reference to the time of day, or a reference to some data stored in a table).

    A technique commonly used, especially for performance, is for an application or product to manage a set of connections and route transactions to an arbitrary connection. In these situations, the non-default value of a global variable or the not deterministic initial default value for a global variable should only be relied on until the end of the transaction. Examples of where this type of situation can occur include applications that: use XA protocols, use connection pooling, use the connection concentrator, and use HADR to achieve failover.

  • Privileges to use a global variable: An attempt to read from or to write to a global variable created by this statement requires that the authorization ID attempting this action hold the appropriate privilege on the global variable. The definer of the variable is implicitly granted all privileges on the variable.
  • Setting of the default value: A created global variable is instantiated to its default value when it is first referenced within its given scope. Note that if a global variable is referenced in a statement, it is instantiated independently of the control flow for that statement.
  • Using a newly created session global variable: If a global variable is created within a session, it cannot be used by other sessions until the unit of work has committed. However, the new global variable can be used within the session that created the variable before the unit of work commits.

Examples

  • Example 1: Create a session global variable to indicate what printer to use for the session.
       CREATE VARIABLE MYSCHEMA.MYJOB_PRINTER VARCHAR(30)
         DEFAULT 'Default printer'
  • Example 2: Create a session global variable to indicate the department where an employee works.
       CREATE VARIABLE SCHEMA1.GV_DEPTNO INTEGER
         DEFAULT ((SELECT DEPTNO FROM HR.EMPLOYEES
           WHERE EMPUSER = SESSION_USER))
  • Example 3: Create a session global variable to indicate the security level of the current user.
       CREATE VARIABLE SCHEMA2.GV_SECURITY_LEVEL INTEGER
         DEFAULT (GET_SECURITY_LEVEL (SESSION_USER))
  • Example 4: Create a session global variable as a cursor on the STAFF table that returns the names of each employee for the specified job type. Order the results by the department number.
       CREATE VARIABLE STAFFJOBS CURSOR
         CONSTANT (CURSOR (WHICHJOB CHAR(5)) 
         FOR SELECT NAME, DEPT FROM STAFF WHERE JOB = WHICHJOB 
           ORDER BY DEPT)
  • Example 5: Create a global variable of the XML data type:
       CREATE VARIABLE MYSCHEMA.CUSTOMER_HISTORY_VAR XML