Link to home
Start Free TrialLog in
Avatar of gs79
gs79

asked on

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

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

ASKER CERTIFIED SOLUTION
Avatar of Yalçın TARKAN
Yalçın TARKAN
Flag of Türkiye 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
SOLUTION
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