U0100082: GaussDB does not support DBMS_SQL.GET_NEXT_RESULT

Description

Oracle: DBMS_SQL.GET_NEXT_RESULT processes multiple result sets returned by dynamic SQL queries and obtains the next result set returned from recursive statements.

GaussDB: DBMS_SQL.GET_NEXT_RESULT is not supported.

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB versions supported by UGO

Syntax Example

create or replace procedure DBMS_SQL_GET_NEXT_RESULT as
DECLARE
    l_cursor_id INTEGER;       --    Cursor ID
    l_return INTEGER;       --     Returned value
   l_id         NUMBER;        --     Values in the ID column
    l_name         VARCHAR2(100);    --  Values in the NAME column
BEGIN
   --  Open the cursor.
    l_cursor_id := DBMS_SQL.OPEN_CURSOR;
    -- Parse SQL statements (multiple simulated result sets).
    DBMS_SQL.PARSE(
        l_cursor_id,
		 'SELECT id, name FROM test_table WHERE id <= 2 union all SELECT id, name FROM test_table WHERE id > 2' ,
        DBMS_SQL.NATIVE
    );
   -- Execute the query.
    l_return := DBMS_SQL.EXECUTE(l_cursor_id);

   -- Obtain and process the result sets.
    LOOP
        BEGIN
        --   Obtain the next result set.
            DBMS_SQL.GET_NEXT_RESULT(l_cursor_id, l_return);
           --  If there are no more result sets, exit the loop.
            EXIT WHEN l_return = 0;
      -- Define columns which match the queried ones.
            DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_id);
            DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_name, 100);

         --   Extract and generate data.
            DBMS_OUTPUT.PUT_LINE('--- Result Set ---');
            LOOP
              -- Extract a row.
                IF DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 THEN
                   -- Obtain column values.
                    DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_id);
                    DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_name);

                    -- Check the output.
                    DBMS_OUTPUT.PUT_LINE('ID: ' || l_id || ', NAME: ' || l_name);
                ELSE
                -- EXIT; -- Stop extracting the result sets.
                END IF;
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
            -- EXIT; -- If an exception occurs, exit the loop.
        END;
    END LOOP;
/

Suggestion

Comment out the logic of DBMS_SQL.GET_NEXT_RESULT.

Syntax compatible with GaussDB:

create or replace procedure DBMS_SQL_GET_NEXT_RESULT1() as
DECLARE
    l_cursor_id  NUMBER (38);         -- Cursor ID
    l_return     NUMBER(38);        --  Returned value
   l_id         NUMBER;        --     Values in the ID column
    l_name         VARCHAR2(100);    --  Values in the NAME column
BEGIN
   --  Open the cursor.
    l_cursor_id := DBE_SQL.REGISTER_CONTEXT();
    -- Parse SQL statements (multiple simulated result sets).
    DBE_SQL.SQL_SET_SQL(
        l_cursor_id,
         'SELECT id, name FROM test_table WHERE id <= 2 union all SELECT id, name FROM test_table WHERE id > 2' ,
        2
    );
   -- Execute the query.
    l_return := DBE_SQL.SQL_RUN(l_cursor_id);
   -- Obtain and process the result sets.
   LOOP
        BEGIN
          /*-- Obtain the next result set.
          DBE_SQL.RUN_AND_NEXT(l_cursor_id/*, l_return*/);
          -- Execute the query.
         l_return := DBE_SQL.SQL_RUN(l_cursor_id);
           --  If there are no more result sets, exit the loop.
           EXIT WHEN l_return = 0;*/
          -- Define columns which match the queried ones.
            DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 1, l_id);
            DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 2, l_name, 100);
         --   Extract and generate data.
            DBE_OUTPUT.PRINT_LINE('--- Result Set ---');
            LOOP
              -- Extract a row.
                IF DBE_SQL.NEXT_ROW(l_cursor_id) > 0 THEN
                   -- Obtain column values.
                    DBE_SQL.GET_RESULTS(l_cursor_id, 1, l_id);
                    DBE_SQL.GET_RESULTS(l_cursor_id, 2, l_name);

                    -- Check the output.
                    DBE_OUTPUT.PRINT_LINE('ID: ' || l_id || ', NAME: ' || l_name);
                ELSE
                -- EXIT; -- Stop extracting the result sets.
                END IF;
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
            -- EXIT; -- If an exception occurs, exit the loop.
        END;
   END LOOP;
    --Close the cursor.
    DBE_SQL.SQL_UNREGISTER_CONTEXT(l_cursor_id);
END;
/