Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Not geting data in second loop?

Posted on 2007-10-19
1
Medium Priority
?
349 Views
Last Modified: 2013-12-19
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;
   
#################################
0
Comment
Question by:KAVINASH1313
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 20112508
the problem is simply that you set up the stmt2 string BEFORE you even open the initial cursor:

#################################
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''';

OPEN v_ref_cur1  FOR stmt1;
    LOOP
        FETCH v_ref_cur1 INTO v_org_i;
        EXIT WHEN v_ref_cur1%NOTFOUND;

        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_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;
   
#################################
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question