ref cusror does not return rows

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.

.

Relevant Pages

  • HELP!! ORA-00902: invalid datatype
    ... I am creating a package in the schema matt with reference to Tables in ... The package and body pasted below. ... TYPE cur_type IS REF CURSOR; ... END LOOP; ...
    (comp.databases.oracle.server)
  • Re: Mixed Simulation of Design (VHDL and Verilog)
    ... Dependency exists between package a and package BODY b ... SUBTYPE c_range IS natural RANGE a'RANGE; ... c_loop: ... Segmentation violation if a subtype is used as an index ...
    (comp.lang.vhdl)
  • Fun with Tasking
    ... Below is sample code I made representing something I wanted to do with ... this, as I mentioned, "exec" is representative of a family of several ... package Convert is new System.Address_To_Access_Conversions ... end loop; ...
    (comp.lang.ada)
  • Looping through files for importing
    ... I understand how to loop through all files in a folder using VB/VBA, so I can manage that if I save the package as a VB File. ... one month and conveniently has the month end date in the file name, so I can populate the field by extracting the date from the file ... then in my Transform Data Task, I would have an ActiveX Script transformation assigning that date global variable to the MEDate ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Workflow Question
    ... >I am using SQL Server 2000 and I am writing a DTS package that loops ... >Script handling the loop routine. ... You could duplicate the ActiveX Script to restart the loop, ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)