output record sets with cursors

I have a dimela where my PL/SQL script does not return any rows. Please understand that I am not running this in SQL PLUS , so I cannot use DBMS_OUTPUT package and I cannot select into variables because I am actually migrating data to SQL SERVER from Oracle using DTS. Here is my script:

DECLARE
current_date Date;
TYPE rst_refcur IS REF CURSOR;
p_rec rst_refcur;
BEGIN
current_date := SYSDATE;
OPEN p_rec FOR
select a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g
where a.geodist = '04'and a.isghstdt = current_date
and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
END;

This gives me a No data returned error, which I can see why, but since I am crossing platforms from my source (Oracle) and my destination (SQLServer) through DTS, I don't see how I can define it.
Please help.Thanks. If anyone knows or has links on how to do this migration , please let me know.

dev

Does the query return any rows if you run in sqlplus?

Thanks for the reply...no rows are returned, should there be some returned?

ok...I understand cursors a bit better now...apparantly, you need a FETCH statement to return the query...so I have put this query together:

DECLARE
current_date Date;
TYPE rst_refcur IS REF CURSOR;
p_rec rst_refcur;
wprptsta char(2);
wpitem char(6);
wpitmseg char(1);
mandisdv char(2);
conclass char(1);
wpwkmix char(4);
itsegman char(20);
wpitemno char(7);
contydot char(2);
geodist char(2);
wpitstat char(3);
localnam char(25);
locatnfm char(25);
locatnto char(25);
isghstdt date;
effdate date;
efftime char(8);
ffisyrcp number(5);
gis_itmseg char(1);
BEGIN
current_date := SYSDATE;
OPEN p_rec FOR
select a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g
where a.geodist = '04' and rownum < 100
and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
FETCH p_rec into wprptsta,wpitem,wpitmseg,mandisdv,conclass,wpwkmix,itsegman,
wpitemno,contydot,geodist,wpitstat,localnam,locatnfm,locatnto,isghstdt,
effdate,efftime,ffisyrcp,gis_itmseg;
END;

However, I am getting

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 32

where line 32 is the fetch line. I've read this error related to running out of stack face, so I have limited the query with rownum < 100, but I still get the mistake. Any ideas? I have a feeling that once I get this to work in SQL plus, it will also work in SQL Server's DTS, becuase I think they use the same runtime checking engine (SQL PLus and Oracle ODBC). Thanks all.

gratefuly,

dev

Hi! I think you are making life hard for yourself. There are several ways of doing a cursor loops. The explicit type with OPEN, FETCH & CLOSE should normally be avoided unless (e.g.) you have a need to leave the cursor open between calls to a procedure.

I suggest you look at this link for the simplest method http://www.csee.umbc.edu/help/oracle...5_ora.htm#1449

Great documentation...the problem lies in that I need the rowset returned to DTS, and I laso need the Select statement to do a comparison with the system date.

So I can have

DECLARE
CURSOR c1 IS
SELECT a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g
where a.geodist = '04'
and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
BEGIN
FOR c1_rec IN c1 LOOP
return(c1_rec); <-- gives an error
END LOOP;
COMMIT;
END;

but in that for loop, I need the record set returned, and this wont work, also I will have to add a condition to the select to compare the system date...I'm not too sure about how to do that. Any ideas on either?

regards,

dev

RETURN is to give a value for a function. I suspect you need a procedure. How does DTS expect the data? All in one big array or a row at a time?

ok...i got something to return the max date, which is just as good as using the system date for me...now I just need to return the row...I believe DTS expects rowsets

DECLARE
CURSOR c1 IS
SELECT a.wprptsta, a.wpitem, a.wpitmseg, a.mandisdv, a.conclass, a.wpwkmix, a.itsegman,
a.wpitemno, a.contydot, a.geodist,a.wpitstat, a.localnam, a.locatnfm, a.locatnto, a.isghstdt,
a.effdate, a.efftime, a.ffisyrcp, g.itmseg gis_itmseg from wpguser.wpgtbl80 a, wpguser.gis_wpgtbl80 g,
(Select max(isghstdt) max_date from wpguser.wpgtbl80
) maxresults
where a.geodist = '04' and a.isghstdt = maxresults.max_date
and a.wprptsta = g.wprptsta(+) and a.wpitem = g.wpitem(+) and a.wpitmseg =g.wpitmseg(+);
BEGIN
DBMS_OUTPUT.PUT_LINE('Entering..');
FOR c1_rec IN c1 LOOP
--DBMS_OUTPUT.PUT_LINE('DATE' || c1_rec.isghstdt);
END LOOP;
END;


Now you see I commented the PUTLINE() , but I need something in that for loop that actually returns that particular row.