If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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.

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.

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.

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?