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
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_con firm >= 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_d ttm_sale, v_event_date_utc,v_venue_i d,v_genre_ id,v_domai n_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(g cf, 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_r ec.lifetim e_cnt, c1_rec.lifetime_tspo, v_domain_id);
commit;
end loop;
end;
/
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
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_con
and 1.src_created_dttm_confirm
and confirm_dt_dw_id > 0
and cancel_dt_dw_id = -999
group by genre_cat_final, user_id,bob_dw_id,buyer_dw
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_d
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(g
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_r
commit;
end loop;
end;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.