1.5.5 Setting a value for an indicator variable

Organization of this subsection
(1) For receiving data (INTO clause of the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement)
(2) Passing data (not applicable to the INTO clause of the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement)

(1) For receiving data (INTO clause of the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement)

When the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement is executed, the values shown in the following table are assigned to the indicator variable that is specified in the INTO clause of the statement. If the null value is returned to an embedded variable, the value of the embedded variable cannot be guaranteed; in this case, an error results unless an indicator variable also is specified.

Table 1-15 Indicator variable values returned by the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement (other than a repetition column, or in the case of the values of elements in a repetition column)

Indicator variable valueValue received by associated embedded variable
NegativeNULL value
If the value of the indicator variable is -2, this is the null value that is assigned when the element specified by a subscript in a repetition column does not exist. If the value of the indicator variable is -4, this is the null value that is assigned by the overflow error suppression option in an arithmetic operation, set function operation, window function operation, or scalar function that is subject to overflow error suppression during SQL execution. Because the embedded variable that received the null value and the specific operation performed are associated with each other, the operation in which the overflow occurred can be determined. In the case of remote database access, the value -1 is assigned to the indicator variable. For the scalar functions that are subject to overflow error suppression, see 2.18 Operational results with overflow error suppression specified.
0NOT NULL value
PositiveNOT NULL value
The received data is either character data or large object data and represents a value that has been truncated on the right because the embedded variable was too short. The indicator variable contains the length existing before this truncation. In the case of a remote database access, the value returned to the indicator variable depends on the data type involved. For details, see Table 1-16 Relationship between the indicator variable value and the value received by the embedded variable.

Table 1-16 Relationship between the indicator variable value and the value received by the embedded variable

Indicator variable value returned by serverValue received by associated embedded variable
BLOB type or BINARY typeAny other data type
1-327671-327671-32767
32768-2147483647​32768-2147483647​1

Table 1-17 Indicator variable values returned by the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement (data on an entire repetition column)

Indicator variable valueValue received by associated embedded variable
NegativeNULL value (number of elements is 0)
0NOT NULL value (number of elements is at least 1)
PositiveNOT NULL value (number of elements is at least 1)
If the number of elements in the embedded variable area is insufficient, the value indicates that the remaining elements have been truncated. In this case, the number of elements before truncation is set in the indicator variable.

Figure 1-5 shows the structures of the indicator variables and embedded variables that receive repetition column data. Figures 1-6 and 1-7 show examples of these structures.

Figure 1-5 Structures of indicator variables and embedded variables that receive repetition column data

[Figure]

Figure 1-6 Examples of receiving repetition column data (1 of 2)

[Figure]

Figure 1-7 Examples of receiving repetition column data (2 of 2)

[Figure]

(2) Passing data (not applicable to the INTO clause of the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement)

When executing an SQL statement other than the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement, in the UAP specify one of the values shown in the following table in the indicator variable before the SQL statement is executed. Depending on the value of the indicator variable, the value of the corresponding embedded variable must be used during execution of the SQL.

Table 1-18 Indicator variable value to be set before execution of SQL (other than a repetition column and element values of a repetition column)

Indicator variable valueValue passed to SQL by associated embedded variable
NegativeNULL value
Any value contained in the embedded variable is ignored.
Non- negativeNOT NULL value.
This is the value contained in the embedded variable.

Table 1-19 Indicator variable value to be set before execution of SQL (information on the entire repetition column)

Indicator variable valueValue passed by SQL to associated embedded variable
NegativeNULL value (number of elements is 0)
Any value contained in the embedded variable is ignored.
Non- negativeValue of the element indicated by the elements count. 0 cannot be specified as the elements count.

Figure 1-8 shows the structures of indicator variables and embedded variables to which data from a repetition column is passed. Figures 1-9 and 1-10 show examples of those structures.

Figure 1-8 Structures of indicator variables and embedded variables to which data from a repetition column is passed

[Figure]

Figure 1-9 Example of data passed from repetition column (1 of 2)

[Figure]

Figure 1-10 Example of data passed from repetition column (2 of 2)

[Figure]