Help with handing "no data found" for a cursor in plsql

gs79
gs79 used Ask the Experts™
on
I have a procedure as shown below..

When ever the data returned for the "into" variables is null, it is throwing out the error and it is coming out of the loop without further inserting the rows.

Incase there is no data found, the exceptional handling should be such a way that it should assign null to all the 'v_' variables and continue to insert records without coming out of the loop..

Please help..
Thanks



Open in new window


create or replace procedure owbruntarget_dw.mer_prc is
v_event_id number;
v_src_created_dttm_sale date;
v_event_date_utc date;
v_venue_id number;
v_genre_id number;
v_domain_id number;
v_object_name varchar2(30);
cursor c1 is
select /*+ index(SPF BUYER_DW_ID_IND) */
user_id,buyer_dw_id,
max (spf.transaction_id) as transaction_id,
genre_cat_final as gcf,
count (1) as lifetime_cnt,
trunc (sum (spf.ticket_cost) / count (1), 2) as lifetime_tspo,
bob_dw_id
from dw_sales_pipeline_fact spf,dw_genre_dim g,dw_users_dim usr
where spf.genre_dw_id = g.genre_dw_id
and spf.buyer_dw_id = usr.user_dw_id
 and exists  (select 1  from dw_sales_pipeline_fact spf1, dw_load_control_tbl l
                       where spf1.buyer_dw_id = spf.buyer_dw_id
                       and l.mapping_name = 'DW_USER_DOM_DIM_MAP'
                       and ( (spf1.src_created_dttm_confirm >= l.start_dttm
                                   and 1.src_created_dttm_confirm < l.end_dttm)))
                                                          and confirm_dt_dw_id > 0
and cancel_dt_dw_id = -999
group by genre_cat_final, user_id,bob_dw_id,buyer_dw_id ;

begin

execute immediate 'truncate table USER_GENRE_SUMRY_DOM_TAB';
for c1_rec in c1
loop

select  /*+ index(F BUYER_DW_ID_IND) */
--F.TRANSACTION_ID,
e.event_id,
f.src_created_dttm_sale,
e.event_date_utc,
e.venue_id,
g.genre_id,
dom.domain_id
into v_event_id,v_src_created_dttm_sale, v_event_date_utc,v_venue_id,v_genre_id,v_domain_id
from dw_sales_pipeline_fact f, dw_events_dim e, dw_genre_dim g, dw_bob_dim bob, dw_events_domain_xref xref, dw_domain_dim dom  
where     f.event_dw_id = e.event_dw_id
and f.genre_dw_id = g.genre_dw_id
and f.bob_dw_id = bob.bob_dw_id
 and bob.domain_id = dom.domain_id
and dom.domain_dw_id = xref.domain_dw_id
 and e.event_dw_id = xref.event_dw_id
and confirm_dt_dw_id > 0
and cancel_dt_dw_id = -999
and f.transaction_id =c1_rec.transaction_id -- 52336086
and f.bob_dw_id = c1_rec.bob_dw_id;--1;

insert into user_genre_sumry_dom_tab(gcf, user_id, transaction_id, event_id, src_created_dttm_sale, event_date_utc, venue_id, genre_id, lifetime_cnt,lifetime_tspo, domain_id)
values (c1_rec.gcf, c1_rec.user_id, c1_rec.transaction_id, v_event_id, v_src_created_dttm_sale,  v_event_date_utc, v_venue_id,v_genre_id,c1_rec.lifetime_cnt, c1_rec.lifetime_tspo, v_domain_id);

commit;

end loop;

end;
/

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
put you select .. into in an exception handling block like

BEGIN
  you select goes here....
EXCEPTION
WHEN NO_DATA_FOUND THEN
    v_event_id:=Null;
    v_src_created_dttm_sale:=Null;
    v_event_date_utc:=Null;
    v_venue_id:=Null;
    v_genre_id:=Null;
    v_domain_id:=Null;
END;
Put your select statement into a nested block with its own exception handler:

begin

select  /*+ index(F BUYER_DW_ID_IND) */
--F.TRANSACTION_ID,
e.event_id,
f.src_created_dttm_sale,
e.event_date_utc,
e.venue_id,
g.genre_id,
dom.domain_id
into v_event_id,v_src_created_dttm_sale, v_event_date_utc,v_venue_id,v_genre_id,v_domain_id
from dw_sales_pipeline_fact f, dw_events_dim e, dw_genre_dim g, dw_bob_dim bob, dw_events_domain_xref xref, dw_domain_dim dom  
where     f.event_dw_id = e.event_dw_id
and f.genre_dw_id = g.genre_dw_id
and f.bob_dw_id = bob.bob_dw_id
 and bob.domain_id = dom.domain_id
and dom.domain_dw_id = xref.domain_dw_id
 and e.event_dw_id = xref.event_dw_id
and confirm_dt_dw_id > 0
and cancel_dt_dw_id = -999
and f.transaction_id =c1_rec.transaction_id -- 52336086
and f.bob_dw_id = c1_rec.bob_dw_id;--1;

exception
  when no_data_found then
      v_event_id := null;

-- repeat for all other variables
end;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial