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);
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); ...(3)
IF errcd = 1405 THEN ...(3)
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
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); ...(4)
RETURN;
END;
/
Set serveroutput on
call search_test('smpid < 100');Symfoware Server
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_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)
PERFORM DBMS_SQL.PARSE(v_cur, str_sql, 1); ...(2)
PERFORM DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid);
PERFORM 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;
PERFORM DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
SELECT value,column_error,actual_length
INTO v_smpid, errcd, length
FROM DBMS_SQL.COLUMN_VALUE(v_cur,
1,
v_smpid); ...(3)
IF errcd = 22002 THEN ...(3)
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = (NULL)');
ELSE
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = ' || v_smpid);
END IF;
SELECT value,column_error,actual_length INTO v_smpnm, errcd, length FROM DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_smpnm);
IF errcd = 22001 THEN
v_addbuff := '... [len=' || length || ']';
END IF;
IF errcd = 22002 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;
v_cur := DBMS_SQL.CLOSE_CURSOR(v_cur); ...(4)
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).
If DBMS_SQL.NATIVE, DBMS_SQL.V6, and DBMS_SQL.V7 are specified, then replace thirdArg with a numeric literal 1.
If either DBMS_SQL.VARCHAR2A type or DBMS_SQL.VARCHAR2S type is used, then sixthArg becomes relevant.
If neither DBMS_SQL.VARCHAR2A type nor DBMS_SQL.VARCHAR2S type is used, then thirdArg becomes relevant.
DBMS_SQL.COLUMN_VALUE(firstArg, secondArg, thirdArg, fourthArg, fifthArg)
The following error codes are returned for column_error.
1406: fetched column value was truncated
1405: fetched column value is NULL
The following error codes are returned for column_error.
22001: string_data_right_truncation
22002: null_value_no_indicator_parameter
Obtained values are received with variables specified for arguments.
Since obtained values are the search results for DBMS_SQL.COLUMN_VALUE, they are received with variables specified for the INTO clause of the SELECT statement.
Convert using the following procedure:
Locate the places where the keyword "DBMS_SQL.COLUMN_VALUE" is used in the stored procedure.
Replace the DBMS_SQL.COLUMN_VALUE location called with a SELECT INTO statement.
Check the number of arguments (v_smpid, errcd, and length in the example) specified after secondArg (1 in the example) of DBMS_SQL.COLUMN_VALUE.
Specify "value", "column_error", and "actual_length" in the select list, according to the number of arguments checked in the previous step (for example, if only thirdArg is specified, then specify "value" only.)
Specify thirdArg, fourthArg, and fifthArg (v_smpid, errcd, length in the example) configured for DBMS_SQL.COLUMN_VALUE, for the INTO clause.
Use DBMS_SQL.COLUMN_VALUE in the FROM clause. Specify firstArg, secondArg, and thirdArg (v_cur, 1, v_smpid, in the example) before modification.
If the fourthArg (column_error value in the example) is used, then check the location of the target variable (errcd in the example).
If a decision process is performed in the location checked, then modify the values used in the decision process as below:
1406 to 22001
1405 to 22002
DBMS_SQL.CLOSE_CURSOR(firstArg)
After closing, the cursor specified in firstArg becomes NULL.
After closing, set the cursor to NULL by assigning the return value of DBMS_SQL.CLOSE_CURSOR to it.
Convert using the following procedure:
Locate the places where the keyword "DBMS_SQL.CLOSE_CURSOR" is used in the stored procedure.
Set the cursor to NULL by assigning (:=) the return value of DBMS_SQL.CLOSE_CURSOR to it.
On the left-hand side, specify the argument (v_cur in the example) specified for DBMS_SQL.CLOSE_CURSOR.
Use DBMS_SQL.CLOSE_CURSOR in the right-hand side. For the argument, specify the same value (v_cur in the example) as before modification.