Hi David, you are correct, close is not necessary there. The issue here is that the cursor is invalid, no operations, like fecth, is possible on the cursor. i used 'close' to simplify the test proc.
Anyways, i got the answer here: http://www.orafaq.com/forum/m/358478/132067/#msg_358478 Thanks Harish On Nov 11, 9:51 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Comments embedded. > > On Nov 10, 9:49 pm, Harish <[EMAIL PROTECTED]> wrote: > > > I've 3 procedures. proc3 calls proc2 calls proc1 > > proc3 passes a sys_refcursor as an out parameter to proc2 > > proc2 passes that to proc1 > > proc1 opens the cursor with a select > > All well and good. > > > proc3 simply closes the out cursor > > Why? It doesn't need to be closed, fetching from it will close it > automatically. > > > i am getting "ORA-01001: invalid cursor" while closing the cursor. > > any idea? > > Yes, it doesn't NEED a close statement. > > > > > > > -- PROC1 > > CREATE OR REPLACE > > PROCEDURE TEST_PROC1 > > ( > > oCur OUT SYS_REFCURSOR > > ) AS > > BEGIN > > OPEN oCur FOR SELECT entity_id FROM entity; > > END TEST_PROC1; > > > -- PROC2 > > CREATE OR REPLACE > > PROCEDURE TEST_PROC2 > > ( > > oCur OUT SYS_REFCURSOR > > ) AS > > BEGIN > > test_proc1(oCur); > > END TEST_PROC2; > > > -- PROC3 > > CREATE OR REPLACE > > PROCEDURE TEST_PROC3 AS > > oCur SYS_REFCURSOR; > > BEGIN > > test_proc2(oCur); > > CLOSE oCur; -- Invalid cursor error here > > END TEST_PROC3; > > Here's what you should have written (or one example of it, anyway): > > SQL> create table entity( > 2 entity_id number > 3 ); > > Table created. > > SQL> > SQL> begin > 2 for i in 1..10 loop > 3 insert into entity > 4 values(i); > 5 end loop; > 6 end; > 7 / > > PL/SQL procedure successfully completed. > > SQL> > SQL> > SQL> -- PROC1 > SQL> CREATE OR REPLACE > 2 PROCEDURE TEST_PROC1 > 3 ( > 4 oCur IN OUT SYS_REFCURSOR > 5 ) AS > 6 BEGIN > 7 OPEN oCur FOR SELECT entity_id FROM entity; > 8 END TEST_PROC1; > 9 / > > Procedure created. > > SQL> > SQL> show errors > No errors. > SQL> > SQL> -- PROC2 > SQL> CREATE OR REPLACE > 2 PROCEDURE TEST_PROC2 > 3 ( > 4 oCur IN OUT SYS_REFCURSOR > 5 ) AS > 6 ent_id entity.entity_id%type; > 7 BEGIN > 8 test_proc1(oCur); > 9 loop > 10 fetch oCur into ent_id; > 11 exit when oCur%notfound; > 12 dbms_output.put_line(ent_id); > 13 end loop; > 14 > 15 END TEST_PROC2; > 16 / > > Procedure created. > > SQL> > SQL> show errors > No errors. > SQL> > SQL> -- PROC3 > SQL> CREATE OR REPLACE > 2 PROCEDURE TEST_PROC3 AS > 3 oCur SYS_REFCURSOR; > 4 BEGIN > 5 test_proc2(oCur); > 6 END TEST_PROC3; > 7 / > > Procedure created. > > SQL> > SQL> show errors > No errors. > SQL> > SQL> > SQL> exec test_proc3; > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 > 9 > 10 > > PL/SQL procedure successfully completed. > > SQL> > > The reference cursor is automatically closed when the fetch gets to > the end of the data. > > David Fitzjarrell --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
