How to return a CTE as REFCURSOR from an Oracle stored procedure

cteoracleoracle-11gstored-procedures

I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement.

My procedure looked like this:

Create or replace Myproc ( MyRefCursor  IN OUT SYS_REFCURSOR)
as
begin
    Open MyRefCursor for
        Select * from ...something ...;


end;
/

I rewrote this query:

   Select * from ...something ...;

As this CTE:

with MyCTE As (
    ... ;
)
Select * from MyCTE;

Opening a cursor this way does not work:

Open MyRefCursor for
with MyCTE As (
    ... ;
)
Select * from MyCTE;

Nor does this:

with MyCTE As (
    ... ;
)
Open MyRefCursor for
Select * from MyCTE;

Best Answer

Since the CTE is part of the same SQL statement, it should not contain a semicolon.

So, there should be no semicolon on the second line of the third block or the third line of the fourth block. The fifth block does not have a contiguous SQL statement.

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Best Answer

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Related Question