Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;    
share|improve this question
    
Gotcha, missed it in the question –  bernd_k Jan 12 '11 at 14:56

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

A complete very basic example using a stored procedure without input parameter:

Some demo data:

CREATE TABLE DEMO(
    id NUMBER(10),
    type NUMBER(10),
    code varchar2(20)
);

Insert INTO DEMO values (1, 0, 'a');
Insert INTO DEMO values (2, 0, 'b');
Insert INTO DEMO values (3, 0, 'c');
Insert INTO DEMO values (4, 1, 'd');
Insert INTO DEMO values (5, 1, 'e');
Insert INTO DEMO values (6, 1, 'f');

SELECT * from DEMO;

Now a simple stored procedure using a trivial CTE:

create or replace procedure return_from_simple_cte
(
    MyRefCursor IN OUT SYS_REFCURSOR
)
AS
BEGIN
    Open MyRefCursor for
    WITH My_CTE AS(
        SELECT * FROM DEMO WHERE Type = 1
    )
    Select * FROM My_CTE;


END;
/

And finally a test call for sqlplus or TOAD

VAR R REFCURSOR
EXEC return_from_simple_cte (:R)
PRINT r

Background to this question:

I did the transformation of the Select-statement to CTE on SQL-Server an I wanted to add row-numbers to the result. I simply forget that add an alias to the *

create or replace  PROCEDURE TEChange
(
    MyRefCursor    IN OUT SYS_REFCURSOR
    ,p_TERefnr    NUMBER
)
AS
BEGIN
    Open MyRefCursor for 
    WITH TE_CTE AS (
        SELECT TeRefnr
        FROM vSYSTE
        WHERE TERefnr <> p_TERefnr
    )
    Select
         ROW_NUMBER() over(order by TERefnr)  Nr, 
        t.* 
    from TE_CTE t;
END;
/    

nonreursive CTEs seem to help to migrate from SQL-Server to Oracle, but there are some minor syntax differences.

share|improve this answer
    
I'm glad this solved your problem, but it really doesn't answer the question you asked. Your question is good and will be useful to others, but this answer, not so much. –  Leigh Riffel Jan 12 '11 at 15:35
    
I agree. I googled about it half an hour an found no simple example which led me in the right direction. I only found it by the usual tactic of commenting out portions of the code. I guess this will soon become a CW. –  bernd_k Jan 12 '11 at 15:42
    
I started with one complete example. I think the the question deserves at least another example using input parameters. The special problem of sql-server -> Oracle migration pitfalls deserve(s) its own question(s). –  bernd_k Jan 12 '11 at 16:12
    
Your additions are good and do answer the question title, but not the question body. Either the title should be changed to match the question or the question should be modified to match the title. The route you choose determines which answer is best. I would prefer the former with the current title as a new question and this answer as an answer. –  Leigh Riffel Jan 12 '11 at 16:47
    
@bernd_k I just realized I didn't tag you in the above comment, so here is a tag so you will see this. –  Leigh Riffel Jan 13 '11 at 18:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.