-
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
-
Re: output record sets with cursors
Does the query return any rows if you run in sqlplus?
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
no rows returned in sql plus
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?
-
getting closer
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|