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 Oracle-PLSQL@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
