SQL & PL/SQL

Hi all,

I have two different cursors of same recors.


create table temp_emp as select * from emp;


cursor cr_emp IS
select * from emp;
cursor cr_tmp IS
select * from temp_emp;

how to compare these two cursors.

Im trying to learn comparing cursors variables. I have oracle 11g, windows Vista.



Initially, I tried to compare only ename coloum:


create or replace procedure compare
IS


cursor cr_emp IS
select * from emp;
cursor cr_temp IS
select * from temp_emp;


v_cr_emp cr_emp%rowtype;
v_cr_temp cr_temp%rowtype;

emp_rownum integer;
tmpy_rownum integer;

V_CODE NUMBER;
V_ERRM VARCHAR2(64);


BEGIN

select MAX(rownum) into emp_rownum from emp;

select MAX(rownum) into tmpy_rownum from tmpy_emp;

open cr_emp;
open cr_temp;

FETCH CR_EMP INTO v_cr_emp;
FETCH CR_TEMP INTO v_cr_temp;


for emp_rownum in cr_emp
loop
for tmpy_rownum in cr_tmpy
loop
if(v_cr_emp.ename = v_cr_temp.ename)
then
dbms_output.put_line('equal');
else
dbms_output.put_line( 'not equal');
end if;
end loop;
end loop;

EXCEPTION
WHEN OTHERS
THEN
V_CODE := SQLCODE;
V_ERRM := SUBSTR(SQLERRM,1,64);
DBMS_OUTPUT.put_line ('ERROR CODE' || V_CODE || ':' || V_ERRM);


close cr_emp;
close cr_tmpy;

END;
/


SQL> start rain.sql

Procedure created.

SQL> exec compare
ERROR CODE-6511:ORA-06511: PL/SQL: cursor already open

PL/SQL procedure successfully completed.

Edited by: user10676396 on Apr 16, 2009 7:50 PM