Re: Stored Procedures - Newbie Questions

In article <tJFO7.130$ko4.13235_at_nasal.pacific.net.au>, "Neo" says...
>
>Hi,
>
>I have been working with Oracle server for about 8 months and I have been
>experimenting with stored procedures. I wanted to find out about the
>procedure that I am using, it opens a cursor and I was wondering if the
>cursor is automatically closed when the package is ended?

Well, yes, the cursor would be closed when the package is "ended" since the way to end a package is to close your session and upon closing your session (logging out), all resources are closed up.

The cursor is opened in this package and returned to some calling routine. It is the responsibility of this calling routine to CLOSE the cursor. If the cursor closed right after you called this procedure -- you would never be able to fetch any data.

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:553222846752

for more details.

> Is there a better
>way to do this? I have pasted the example that I have been using below.
>
>Thanks in advance for your answer
>
>Sean
>
>
>
>CREATE OR REPLACE PACKAGE employees_orcl
>AS
>TYPE empcur IS REF CURSOR;
>
>PROCEDURE GetEmpRecords(indeptno IN NUMBER,
>p_cursor OUT empcur,
>
>p_errorcode OUT NUMBER);
>
>END employees_orcl;
>/
>
>CREATE OR REPLACE PACKAGE BODY employees_orcl
>AS
>PROCEDURE GetEmpRecords(indeptno IN NUMBER,
>p_cursor OUT empcur,
>p_errorcode OUT NUMBER)
>IS
>BEGIN
>p_errorcode := 0;
>OPEN p_cursor FOR SELECT * FROM emp WHERE deptno = indeptno ORDER BY
>empno;
>EXCEPTION
>WHEN OTHERS THEN
>p_errorcode := SQLCODE;
>END GetEmpRecords;
>END employees_orcl;
>/
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp