U0100084: GaussDB does not support DBMS_SQL.TO_CURSOR_NUMBER

Description

In Oracle, DBMS_SQL.TO_CURSOR_NUMBER is used to obtain a cursor number. GaussDB does not support this function.

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

DECLARE
  l_ref_cur SYS_REFCURSOR;
  l_dbms_sql_cursor NUMBER;
BEGIN
  -- Open REF CURSOR.
  OPEN l_ref_cur FOR SELECT 'Hello, DBMS_SQL!' AS message FROM dual;
 
  -- Convert the cursor number to the one that can be used by DBMS_SQL.
  l_dbms_sql_cursor := DBMS_SQL.TO_CURSOR_NUMBER(l_ref_cur);
 
  DBMS_OUTPUT.PUT_LINE('Cursor number after conversion: ' | l_dbms_sql_cursor);
 
  -- Close the cursor.
  DBMS_SQL.CLOSE_CURSOR(l_dbms_sql_cursor);
END;
/

Suggestion

Comment out DBMS_SQL.TO_CURSOR_NUMBER and use DBE_SQL.REGISTER_CONTEXT, DBE_SQL.SQL_SET_SQL, and DBE_SQL.SQL_RUN.

Syntax compatible with GaussDB:

DECLARE
  l_ref_cur SYS_REFCURSOR;
  l_dbms_sql_cursor NUMBER;
BEGIN
  -- Open REF CURSOR.
  OPEN l_ref_cur FOR SELECT 'Hello, DBMS_SQL!' AS message FROM dual;
  -- Convert the cursor number to the one that can be used by DBMS_SQL.
  --l_dbms_sql_cursor := DBMS_SQL.TO_CURSOR_NUMBER(l_ref_cur);
  l_dbms_sql_cursor := DBE_SQL.REGISTER_CONTEXT();
   DBE_SQL.SQL_SET_SQL( l_dbms_sql_cursor,l_ref_cur,2);
   DBE_SQL.SQL_RUN( l_dbms_sql_cursor);
 
  DBE_OUTPUT.PRINT_LINE('Cursor number after conversion: ' | l_dbms_sql_cursor);
  -- Close the cursor.
  DBE_SQL.SQL_UNREGISTER_CONTEXT(l_dbms_sql_cursor);
END;
/