Re: Why this code is not working (REF CURSOR)?

I think you should open l_errorlog_cursor for select.

In article <85dkjs$r1r$1_at_nnrp1.deja.com>,   gmei_at_my-deja.com wrote:
> Hi:
>
> I am trying to write a package which will return a record set from
> oracle db.
> I am using REF CURSOR to return a cursor set. But I can not get the
data
> from the calling procedure. The data prints correctly in the package,
> but not outside the package.
> Could you tell me what I am doing wrong here?
>
> Thanks in advance.
>
> By the way, if you reply, please send a copy to zlmei_at_hotmail.com
>
> Guang
>
> ---------I am running this in sql worksheet----
>
> declare
> type errorlog_cursor_type IS REF CURSOR;
> l_errorlog_cursor errorlog_cursor_type;
> lERRORLOGID HPXERRORLOG.ERRORLOGID%TYPE;
> lERRORLOGTIMESTAMP HPXERRORLOG.ERRORLOGTIMESTAMP%TYPE;
> lERRORLOGUSERSTAMP HPXERRORLOG.ERRORLOGUSERSTAMP%TYPE;
> lERRORMESSAGEHEADER HPXERRORLOG.ERRORMESSAGEHEADER%TYPE;
> lERRORMESSAGEDETAIL HPXERRORLOG.ERRORMESSAGEDETAIL%TYPE;
> begin
> l_errorlog_cursor := HPXPKG_ERRORLOG.load_errorlog(1);
>
> DBMS_OUTPUT.PUT_LINE('Outside package, after call:');
>
> LOOP FETCH l_errorlog_cursor
> INTO lERRORLOGID,
> lERRORLOGTIMESTAMP,
> lERRORLOGUSERSTAMP,
> lERRORMESSAGEHEADER,
> lERRORMESSAGEDETAIL;
> EXIT WHEN l_errorlog_cursor%NOTFOUND;
>
> DBMS_OUTPUT.PUT_LINE(lERRORLOGTIMESTAMP);
> DBMS_OUTPUT.PUT_LINE(lERRORMESSAGEHEADER);
> DBMS_OUTPUT.PUT_LINE(lERRORMESSAGEDETAIL);
>
> END LOOP;
> DBMS_OUTPUT.PUT_LINE('Outside package, after end loop');
> end;
>
> -----------------
>
> --------here is the package code------------
>
> ------------------------------------------------------------------
> CREATE OR REPLACE PACKAGE hpxpkg_errorlog
> IS
>
> TYPE errorlog_ref_cur IS REF CURSOR;
> ------------------------------------------------------------------
>
> FUNCTION load_errorlog (
> pERRORLOGID IN HPXERRORLOG.ERRORLOGID%TYPE)
> RETURN errorlog_ref_cur;
>
> END hpxpkg_errorlog ;
> /
> ------------------------------------------------------------------
> CREATE OR REPLACE PACKAGE BODY hpxpkg_errorlog
>
> IS
> ------------------------------------------------------------------
> ------------------------------------------------------------------
> FUNCTION load_errorlog (
> pERRORLOGID IN HPXERRORLOG.ERRORLOGID%TYPE)
> RETURN errorlog_ref_cur
> IS
> l_errorlog_cursor errorlog_ref_cur;
> lERRORLOGID HPXERRORLOG.ERRORLOGID%TYPE;
> lERRORLOGTIMESTAMP HPXERRORLOG.ERRORLOGTIMESTAMP%TYPE;
> lERRORLOGUSERSTAMP HPXERRORLOG.ERRORLOGUSERSTAMP%TYPE;
> lERRORMESSAGEHEADER HPXERRORLOG.ERRORMESSAGEHEADER%TYPE;
> lERRORMESSAGEDETAIL HPXERRORLOG.ERRORMESSAGEDETAIL%TYPE;
> BEGIN
>
> OPEN l_errorlog_cursor FOR
> SELECT ERRORLOGID,
> ERRORLOGTIMESTAMP,
> ERRORLOGUSERSTAMP,
> ERRORMESSAGEHEADER,
> ERRORMESSAGEDETAIL
> FROM HPXERRORLOG
> WHERE ERRORLOGID = pERRORLOGID;
>
> FETCH l_errorlog_cursor
> INTO lERRORLOGID,
> lERRORLOGTIMESTAMP,
> lERRORLOGUSERSTAMP,
> lERRORMESSAGEHEADER,
> lERRORMESSAGEDETAIL;
>
> DBMS_OUTPUT.PUT_LINE('Inside package:');
> DBMS_OUTPUT.PUT_LINE(lERRORLOGID);
> DBMS_OUTPUT.PUT_LINE(lERRORLOGTIMESTAMP);
> DBMS_OUTPUT.PUT_LINE(lERRORLOGUSERSTAMP);
> DBMS_OUTPUT.PUT_LINE(lERRORMESSAGEHEADER);
> DBMS_OUTPUT.PUT_LINE(lERRORMESSAGEDETAIL);
>
> RETURN l_errorlog_cursor ;
>
> END load_errorlog;
> ------------------------------------------------------------------
>
> END hpxpkg_errorlog;
>
> -----Here is what I got from the sql worksheet:
>
> Inside package:
> 1
> 10-JAN-00
> ESPEED_OWNER
> 123
> 456
> Outside package, after call:
> Outside package, after end loop
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jan 22 2000 - 06:27:59 CST