U0100081: GaussDB does not support DBMS_SQL.TO_REFCURSOR

Description

Oracle: DBMS_SQL.TO_REFCURSOR converts the ID of DBMS_SQL to REF CURSOR.

GaussDB: DBMS_SQL.TO_REFCURSOR 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_TO_REFCURSOR AS l_cursor_id INTEGER;
l_ref_cursor SYS_REFCURSOR;
l_id NUMBER;
l_name VARCHAR2 (100);
l_return INTEGER;
BEGIN
-- Open the DBMS_SQL cursor.
  l_cursor_id := DBMS_SQL.OPEN_CURSOR;
-- Parse SQL statements.
  DBMS_SQL.PARSE (l_cursor_id, 'SELECT id, name FROM test_table', DBMS_SQL.NATIVE);
-- Execute the query.
  l_return := DBMS_SQL.EXECUTE (l_cursor_id);
-- Convert DBMS_SQL to REF CURSOR.
  l_ref_cursor := DBMS_SQL.TO_REFCURSOR (l_return);
-- Use REF CURSOR to process result sets.
  LOOP
  FETCH l_ref_cursor INTO l_id,
  l_name;
  EXIT 
  WHEN l_ref_cursor % NOTFOUND;
  DBMS_OUTPUT.PUT_LINE ('ID: ' || l_id || ', NAME: ' || l_name);
END LOOP;
-- Close REF CURSOR.
CLOSE l_ref_cursor;
END;
/

Suggestion

Change DBMS_SQL.TO_REFCURSOR to open cursor for...

create or replace procedure DBMS_SQL_TO_REFCURSOR as
    l_cursor_id  NUMBER(38);
    l_ref_cursor SYS_REFCURSOR;
    l_id         NUMBER;
    l_name       VARCHAR2(100);
    l_return     NUMBER(38);       
BEGIN
   -- Open the DBMS_SQL cursor.
    --  l_cursor_id := DBE_SQL.REGISTER_CONTEXT();
   -- Parse SQL statements.
    -- DBE_SQL.SQL_SET_SQL(l_cursor_id, 'SELECT id, name FROM test_table', 2);
    -- Convert DBMS_SQL to REF CURSOR.
    --l_ref_cursor := DBMS_SQL.TO_REFCURSOR(l_return);
   -- Use REF CURSOR to process result sets.
open l_ref_cursor for SELECT id, name FROM test_table; 
    LOOP
        FETCH l_ref_cursor INTO l_id, l_name;
        EXIT WHEN l_ref_cursor%NOTFOUND;
        DBE_OUTPUT.PRINT_LINE('ID: ' || l_id || ', NAME: ' || l_name);
    END LOOP;
   -- Close REF CURSOR.
    CLOSE l_ref_cursor;
END;
/