ref cusror does not return rows

From: Manoj <manoj_20057_at_yahoo.com>
Date: 12 Oct 2005 20:48:13 -0700
Message-ID: <1129175293.720235.36250@g47g2000cwa.googlegroups.com>

Hello all,

I have created following package using REF CURSOR. But the cursor does not return any rows. But if i do, fetch loop, it shows the proper values. What I am doing wrong ??

CREATE OR REPLACE PACKAGE pckgNm
IS

   TYPE t_cursor IS REF CURSOR;
   PROCEDURE sp_getdata (p_recid IN NUMBER, p_datarec IN OUT t_cursor);

END pckgnm;

CREATE OR REPLACE PACKAGE BODY pckgnm
IS

PROCEDURE sp_getdata (p_recid IN NUMBER, p_datarec IN OUT t_cursor)

   IS

      chkrec   CHAR;
      chkcnt   NUMBER;
      v_field1 varchar2(10);
      v_rec    t_cursor;

   BEGIN

      OPEN v_rec
       FOR
          SELECT field1
            FROM view_object where recidfield = p_recid;

      chkcnt := v_rec%ROWCOUNT;			-- shows zero

      IF v_rec%FOUND
      THEN
         p_datarec := v_rec;
         chkrec := 'Y';
      ELSE
         chkrec := 'N';				-- control comes here if still having

records

      END IF;
      p_datarec := v_datarec;                   -- passes null

      LOOP
      FETCH v_rec INTO v_field1;

        -- this loop runs if having the records	and v_field1 shows

proper values

   	EXIT WHEN v_rec%NOTFOUND;
	-- process row

      END LOOP;

      CLOSE v_rec;

   END; end pckgnm;

thanks,

Manoj. Received on Wed Oct 12 2005 - 22:48:13 CDT