PL/SQL Cursor over DB Link from Oracle to Sql Server
Posted on 2011-10-20
I have a DB link from oracle to sql server. I am generating a for in cursor. It returns the data referenced from the cursor fine but is only looping through the cursor once???
cursor c1 is
select row_a,row_b,row_c from table a;
for r1 in c1 loop
select a into b from table b where c = r1.row_b;
output := r1.row_a, etc;
The output with the direct reference to the cursor outputs fine (output := r1.row_a, etc) and returns all rows from the cursor correctly.
The output with using a select into (select a into b from table b where c = r1.row_b); where where c = r1.row_b is only reading the first row into r1.row_b and not the remainder of the rows.
I'm not sure if this is a limitation of Oracle to Sql Server over the link using PL/SQL. It almost seems as if it is retuning it as a ref cursor and ignoring everything else in the loop.