plsql - Oracle 11g PL/SQL cursor,intersect query -

plsql - Oracle 11g PL/SQL cursor,intersect query -

i have next query

select distinct name table1 intersect select distinct name table2;

i load resultset cursor in pl/sql procedure, so:

cursor c1 (select distinct name table1 intersect select distinct name table2);

for reason lastly value in resultset duplicated in cursor. not happen when running query itself. ideas why happening?

code loop:

var table.col%type; begin open c1; loop begin exit when c1%notfound; fetch c1 var; insert table values (col1, var); commit; exception when dup_val_on_index continue; end; end loop; end;

exit when .. clause should come after fetch.

let's say, cursor had 10 records return. before first fetch fired, %notfound evaluated null, , processing moves next statement, fetch in case. now, if fast forwards 10th iteration, fetch 10th record, , same inserted destination table. loop move ahead, , since exit when %notfound before fetch, still has value lastly iteration, , lets command move ahead, , there, fetch not able record, code anyhow insert lastly row retrieved in 10th iteration. in next loop, c1%notfound evaluated true , loop terminate

var table.col%type; begin open c1; loop begin fetch c1 var; exit when c1%notfound; insert table values (col1, var); commit; exception when dup_val_on_index continue; end; end loop; end;

oracle plsql cursor