no_data_found error when running a cursor loop

Question

I need to continue my loop whenever any select statement in my cursor loop does not fetch data. I want to handle the exception for the same. This procedure inserts data whenever it finds it in continuity but as soon as o.id selected by the cursor does not hold the related data it exists the loop and only inserts data for the previously fetched records and does not continue the loop.

CREATE OR REPLACE procedure order_violation1(
   p_type          number,
   p_code          varchar2,
   p_submit_from   date,
   p_submit_to     date,
   p_approved_from date,
   p_approved_to   date,
   p_flag          number,
   p_status        varchar2
) is 
  pp_type varchar2(100);
  pp_company varchar2(50);
  pp_code varchar2(20);
  pp_ord_num varchar2(50);
  pp_status varchar2(50);
  SUBMIT_DATE date;
  APPROVAL_DATE date;
  ORDERING_RATIO_FLAG number;
  pp_submit_date date;
  pp_app_date date;
  pp_package varchar2(3000);
  pp_flag NUMBER;

  cursor pp_id is
     select distinct o.id 
       from orders o,
            partnerprofile pp
      where type_id=p_type 
        and o.ordering_ratio_flag=p_flag 
        and pp.id=o.to_partner_id 
        and decode(P_CODE,null,'1',pp.code) = decode(P_CODE,null,'1',p_code)
        and decode(p_submit_from,null, to_date('01/01/01','dd/mm/yy'),
                   to_date(submit_date,'dd/mm/yy')) between 
            decode(p_submit_from ,null,
                   to_date('01/01/01','dd/mm/yy'),p_submit_from)  and 
            decode(p_submit_to,null,to_date('01/01/01','dd/mm/yy'),'05-JUL-14')
        and decode(p_approved_from,null,
                   to_date('01/01/01','dd/mm/yy'),
                   to_date(submit_date,'dd/mm/yy')) between
            decode(p_approved_from,null,
                   to_date('01/01/01','dd/mm/yy'),p_approved_from) and
            decode(p_approved_to,null,to_date('01/01/01','dd/mm/yy'),'05-JUL-14')
        and decode(p_status,null,'1',o.status) = decode(p_status,null,'1',p_status);
begin
  FOR r_partner IN pp_id
  loop
     select name 
       into pp_type 
       from  partnertype 
      where id=p_type;

     select code,
            company_name 
       into pp_code,
            pp_company 
       from partnerprofile pp,
            orders o 
      where o.id=r_partner.id 
        and pp.id=o.to_partner_id;

     select ORDER_NUMBER,
            STATUS,
            SUBMIT_DATE,
            APPROVAL_DATE,
            ORDERING_RATIO_FLAG 
       into pp_ord_num,
            pp_status,
            pp_submit_date,
            pp_app_date,
            pp_flag 
       from orders 
      where id=r_partner.id;

      select distinct
             rtrim (xmlagg (
                            xmlelement (e, pk.name||'='||
                                        nvl(oln.total_amount,0) || '||')
                            ).extract ('//text()'), ',') 
              into pp_package
        from  package pk,
              orderlineitem oln 
        where oln.package_id=pk.id 
          and oln.order_id=r_partner.id 
       GROUP  BY oln.order_id;

     insert into order_violation_tab1 
       values (pp_type, pp_code, pp_company, pp_ord_num,
               pp_status, pp_submit_date, pp_app_date,
               pp_flag, null, null);
     --pp_package);

  END;

Answer 1

As Nicholas pointed out previous method will not work here. You will have to use exception handling inside loop like below to handle this.

 LOOP
     BEGIN
    -- select code
    exception
        when no_data_found then
          continue; 
     END;
     -- insert code
    END LOOP;

Also continue; is feature of 11gr1 and up, for older ones you will have to use goto.

   LOOP
    BEGIN
    -- select code
    exception
        when no_data_found then
          goto label1;
     END;
    -- insert code 
    <<label1>>  
    null;
    END LOOP;