G.2.2 Correction Method

Describes how to modify each feature.

Note that <datatype> in the text indicates the data type listed in "G.3 DBMS_SQL Package for Fujitsu Enterprise Postgres 16 SPz and earlier".

BIND_VARIABLE

[Feature]: Binds a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement.

[How to use]

	Type	Argument types
Old package	function	integer, text, <datatype> [, integer]
New package	procedure	integer, oracle.varchar2, "any"
New package	function (bind_variable_f)	integer, oracle.varchar2, "any"

[Migrating to new package]

The execution method needs to be turned into the procedure or function.
If you are specifying the fourth argument, before using this function, use the LEFT function to cut out the second argument string so that it can be specified with the length of the fourth argument.

CLOSE_CURSOR

[Feature]: Closes the specified cursor and frees memory.

[How to use]

	Type	Argument types
Old package	function	integer
New package	procedure	integer

[Migrating to new package]

The execution method needs to be turned into a procedure.

COLUMN_VALUE

[Feature]: The value of the cursor element at the specified position within the cursor is assigned to the variable specified in the third argument.

[How to use]

	Type	Argument types
Old package	function	integer, integer, INOUT <datatype>
New package	procedure	integer, integer, INOUT anyelement
New package	function (column_value_f)	integer, integer, INOUT anyelement

[Migrating to new package]

The execution method needs to be turned into the procedure or function.
If you need the length of the resulting string, after using this feature, add a process to get the length using the LENGTH function.
You cannot obtain error codes (22001, 22002) for the processing results. You need to add processing depending on the type of error code you are judging.
[When you need to judge error code 22002]
It judges whether the result string is a NULL value or not.
In the new COLUMN_VALUE package, please either "change the judgment process for error code 22002 to the NULL value judgment process for the result string" or "perform a NULL value judgment on the result string in advance, and if it is a NULL value, set the error code 22002 yourself".
[When you need to judge error code 22001]
It judges whether the result string was truncated to the maximum string length previously specified in DEFINE_COLUMN. In addition, the maximum string length is the value of the fourth argument of DEFINE_COLUMN executed beforehand. Therefore, in order to obtain the same result as error code 22001 with the new package's COLUMN_VALUE, the following measures are required.
- Prepare a separate INTEGER type variable, set the value of the fourth argument of DEFINE_COLUMN to that variable, and omit the fourth argument.
- Compare the length of the result string with the saved maximum string length, and take either of the following measures: "If the length of the result string is greater, execute the judgment process for error code 22001" or "Set the error code 22001 yourself to make it possible to execute the subsequent error code judgment process"
Example) Below is an example of how to handle error code judgment process for NULL values and truncation at the maximum string length.
```
max_length INTEGER;
errcd INTEGER;
length INTEGER;

・・・

max_length := 10;
CALL DEFINE_COLUMN(v_cursor, 1, v_string_values1);

・・・

errcd := 0;
length := 0;
CALL COLUMN_VALUE(v_cursor, 1, v_string_values1);

-- Setting errcd
IF v_string_values1 IS NULL THEN
  errcd := 22002;
ELSE
  IF LENGTH(v_string_values1) > max_length THEN
    errcd := 22001;
    v_string_values1 := LEFT(v_string_values1, max_length);
  END IF;
END IF;

-- Setting length
length := LENGTH(v_string_values1);

IF errcd = 22001 THEN
  -- Handling errcd 22001

  ・・・

END IF;

IF errcd = 22002 THEN
  -- Handling errcd 22002

  ・・・

END IF;
```

DEFINE_COLUMN

[Feature]: Defines the columns to be selected from the given cursor.

[How to use]

	Type	Argument types
Old package	function	integer, integer, <datatype> [, integer]
New package	procedure	integer, integer, "any" [, integer]

[Migrating to new package]

The execution method needs to be turned into a procedure.
If the fourth argument is specified and the subsequent COLUMN_VALUE is used to determine whether the string has been truncated (error code: 22001), save the value in the fourth argument as an INTEGER variable and omit the fourth argument. For information on how COLUMN_VALUE works, refer to "COLUMN_VALUE".
This feature must be executed for all columns specified in SELECT. If EXECUTE is executed with any columns missing or omitted, an error will occur.

Old package

You can get only some columns with DEFINE_COLUMN.

Example) To get the second column
SQL := 'SELECT id, data, val1 FROM test WHERE data=:x';
perform DBMS_SQL.DEFINE_COLUMN(CURSOR, 2, COL2, 10 );

New package

Even if you only need some of the columns, you need to get all the columns with DEFINE_COLUMN. Therefore, please do one of the following:

Example 1) Execute DEFINE_COLUMN for all columns.
SQL := 'SELECT id, data, va1 FROM test WHERE data=:x';
call DBMS_SQL.DEFINE_COLUMN(CURSOR, 1, COL1, 10 );
call DBMS_SQL.DEFINE_COLUMN(CURSOR, 2, COL2, 10 );
call DBMS_SQL.DEFINE_COLUMN(CURSOR, 3, COL3, 10 );

Example 2) Modify your SELECT statement to retrieve only the columns you need.
SQL := 'SELECT data FROM test WHERE data=:x';
call DBMS_SQL.DEFINE_COLUMN(CURSOR, 1, COL2, 10 );

EXECUTE

[Feature]: Executes the specified cursor.

[How to use]: There is no difference.

FETCH_ROWS

[Feature]: Fetches rows from the given cursor.

[How to use]: There is no difference.

OPEN_CURSOR

[Feature]: Opens a new cursor.

[How to use]

	Type	Argument types
Old package	function	[integer]
New package	function	void

[Migrating to new package]

The argument is not necessary, so if it is specified, delete it.

PARSE

[Feature]: Parses the specified statement in the specified cursor. All statements are parsed immediately. In addition, DDL statements are executed immediately when they are parsed.

[How to use]

	Type	Argument types
Old package	function	integer, text [, integer, text DEFAULT '', text DEFAULT '', Boolean DEFAULT false]
New package	procedure	integer, oracle.varchar2

[Migrating to new package]

The execution method needs to be turned into a procedure.

Example) Programs in old packages

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();

    PERFORM DBMS_SQL.PARSE(v_cur, str_sql, 1); 
    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); 
        IF errcd = 22002 THEN
          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); 
    RETURN;
END;
$$
LANGUAGE plpgsql;

Example) Programs after migration to new packaging
The corrections are in red.

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();

    CALL DBMS_SQL.PARSE(v_cur, str_sql); 
    CALL DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid);
    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('--------------------------------------------------------');

        errcd := 0;
        length := 0;
        CALL DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_smpid); 

        IF v_smpid IS NULL THEN
          errcd := 22002;
        END IF;

        IF errcd = 22002 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);

        errcd := 0; 
        length := 0;
        IF v_smpnm IS NULL THEN
          errcd := 22002;
        ELSE;
          length := LENGTH(v_smpnm);
          IF length > v_smpnm_max_length THEN
             errcd := 22001;
             length := v_smpnm_max_length;
             v_smpnm := LEFT(v_smpnm, v_smpnm_max_length);
          END IF;
        END IF;

        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;

    CALL DBMS_SQL.CLOSE_CURSOR(v_cur); 
    v_cur := NULL;
    RETURN;
END;
$$
LANGUAGE plpgsql;