U0100083: GaussDB does not support DBMS_SQL.RETURN_RESULT

Description

DBMS_SQL.RETURN_RESULT is introduced in Oracle 12c. Result sets are implicitly returned in dynamic SQL statements. sys_refcursor does not need to be explicitly declared as an OUT parameter. Multiple result sets can be returned at the same time.

GaussDB does not support DBMS_SQL.RETURN_RESULT.

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 get_dynamic_results (p_min_salary IN NUMBER)
AS
    l_cursor SYS_REFCURSOR;
BEGIN
   -- Check the first result set: employee information
    OPEN l_cursor FOR
        SELECT emp_id, emp_name, salary
        FROM employees
        WHERE salary >= p_min_salary;
    DBMS_SQL.RETURN_RESULT(l_cursor);

   -- Check the second result set: department information
    OPEN l_cursor FOR
        SELECT dept_id, dept_name, location
        FROM departments;
    DBMS_SQL.RETURN_RESULT(l_cursor);
END;
/

Suggestion

Comment out DBMS_SQL.RETURN_RESULT. Several result sets are returned. Add several OUT parameters. Set the parameter type to sys_refcursor. OUT parameter variable names must be unique.

Syntax compatible with GaussDB:

CREATE OR REPLACE PROCEDURE get_dynamic_results (p_min_salary IN NUMBER, l_cursor out SYS_REFCURSOR,l_cursor2 out SYS_REFCURSOR)
AS
   --- l_cursor SYS_REFCURSOR;
BEGIN
   -- Check the first result set: employee information
    OPEN l_cursor FOR
        SELECT emp_id, emp_name, salary
        FROM employees
        WHERE salary >= p_min_salary;
 --   DBMS_SQL.RETURN_RESULT(l1_cursor);

   -- Check the second result set: department information
   OPEN l_cursor2 FOR
       SELECT dept_id, dept_name, location
        FROM departments;
    ---DBMS_SQL.RETURN_RESULT(l_cursor);
END;
/