No data found problem

In the following code I am retreiving a value (finished good item) and check to see if a Bill Of Material has the finished good assigned to it. I then want to print that finished good item number to the screen (ultimately insert it into the interface table).

However when it hits an item that isn't in a BOM the query fails with 'no data found'. How do I get around this so it doesn't break on the error but instead drops into the IF statement and prints the record?


OPEN get_fg (bom_v);
         LOOP
         FETCH get_fg INTO fg_v;
               EXIT WHEN get_fg%NOTFOUND;
-- check to see if fg item exists in BOM
               select distinct segment1 into comp_exist_v
               from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
               where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+)
               and mtl.organization_id = 402
               and comp.bill_sequence_id = bom.bill_sequence_id (+)
               and mtl.segment1 = fg_v;
-- if item is missing from BOM, print to screen              
                         if comp_exist_v != fg_v
                         THEN
                    DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  
                         end if;
 END LOOP;
CLOSE get_fg;
feign3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
OPEN get_fg (bom_v);
        LOOP
        FETCH get_fg INTO fg_v;
              EXIT WHEN get_fg%NOTFOUND;
-- check to see if fg item exists in BOM

         BEGIN
              select distinct segment1 into comp_exist_v
              from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
              where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+)
              and mtl.organization_id = 402
              and comp.bill_sequence_id = bom.bill_sequence_id (+)
              and mtl.segment1 = fg_v;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
               -- if item is missing from BOM, print to screen            
               DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  
       END;
 END LOOP;
CLOSE get_fg;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
catchmeifuwantCommented:
Have an explicit Error Handler for NO_DATA_FOUND

OPEN get_fg (bom_v);
LOOP

FETCH get_fg
INTO fg_v;

EXIT WHEN get_fg%NOTFOUND;

-- check to see if fg item exists in BOM
begin

select distinct segment1
into comp_exist_v
from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+)
and mtl.organization_id = 402
and comp.bill_sequence_id = bom.bill_sequence_id (+)
and mtl.segment1 = fg_v;

exception
when no_data_found then
comp_exists_v := 'Dummy Value';  -------------- Assign some dummay value or do your printing here etc..
end;

-- if item is missing from BOM, print to screen            
if comp_exist_v != fg_v
THEN
DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  
end if;

END LOOP;
CLOSE get_fg;
0
feign3Author Commented:
Hmmm, I tried an exception within the loop but it threw an error when I tried executing it. I'll try it again... maybe my placement was wrong.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Hmmm, I tried an exception within the loop but it threw an error when I tried executing it.

which error?

OPEN get_fg (bom_v);
        LOOP
        FETCH get_fg INTO fg_v;
              EXIT WHEN get_fg%NOTFOUND;
-- check to see if fg item exists in BOM

         BEGIN
              select distinct segment1 into comp_exist_v
              from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
              where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+)
              and mtl.organization_id = 402
              and comp.bill_sequence_id = bom.bill_sequence_id (+)
              and mtl.segment1 = fg_v;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
               -- if item is missing from BOM, print to screen            
               DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  

          WHEN OTHERS THEN
               -- some other error:
               v_error := SQLERRM;
               DBMS_OUTPUT.PUT_LINE(v_error);  
       END;
 END LOOP;
CLOSE get_fg;
0
Harish_RajaniCommented:
Catch the exception 'WHEN NO DATA FOUND' and have an IF statement in the exception block.


OPEN get_fg (bom_v);
        LOOP
        FETCH get_fg INTO fg_v;
              EXIT WHEN get_fg%NOTFOUND;
-- check to see if fg item exists in BOM
              select distinct segment1 into comp_exist_v
              from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
              where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+)
              and mtl.organization_id = 402
              and comp.bill_sequence_id = bom.bill_sequence_id (+)
              and mtl.segment1 = fg_v;
-- if item is missing from BOM, print to screen            
               EXCEPTION WHEN NO DATAFOUND THEN
                         if comp_exist_v != fg_v
                         THEN
                   DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  
                         end if;
                END LOOP;
               END;
-- if there is no exception even then if should execute.
  if comp_exist_v != fg_v
                         THEN
                   DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  
                         end if;
               


 END LOOP;
CLOSE get_fg;


Rgds,
HR
0
MohanKNairCommented:

OPEN get_fg (bom_v);
        LOOP
        FETCH get_fg INTO fg_v;
EXIT WHEN get_fg%NOTFOUND;
-- check to see if fg item exists in BOM
for c11 IN(select distinct segment1 from mtl_system_items_b mtl,APPS.BOM_INVENTORY_COMPONENTS comp,apps.BOM_BILL_OF_MATERIALS bom
where mtl.inventory_item_id = comp.COMPONENT_ITEM_ID (+) and mtl.organization_id = 402 and comp.bill_sequence_id = bom.bill_sequence_id (+) and mtl.segment1 = fg_v)
LOOP
-- if item is missing from BOM, print to screen            
           if c11.segment1 != fg_v
           THEN
     DBMS_OUTPUT.PUT_LINE(bom_v||'    '||'MISSING: '||fg_v);  
           end if;
      END LOOP;
 END LOOP;
CLOSE get_fg;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.