How to return a CTE as REFCURSOR from an Oracle stored procedure? 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;
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.