8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Implicit Statement Results in Oracle Database 12c Release 1 (12.1) (DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT)
Implicit statement results will simplify the migration of stored procedures from other database engines to Oracle 12c onward.
Related articles.
- Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results
- Using Ref Cursors To Return Recordsets
- Dynamic SQL Enhancements in Oracle 11g (TO_CURSOR_NUMBER, TO_REFCURSOR)
Background
Spend more than a few minutes with a Transact-SQL developer and you will have a conversation something like this.
- T-SQL Developer: How do I pass results out of a stored procedure?
- Oracle Developer: You use an out parameter.
- T-SQL Developer: But I want to return a resultset.
- Oracle Developer: No problem, make the out parameter a ref cursor and you're laughing.
- T-SQL Developer: So I have to define out parameters for each of the resultsets I want to pass out?
- Oracle Developer: Yes.
- T-SQL Developer: Oh man! Oracle Sucks!
This type of conversation comes about because Transact-SQL allows implicit returns of results from queries. For example, the following Transact-SQL stored procedure implicitly returns the results of the query.
CREATE PROCEDURE Get_My_Results ( @p_id int ) AS SELECT description, created_date FROM t1 WHERE id = @p_id RETURN 0 GO
Oracle 12c now supports similar functionality using the RETURN_RESULT
procedure in the DBMS_SQL
package.
I can't imagine using this feature in the context of a regular PL/SQL programming environment, but it will be extremely valuable in any migration projects.
RETURN_RESULT
Rather than defining explicit ref cursor out parameters, the RETURN_RESULT
procedure in the DBMS_SQL
package allows you to pass them out implicitly. To see an example of this, create the following table.
CREATE table t1 ( id NUMBER, description VARCHAR2(30), created_date DATE ); INSERT INTO t1 VALUES (1, 'The value 1', SYSDATE-2); INSERT INTO t1 VALUES (2, 'The value 2', SYSDATE-1); INSERT INTO t1 VALUES (3, 'The value 3', SYSDATE); COMMIT;
Now we can create a stored procedure to return one or more resultsets containing this data.
CREATE OR REPLACE PROCEDURE get_my_results (p_id IN NUMBER DEFAULT NULL) AS l_cursor_1 SYS_REFCURSOR; l_cursor_2 SYS_REFCURSOR; BEGIN IF p_id IS NOT NULL THEN OPEN l_cursor_1 FOR SELECT description, created_date FROM t1 WHERE id = p_id; DBMS_SQL.RETURN_RESULT(l_cursor_1); END IF; OPEN l_cursor_2 FOR SELECT COUNT(*) FROM t1; DBMS_SQL.RETURN_RESULT(l_cursor_2); END; /
If we execute this stored procedure from SQL*Plus, it will automatically display the implicit statement results.
SQL> EXEC get_my_results(1); PL/SQL procedure successfully completed. ResultSet #1 DESCRIPTION CREATED_DATE ------------------------------ -------------------- The value 1 06-JUL-2013 21:19:45 1 row selected. ResultSet #2 COUNT(*) ---------- 3 1 row selected. SQL> EXEC get_my_results; PL/SQL procedure successfully completed. ResultSet #1 COUNT(*) ---------- 3 1 row selected. SQL>
There is also an overload of the RETURN_RESULT
procedure that returns a cursor using a DBMS_SQL
cursor ID, but I can't see this being useful for a Java or C# developer, so I will not cover it here.
GET_NEXT_RESULT
Typically we would expect these resultsets to be processed by a client programming language, like Java or C#, but they can be processed in PL/SQL using the GET_NEXT_RESULT
procedure in the DBMS_SQL
package.
The following code sample executes the procedure using the DBMS_SQL
package. Since the procedure can return multiple resultsets with differing record structures (variant resultsets), we have to describe the resultset to see how to process it. The decision is made based on the number of columns in the resultset.
SET SERVEROUTPUT ON DECLARE l_sql_cursor PLS_INTEGER; l_ref_cursor SYS_REFCURSOR; l_return PLS_INTEGER; l_col_cnt PLS_INTEGER; l_desc_tab DBMS_SQL.desc_tab2; l_count NUMBER; l_description t1.description%TYPE; l_created_date t1.created_date%TYPE; BEGIN -- Execute the function. l_sql_cursor := DBMS_SQL.open_cursor(treat_as_client_for_results => TRUE); DBMS_SQL.parse(c => l_sql_cursor, statement => 'BEGIN get_my_results(1); END;', language_flag => DBMS_SQL.native); l_return := DBMS_SQL.execute(l_sql_cursor); -- Loop through retrieving every resultset. LOOP -- Get the next resultset. BEGIN DBMS_SQL.get_next_result(l_sql_cursor, l_ref_cursor); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; -- Describe the resultset, to check the number of columns. l_return := DBMS_SQL.to_cursor_number(l_ref_cursor); DBMS_SQL.describe_columns2 (l_return, l_col_cnt, l_desc_tab); l_ref_cursor := DBMS_SQL.to_refcursor(l_return); -- Process the result set according to the number of columns. CASE l_col_cnt WHEN 1 THEN DBMS_OUTPUT.put_line('It must be the COUNT'); FETCH l_ref_cursor INTO l_count; DBMS_OUTPUT.put_line('l_count=' || l_count); CLOSE l_ref_cursor; WHEN 2 THEN DBMS_OUTPUT.put_line('It must be the DESCRIPTION and CREATED_DATE.'); LOOP FETCH l_ref_cursor INTO l_description, l_created_date; EXIT WHEN l_ref_cursor%NOTFOUND; DBMS_OUTPUT.put_line('l_description=' || l_description || ' ' || 'l_created_date=' || TO_CHAR(l_created_date, 'DD-MON-YYYY')); END LOOP; CLOSE l_ref_cursor; ELSE DBMS_OUTPUT.put_Line('I wasn''t expecting that!'); END CASE; END LOOP; END; / It must be the DESCRIPTION and CREATED_DATE. l_description=The value 1 l_created_date=06-JUL-2013 It must be the COUNT l_count=3 PL/SQL procedure successfully completed. SQL>
There is also an overload of the GET_NEXT_RESULT
procedure that returns a cursor using a DBMS_SQL
cursor ID.
For more information see:
- Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results
- Using Ref Cursors To Return Recordsets
- Dynamic SQL Enhancements in Oracle 11g (TO_CURSOR_NUMBER, TO_REFCURSOR)
- Implicit Statement Results
- DBMS_SQL
- Fetching Iterative Results from a SELECT inside a PL/SQL Block
Hope this helps. Regards Tim...