Why this code is not working (REF CURSOR)?

From: <gmei_at_my-deja.com>
Date: Mon, 10 Jan 2000 21:58:25 GMT
Message-ID: <85dklo$r2a$1@nnrp1.deja.com>

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

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. Received on Mon Jan 10 2000 - 15:58:25 CST