[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1135
  • Last Modified:

PL/SQL Cursor over DB Link from Oracle to Sql Server

Hi,

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???

I.e.

cursor c1 is
select row_a,row_b,row_c from table a;

begin

for r1 in c1 loop

select a into b from table b where c = r1.row_b;

output := r1.row_a, etc;

end loop;

end;
/

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.

0
1500orion
Asked:
1500orion
  • 6
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
That is what is is supposed to do.

each iteration through the loop sets a new value for r1.row_b therefore a new value for b.

What are you wanting to happen?  I'm really not following.
0
 
1500orionAuthor Commented:
This may be an easier way to explain as I also tested with a counter.  If col A = a b c d e

loop

cnt := 0
cnt := cnt + 1

output := r1.row_a, etc;

end loop;

If there were 5 rows cnt returned 1 1 1 1 1 and output returned a b c d e
0
 
slightwv (䄆 Netminder) Commented:

>>This may be an easier way to explain

Still not understanding.

>>rows cnt returned 1 1 1 1 1

If you set cnt=1 then add 1 to it inside the loop, what do you expect cnt to be?  It will always be 1.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
1500orionAuthor Commented:
Sorry, typo...

I initialized the counter at zero outside the loop.

cnt := 0;

loop

cnt := cnt + 1

end loop

So it should iterate the count each time through the loop but it's not.  
0
 
slightwv (䄆 Netminder) Commented:
Even fixing that typo, it still doesn't help me.

What is the results of:
select row_a,row_b,row_c from table a;

What are you expecting in the results of 'b' on each execution of:
select a into b from table b where c = r1.row_b;


Please provide sample data and expected results.
0
 
1500orionAuthor Commented:
Okay, looking at sample table of what I am expecting:

------------------------------------------------------------------------

Emp table                 Stud table
ID      Data                 ID         Data
1       Test1                 1          Misc A
2       Test2                 2          Misc B
2       Test3
1       Test 4      

------------------------------------------------------------------------

cursor c1 is select id,data from emp;

var_data varchar2(55);

begin

for r1 in c1 loop

select data into var_data from stud where id = r1.id;

output := r1.data||' '||var_data;

end loop;

end;
/

---------------------------------------------------------------------------------------------

What should be returned is:

Test1 Misc A
Test2 Misc B
Test3 Misc B
Test4 Misc A

What is being returned is

Test1 Misc A
Test2 Misc A
Test3 Misc A
Test4 Misc A


So I tested with the counter and even wrote var_data out to a temp table and it is only storing the value from the first read or iteration through the loop or in this case ID '1' in any of my code except for the data returned from the cursor itself defined in the declaration.
0
 
slightwv (䄆 Netminder) Commented:
Using straight sqlplus and oracle I get your expected results running against a 10.2.0.3 database.

Here is the output from my run:
Got: Test1 Misc A
Got: Test2 Misc B
Got: Test3 Misc B
Got: Test 4 Misc A

PL/SQL procedure successfully completed.


drop table myEmp purge;
create table myEmp(id number, data varchar2(10));

drop table myStud purge;
create table myStud(id number, data varchar2(10));

insert into myEmp values(1,'Test1');
insert into myEmp values(2,'Test2');
insert into myEmp values(2,'Test3');
insert into myEmp values(1,'Test 4');

insert into myStud values(1,'Misc A');
insert into myStud values(2,'Misc B');
commit;



declare

cursor c1 is select id,data from myemp;

var_data varchar2(55);
output varchar2(55);

begin

for r1 in c1 loop

select data into var_data from mystud where id = r1.id;

output := r1.data||' '||var_data;

dbms_output.put_line('Got: ' || output);

end loop;

end;
/

Open in new window

0
 
1500orionAuthor Commented:
I don't have this issue with the code against just the Oracle DB.  I've written tons of these against the oracle DB.  I was just wondering if it had something to do with the DB link to Sql Server in the way it was handling the PL/SQL cursor.  I haven't found much online in reference to using PL/SQL cursors and fetches across a db link from oracle to Sql Server.
0
 
slightwv (䄆 Netminder) Commented:
Sorry. I can't help with the SQL Server link issues.

If no other Experts arrive later please click the Request Attention  link and see if a Moderator can locate some other Experts.

In the mean time I'll add a couple more Zones.
0
 
1500orionAuthor Commented:
It was necessary to create another ref cursor fetched in the for loop.  At least for 9i to SqlServer 2005.
0
 
1500orionAuthor Commented:
It was necessary to create an ref cursor called in the for loop.   At least for 9i to Sql Server 2005.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now