Oracle database
CREATE PROCEDURE search_test(h_where CLOB) AS
str_sql CLOB;
v_cnt INTEGER;
v_array DBMS_SQL.VARCHAR2A;
v_cur INTEGER;
v_smpid INTEGER;
v_smpnm VARCHAR2(20);
v_addbuff VARCHAR2(20);
v_smpage INTEGER;
errcd INTEGER;
length INTEGER;
ret INTEGER;
BEGIN
str_sql := 'SELECT smpid, smpnm FROM smp_tbl WHERE ' || h_where || ' ORDER BY smpid';
v_smpid := 0;
v_smpnm := '';
v_smpage := 0;
v_cur := DBMS_SQL.OPEN_CURSOR; ...(1)
v_cnt :=
CEIL(DBMS_LOB.GETLENGTH(str_sql)/1000);
FOR idx IN 1 .. v_cnt LOOP
v_array(idx) :=
DBMS_LOB.SUBSTR(str_sql,
1000,
(idx-1)*1000+1);
END LOOP;
DBMS_SQL.PARSE(v_cur, v_array, 1, v_cnt, FALSE, DBMS_SQL.NATIVE); ...(2)
DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid); ...(3)
DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_smpnm, 10);
ret := DBMS_SQL.EXECUTE(v_cur);
LOOP
v_addbuff := '';
IF DBMS_SQL.FETCH_ROWS(v_cur) = 0 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_smpid, errcd, length); ...(4)
IF errcd = 1405 THEN ...(4)
DBMS_OUTPUT.PUT_LINE('smpid = (NULL)');
ELSE
DBMS_OUTPUT.PUT_LINE('smpid = ' || v_smpid);
END IF;
DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_smpnm, errcd, length);
IF errcd = 1406 THEN ...(4)
v_addbuff := '... [len=' || length || ']';
END IF;
IF errcd = 1405 THEN
DBMS_OUTPUT.PUT_LINE('v_smpnm = (NULL)');
ELSE
DBMS_OUTPUT.PUT_LINE('v_smpnm = ' || v_smpnm || v_addbuff );
END IF;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
DBMS_OUTPUT.NEW_LINE;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cur); ...(5)
RETURN;
END;
/
Set serveroutput on
call search_test('smpid < 100');Fujitsu Enterprise Postgres
CREATE FUNCTION search_test(h_where text) RETURNS void AS $$
DECLARE
str_sql text;
v_cur INTEGER;
v_smpid INTEGER;
v_smpnm VARCHAR(20);
v_smpnm_max_length INTEGER;
v_addbuff VARCHAR(20);
v_smpage INTEGER;
errcd INTEGER;
length INTEGER;
ret INTEGER;
BEGIN
PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
str_sql := 'SELECT smpid, smpnm FROM smp_tbl WHERE ' || h_where || ' ORDER BY smpid';
v_smpid := 0;
v_smpnm := '';
v_smpage := 0;
v_cur := DBMS_SQL.OPEN_CURSOR(); ...(1)
CALL DBMS_SQL.PARSE(v_cur, str_sql); ...(2)
CALL DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid); ...(3)
CALL DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_smpnm);
v_smpnm_max_length := 10;
ret := DBMS_SQL.EXECUTE(v_cur);
LOOP
v_addbuff := '';
IF DBMS_SQL.FETCH_ROWS(v_cur) = 0 THEN
EXIT;
END IF;
PERFORM DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
CALL DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_smpid); ... (4)
errcd := 0; ・・・(4)
IF v_smpid IS NULL THEN
errcd := 1405;
END IF;
IF errcd = 1405 THEN
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = (NULL)');
ELSE
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = ' || v_smpid);
END IF;
CALL DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_smpnm); ... (4)
errcd := 0;
length := 0;
IF v_smpnm IS NULL THEN
errcd := 1405;
length := 0;
ELSE;
length := LENGTH(v_smpnm);
IF length > v_smpnm_max_length THEN
errcd := 1406;
length := v_smpnm_max_length;
v_smpnm := LEFT(v_smpnm, v_smpnm_max_length);
END IF;
END IF;
IF errcd = 1406 THEN
v_addbuff := '... [len=' || length || ']';
END IF;
IF errcd = 1405 THEN
PERFORM DBMS_OUTPUT.PUT_LINE('v_smpnm = (NULL)');
ELSE
PERFORM DBMS_OUTPUT.PUT_LINE('v_smpnm = ' || v_smpnm || v_addbuff );
END IF;
PERFORM DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
PERFORM DBMS_OUTPUT.NEW_LINE();
END LOOP;
CALL DBMS_SQL.CLOSE_CURSOR();・・・(5)
v_cur := NULL;
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT search_test('smpid < 100');Same as NEW_LINE in the DBMS_OUTPUT package. Refer to NEW_LINE in the DBMS_OUTPUT package for information on specification differences and conversion procedures associated with specification differences.
DBMS_SQL.PARSE(firstArg, secondArg, thirdArg, fourthArg, fifthArg)
SQL statements can be specified with string table types (VARCHAR2A type, VARCHAR2S type). Specify this for secondArg.
DBMS_SQL.NATIVE, DBMS_SQL.V6, DBMS_SQL.V7 can be specified for processing SQL statements.
SQL statements cannot be specified with string table types.
DBMS_SQL.NATIVE, DBMS_SQL.V6, DBMS_SQL.V7 cannot be specified for processing SQL statements.
Convert using the following procedure:
Locate the places where the keyword "DBMS_SQL.PARSE" is used in the stored procedure.
Check the data type of the SQL statement specified for secondArg (v_array in the example).
If the data type is either DBMS_SQL.VARCHAR2A type or DBMS_SQL.VARCHAR2S type, then it is a table type specification. Execute step 3 and continue the conversion process.
If the data type is neither DBMS_SQL.VARCHAR2A type nor DBMS_SQL.VARCHAR2S type, then it is a string specification. Execute step 7 and continue the conversion process.
Check the SQL statement (str_sql in the example) before it was divided into DBMS_SQL.VARCHAR2A type and DBMS_SQL.VARCHAR2S type.
Delete the sequence of the processes (processes near FOR idx in the example) where SQL is divided into DBMS_SQL.VARCHAR2A type and DBMS_SQL.VARCHAR2S type.
Replace secondArg with the SQL statement (str_sql in the example) before it is divided, that was checked in step 2.
Delete thirdArg, fourthArg, and fifthArg (v_cnt, FALSE, DBMS_SQL.NATIVE, in the example).
DBMS_SQL.DEFINE_COLUMN(firstArg, secondArg, thirdArg, fourthArg)
fourthArg specifies the maximum length of the character string data to be returned. If specified, the character string will be truncated to the specified length when the character string data is subsequently retrieved with DBMS_SQL.COLUMN_VALUE.
You can also check whether truncation has occurred by checking the error code of DBMS_SQL.COLUMN_VALUE.
fourthArg specifies the maximum length of the character string data to be returned. If specified, the character string will be truncated to the specified length when the character string data is subsequently retrieved with DBMS_SQL.COLUMN_VALUE.
However, whether truncation has occurred cannot be determined at the time DBMS_SQL.COLUMN_VALUE is executed.
Convert using the following procedure:
Locate the places where the keyword "DBMS_SQL.DEFINE_COLUMN" is used in the stored procedure.
Check whether a numeric value is specified for the fourthArg.
If a numeric value is specified for the fourth argument, check whether the error code 1406 is evaluated when DBMS_SQL.COLUMN_VALUE is subsequently executed. If it is, take the following measures to ensure that the same evaluation can be performed.
Add an INTEGER variable declaration to store the fourthArg of DBMS_SQL.DEFINE_COLUMN.
Set the specified value of the fourthArg of DBMS_SQL.COLUMN_VALUE to the above variable, and delete the fourthArg of DBMS_SQL.DEFINE_COLUMN.
After the subsequent execution of DBMS_SQL.COLUMN_VALUE, use the above INTEGER variable to truncate the string and evaluate the error code 1406. For details, refer to "(4) COLUMN_VALUE".
DBMS_SQL.COLUMN_VALUE(firstArg, secondArg, thirdArg, fourthArg, fifthArg)
The following error codes are returned for the fourthArg.
1405: fetched column value is NULL
1406: fetched column value was truncated
The fourthArg and fifthArg cannot be specified.
Therefore, it is not possible to determine the above error code using the fourthArg, nor is it possible to determine the length of the retrieved value that should be set in the fifthArg.
Obtained values are received with variables specified for arguments.
The retrieved value will be received in the variable specified in the argument.
However, because the fourthArg and fifthArg cannot be specified, the variables that could not be specified will be reset to appropriate values based on the processing results, allowing the transition to take place without changing the existing processing.
Convert using the following procedure:
Locate the places where the keyword "DBMS_SQL.COLUMN_VALUE" is used in the stored procedure.
When determining whether the value of the fourthArg of COLUMN_VALUE is 1405, a process is performed to check whether the obtained data value is NULL.
The following support for the fourthArg will allow the existing process to be executed.
Add a process to check whether the obtained data value is NULL immediately before the process to determine the fourthArg. If the value is NULL, set 1405 to the variable of the fourthArg.
When determining whether the value of the fourthArg of COLUMN_VALUE is 1406, a process is performed to check whether the acquired data value has been truncated.
Truncation of the acquired data value occurs when the maximum length of the returned character string data is specified in the fourthArg of DBMS_SQL.DEFINE_COLUMN, so it is necessary to deal with not only COLUMN_VALUE but also DBMS_SQL.DEFINE_COLUMN at the same time.
Handling DBMS_SQL.DEFINE_COLUMN
The maximum length of the character string data must be saved. For details, rrefer to "(3) DEFINE_COLUMN".
Check whether the returned characters should be truncated
The above DBMS_SQL.DEFINE_COLUMN handling returns the character string without truncation. Therefore, before checking whether the fourthArg is 1406, the length of the returned value (obtained with the LENGTH function) is compared with the maximum length of the character string data saved from DBMS_SQL.DEFINE_COLUMN to check whether the returned characters should have been truncated.
If the length of the returned value is greater, set the fourthArg to 1406 so that the subsequent existing judgment process will be performed. At this time, it is also necessary to use the LEFT function to truncate the length to the maximum length of the string data.
If the fifthArg of COLUMN_VALUE is specified, you must set the length of the returned string.
After executing COLUMN_VALUE, use the LENGTH function to obtain the length of the returned value and assign it to the variable of the fifthArg.
DBMS_SQL.CLOSE_CURSOR(firstArg)
After closing, the cursor specified in firstArg becomes NULL.
Even if you close it, the cursor specified in the argument does not become NULL. Set the cursor to NULL again.
Convert using the following procedure:
Locate the places where the keyword "DBMS_SQL.CLOSE_CURSOR" is used in the stored procedure.
Ensure that the cursor is null-valued after calling DBMS_SQL.CLOSE_CURSOR.