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; /
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot