KAVINASH1313
asked on
Not geting data in second loop?
Second cursor is not fetching any data. I am new to Oracle, there might be some fundamentle mistake I might be doing. Please help.
Note:- i am getting data when i execute the sql statments individually
########################## #######
declare
TYPE ref_cur IS REF CURSOR;
v_ref_cur1 ref_cur;
v_ref_cur2 ref_cur;
v_org_i CHAR(3);
v_role_i CHAR(10);
stmt1 VARCHAR2(10000);
stmt2 VARCHAR2(10000);
begin
stmt1:= 'select unique(org_i) from company_X1
where elt_i = ''250''
minus
select unique(org_i) from company_X1
where elt_i = ''265''';
stmt2:= 'select ORG_ELT_ROLE_SEQ_N from company_X2
where elt_i = ''250'' and org_i = '|| '''' || v_org_i || '''' ||'
minus
select ORG_ELT_ROLE_SEQ_N from company_X2
where elt_i = ''265'' and org_i = '|| '''' || v_org_i || '''';
OPEN v_ref_cur1 FOR stmt1;
LOOP
FETCH v_ref_cur1 INTO v_org_i;
EXIT WHEN v_ref_cur1%NOTFOUND;
OPEN v_ref_cur2 FOR stmt2;
LOOP
FETCH v_ref_cur2 INTO v_role_i;
EXIT WHEN v_ref_cur2%NOTFOUND;
dbms_output.put_line(v_rol e_i);
end loop;
dbms_output.put_line(v_org _i);
end loop;
commit;
EXCEPTION
WHEN others then
dbms_output.put_line(sqler rm||sqlcod e);
end;
########################## #######
Note:- i am getting data when i execute the sql statments individually
##########################
declare
TYPE ref_cur IS REF CURSOR;
v_ref_cur1 ref_cur;
v_ref_cur2 ref_cur;
v_org_i CHAR(3);
v_role_i CHAR(10);
stmt1 VARCHAR2(10000);
stmt2 VARCHAR2(10000);
begin
stmt1:= 'select unique(org_i) from company_X1
where elt_i = ''250''
minus
select unique(org_i) from company_X1
where elt_i = ''265''';
stmt2:= 'select ORG_ELT_ROLE_SEQ_N from company_X2
where elt_i = ''250'' and org_i = '|| '''' || v_org_i || '''' ||'
minus
select ORG_ELT_ROLE_SEQ_N from company_X2
where elt_i = ''265'' and org_i = '|| '''' || v_org_i || '''';
OPEN v_ref_cur1 FOR stmt1;
LOOP
FETCH v_ref_cur1 INTO v_org_i;
EXIT WHEN v_ref_cur1%NOTFOUND;
OPEN v_ref_cur2 FOR stmt2;
LOOP
FETCH v_ref_cur2 INTO v_role_i;
EXIT WHEN v_ref_cur2%NOTFOUND;
dbms_output.put_line(v_rol
end loop;
dbms_output.put_line(v_org
end loop;
commit;
EXCEPTION
WHEN others then
dbms_output.put_line(sqler
end;
##########################
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.