Parameters, routine parameters, and SQL variables are often used
in value expressions (for information on value expression, see
the Value_Expressions HELP topic.
A variable is an identifier that represents a value that can
change during the execution of a program. You can use SQL
variables in multistatement procedures.
A routine parameter is a variable associated with a parameter
of a routine that is used in a stored routine or an external
routine. A stored routine refers to both stored procedures and
stored functions defined using the CREATE MODULE statement. An
external routine refers to both external procedures and external
functions defined using the CREATE PROCEDURE and CREATE FUNCTION
statements.
When you use SQL variables in multistatement procedures or when
you use routine parameters, you do not use indicator variables.
See the Multistatement_Variables_and_Stored_Routines HELP topic
for more information about SQL variables in multistatement
procedures and stored routine parameters. See the External_
Routine_Parameters HELP topic for more information about external
routine parameters.
A parameter is an identifier declared in a host language program
that is associated with an SQL statement. A parameter represents
values that can change during the execution of a program.
Many SQL data manipulation clauses that do not accept general
value expressions require parameters. However, you cannot use
parameters in data definition language statements.
You can use parameters in the following places:
o Interactive SQL
In interactive SQL, you use the DECLARE Variable statement to
declare the parameter. For more information about declaring
parameters and variables, see DECLARE Variable.
o SQL module language
In programs that call SQL module procedures containing SQL
statements, references to host language variables by SQL
statements are indirect. The variable declared in the program
is specified as a parameter in a host language call statement
to a procedure in the SQL module. Parameters in such call
statements are called actual parameters.
In nonstored procedures, the SQL module procedure contains
parameter declarations that correspond to the actual
parameters in the calling program. Module parameters in those
declarations are called formal parameters. The SQL statement
in the module procedure uses the formal parameter name to
refer indirectly to the actual parameter named in the host
language call to the module procedure.
o Precompiled SQL
In precompiled programs, SQL statements embedded in the
program refer directly to the host language variable using it
in the statement. The SQL precompiler supports only a subset
of the declaration syntax for host languages. See the Oracle
Rdb SQL Reference Manual for more information. You can only
use parameter names that conform to the rules of the host
language.
o Dynamic SQL
In dynamic SQL, dynamically executed SQL statements refer to
parameters with parameter markers denoted by a question mark
(?) in the statement string of PREPARE statements.
SQL statements use parameters for the following purposes:
o SQL retrieves data from the database and places it in
parameters for use by a program.
o Parameters contain data generated by a program that SQL uses
to update the database.
o Data manipulation statements can specify parameters in value
expressions.
o Special-purpose parameters called indicator parameters
indicate whether or not the value stored in a corresponding
main parameter is null. (Indicator parameters are not used in
stored routines.)
o SQL puts information about the success or failure of SQL
statements in a parameter called SQLCODE that is either
declared explicitly or as part of the SQL Communications Area
(SQLCA) or in the SQLSTATE status parameter (ANSI/ISO SQL
standard).
See SQLCA for more information on SQLCODE and SQLSTATE.
o SQL and programs use a collection of parameters called the
SQL Descriptor Areas (SQLDA and SQLDA2) to communicate
information about dynamic SQL statements. See SQLDA for more
information.
SQL statements cannot use parameters to refer to columns, tables,
or views. For instance, if BADVAR is a host language variable
that contains the name of a table in the database, the following
statement is invalid:
EXEC SQL SELECT FIRST_NAME INTO :GOODVAR FROM :BADVAR END-EXEC
When you use the precompiler, module language, or dynamic SQL,
display operations should use CAST or EXTRACT with CHAR host
variables to convert date-time data from binary format when
passing data to and from the database. For example:
EXEC SQL SELECT CAST(TBL_INT_H3 AS CHAR(4))
INTO :string_var3
FROM ALL_DATE_TABLE;
For more information about the CAST and EXTRACT functions, see
the Built_In_Functions HELP topic.
1 – Data and Indicator Parameters
A data parameter contains the value that an SQL statement stores
in, retrieves from, or compares to a column in the database. An
indicator parameter specifies whether or not its associated data
parameter was assigned a null value. You specify an indicator
parameter after the data parameter. As for data parameters, the
notation for referring to indicator parameters depends on the
environment in which an SQL statement is issued.
(B)0[m[1mparameter = [m
[1m [m
[1mqq> : <data-parameter> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mwqqqqqqqqqqqqqqqwqqqqqqqqqk x [m
[1m mq> INDICATOR qqj x x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mq> : <indicator-parameter> qj [m
[1m [m
o If you set the dialect to SQL99 or another dialect that
enforces the use of parameters or if you use a clause, such
as PARAMETER COLONS, that enforces the use of parameters, all
parameter names must begin with a colon. This rule applies
to declarations and references of module language procedure
parameters. If you do not use one of these dialects or
clauses, no parameter name can begin with a colon. For more
information, see SET_DIALECT.
The current default behavior is no colons are used. However,
this default is deprecated syntax. In the future, colons
will be the default because it allows processing of ANSI/ISO
standard modules.
o In SQL statements to be dynamically executed, you refer
to the data parameters and indicator parameters with a
single parameter marker (?). SQL gets information about the
parameters in EXECUTE or OPEN statements. These statements
either provide an explicit list of data parameters and
indicator parameters (using the notation for precompiled SQL
or SQL modules as appropriate) or refer to the SQLDA that
has fields that provide information about data parameters
(SQLDATA) and indicator parameters (SQLIND).
2 – Host Structures and Indicator Arrays
Host structures are host language parameters that correspond
to group constructs or records in the languages that support
such constructs. Use a host structure to refer to a list of host
language variables with a single name. Once you define a host
structure, you can refer to it in an embedded SQL statement or
in an SQL module language procedure instead of listing the host
language variables that comprise it.
Parameters can be qualified by group fields to any depth.
The format of a qualified reference to a parameter in a group
construct is:
(B)0[m[1mqualified-parameter = [m
[1m [m
[1mqq> : qqwqwqqqqqqqqqqqqqqqqqqqqqqqqwqwqq> parameter-name qqqq> [m
[1m x mqq> group-field-name. qqj x [m
[1m mqqqqqqqqqqqq <qqqqqqqqqqqqqqj [m
[1m [m
In addition, you can declare an indicator parameter for a
host structure by defining a one-dimensional array of signed
longword integers. This array provides indicator parameters
for fields in the host structure and is called an indicator
array. (Indicator arrays are also called indicator structures or
indicator vectors.) Just as you append an indicator parameter to
a data parameter, you can append the name of an indicator array
to a host structure that represents several data parameters.
Indicator arrays are the only way to specify indicator parameters
for host structures.
You can refer to a host structure anywhere that SQL allows a list
of parameters:
o VALUES clause of an INSERT statement
o Select lists
o IN predicates
o INTO clause of FETCH or singleton SELECT statements
o USING clause of OPEN or EXECUTE statements
You cannot use host structures in a stored routine or a
multistatement procedure.
You can also refer to a single parameter in a host structure. In
FORTRAN, C, Pascal, and Ada, you must qualify the parameter name
with all preceding group field names. In COBOL and PL/I, you need
to qualify the parameter with group field names only if the name
is ambiguous without such qualification.
3 – Multistatement Variables and Stored Routines
Multistatement procedure variables and stored routine parameters
are often used in value expressions A variable is an identifier
that represents a value that can change during the execution of
a program. You use SQL variables in multistatement procedures.
A stored routine parameter is a variable associated with the
parameters of a stored routine that you use in a stored procedure
or stored function. A stored routine refers to both stored
procedures and stored functions defined using the CREATE MODULE
statement.
(B)0[m[1mvariable = [m
[1m [m
[1mqqwq> : <variable-name> qqqqqqqqqqqqqqwq> [m
[1m mq> : <stored-procedure-parameter> qj [m
[1m [m
Variables in multistatement procedures and stored routine
parameters follow the rules, such as case-sensitivity rules,
associated with the encompassing module. That means:
o In embedded SQL, the variables follow the rules for the host
language in which the program is written.
o In SQL module language programs, the variables follow the
rules for the SQL interface.
o In stored routines, the variables follow the rules for the SQL
interface.
Unlike data parameters, variables and stored routine parameters
allow null values. Because of this, you cannot use indicator
parameters with variables and stored routine parameters.
For more information about stored routine parameters, see CREATE
MODULE.
4 – External Routine Parameters
An external routine parameter is a 3GL declaration that
corresponds to an actual parameter in the calling program. These
declarations are called formal parameters. 3GL or SQL statements
in the external routine use the formal parameter name to refer
indirectly to the calling programs actual parameters.
External routine parameters cannot represent null values.