Link to home
Start Free TrialLog in
Avatar of KAVINASH1313
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_role_i);  
            end loop;              
         dbms_output.put_line(v_org_i);  
     end loop;
     commit;    

  EXCEPTION
    WHEN others then
    dbms_output.put_line(sqlerrm||sqlcode);
  end;
   
#################################
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial