This section explains each feature of DBMS_SQL.
BIND_VARIABLE sets values in the host variable within the SQL statement.
Specify the cursor number to be processed.
Specify the name of the host variable within the SQL statement using a string for the host variable name.
Specify the value set in the host variable. The data type of the host variable is the same as that of the value expression - it is implicitly converted in accordance with its position within the SQL statement. Refer to "A.3 Implicit Data Type Conversions" for information on implicit conversions.
If the value is a character type, the string length is the number of characters. If the string length is not specified, the size is the total length of the string.
It is necessary to place a colon at the beginning of the host variable in SQL statements to identify the host variable. The colon does not have to be added to the host variable names specified at BIND_VARIABLE. The following shows examples of host variable names specified with SQL statements and host variable names specified with BIND_VARIABLE:
PERFORM DBMS_SQL.PARSE(cursor, 'SELECT emp_name FROM emp WHERE sal > :x', 1);
In this example, BIND_VARIABLE will be as follows:
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':x', 3500);
Or,
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, 'x', 3500);
The length of the host variable name can be up to 30 bytes (excluding colons).
If the data type of the set value is string, specify the effective size of the column value as the fourth argument.
Example
If the data type of the value to be set is not a string:
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':NO', 1);
If the data type of the value to be set is a string:
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':NAME', h_memid, 5);
CLOSE_CURSOR closes the cursor.
Specify the cursor number to be processed.
The value returned is a NULL value.
Example
cursor := DBMS_SQL.CLOSE_CURSOR(cursor);
COLUMN_VALUE retrieves the value of the column in the select list extracted with FETCH_ROWS.
Specify the cursor number to be processed.
Specify the position of the column of the select list in the SELECT statement. The position of the first column is 1.
Specify the destination variable name.
Use a SELECT statement to obtain the values of the value, column_error, and actual_length columns.
The value column returns the value of the column specified at the column position. The data type of the variable name must match that of the column. If the data type of the column in the SELECT statement specified in PARSE is not compatible with DBMS_SQL, use CAST to convert to a compatible data type.
The data type of the column_error column is NUMERIC. If the column value could not be set correctly in the value column, a value other than 0 will be returned:
22001: The extracted string has been truncated
22002: The extracted value contains a NULL value
The data type of the actual_length column is INTEGER. If the extracted value is a character type, the number of characters will be returned (if the value was truncated, the number of characters prior to the truncation will be returned), otherwise, the number of bytes will be returned.
Example
When retrieving the value of the column, the error code, and the actual length of the column value:
SELECT value, column_error, actual_length INTO v_memid, v_col_err, v_act_len FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_memid);
When retrieving just the value of the column:
SELECT value INTO v_memid FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_memid);
DEFINE_COLUMN defines the column from which values are extracted and the storage destination.
Specify the cursor number to be processed.
Specify the position of the column in the select list in the SELECT statement. The position of the first column is 1.
Specify the destination variable name. The data type should be match with the data type of the column from which the value is to be extracted. If the data type of the column in the SELECT statement specified in PARSE is not compatible with DBMS_SQL, use CAST to convert to a compatible data type.
Specify the maximum number of characters of character type column values.
If the data type of the column value is string, specify the effective size of the column value as the fourth argument.
Example
When the data type of the column value is not a string:
PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 1, v_memid);
When the data type of the column value is a string:
PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 1, v_memid, 10);
EXECUTE executes SQL statements.
Specify the cursor number to be processed.
The return value is an INTEGER type, is valid only with INSERT statement, UPDATE statement, and DELETE statement, and is the number of rows processed. Anything else is invalid.
Example
ret := DBMS_SQL.EXECUTE(cursor);
FETCH_ROWS positions at the next row and extracts values from the row.
Specify the cursor number to be processed.
The return value is an INTEGER type and is the number of rows extracted. 0 is returned if all are extracted.
The extracted information is retrieved with COLUMN_VALUE.
Example
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor) = 0 THEN
EXIT;
END IF;
...
END LOOP;OPEN_CURSOR opens a new cursor.
The parameter is used for compatibility with Oracle databases only, and is ignored by Fujitsu Enterprise Postgres. An INTEGER type can be specified, but it will be ignored. If migrating from an Oracle database, specify 1.
Close unnecessary cursors by executing CLOSE_CURSOR.
The return value is an INTEGER type and is the cursor number.
Example
cursor := DBMS_SQL.OPEN_CURSOR();
PARSE analyzes dynamic SQL statements.
Specify the cursor number to be processed.
Specify the SQL statement to be parsed.
Parameters 1, 2, 3, and 4 are used for compatibility with Oracle databases only, and are ignored by Fujitsu Enterprise Postgres. If you are specifying values anyway, specify the following:
- Parameter 1 is an INTEGER type. Specify 1.
- Parameters 2 and 3 are TEXT types. Specify NULL.
- Parameter 4 is a BOOLEAN type. Specify TRUE.
If migrating from an Oracle database, the specified values for parameters 2, 3, and 4 do not need to be changed.
Add a colon to the beginning of host variables in SQL statements.
The DDL statement is executed when PARSE is issued. EXECUTE is not required for the DDL statement.
If PARSE is called again for opened cursors, the content in the data regions within the cursors is reset, and the SQL statement is parsed anew.
Example
PERFORM DBMS_SQL.PARSE(cursor, 'SELECT memid, memnm FROM member WHERE memid = :NO', 1);