1] Rewrite : One SQL order vs.
Cursor loop
It is obvious that a single
call to the RDBMS engine is better than going back and forth from the PL to the
SQL engine when using cursor.
Example :
FOR v_P1 in
(select no_init_appel_cg,dat,
plage_demi_heure,
sum(duree_conversation+duree_message_usager+duree_message_accueil) as
duree_relation_cso
from FACTS_4760
F where f.sens_appel='ENTRANT'
and
f.no_init_appel_cg is not null
group by
no_init_appel_cg,dat, plage_demi_heure)
LOOP
NB :=
NB+1;
update faits_v2
set
duree_relation_cso=v_P1.duree_relation_cso
where
no_poste_callipso=v_P1.no_init_appel_cg
and dat=v_P1.dat
and plage_demi_heure=v_P1.plage_demi_heure
and no_sda like '041331%';
Commit;
END LOOP;
insert into APP_LOG(TEXT) values('APP_RECUP_4760 : ' || v_cursor || ' Ok : '|| to_char(NB));
commit;
Rewrite to
update
faits_v2 a
set
duree_relation_cso=(
select
sum(duree_conversation+duree_message_usager+duree_message_accueil) as
duree_relation_cso
from
FACTS_4760 b where b.sens_appel='ENTRANT'
and b.no_init_appel_cg is not null
and
a.no_poste_callipso=b.no_init_appel_cg and a.dat=b.dat and
a.plage_demi_heure=b.plage_demi_heure
group
by no_init_appel_cg,dat, plage_demi_heure
)
where
no_sda like '041331%';
Multi-column example
v_cursor:= 'P012';
NB := 0;
-- Maj des infos Postes
FOR v_P1 in
(
select distinct no_poste_callipso ,
t.lib_poste,
t.dsit_nosite,
t.type_tel,
t.stat_accueil,
t.std_identifie,
decode(nvl(t.std_identifie,'Non'),'Oui','ACCUEIL','POSTE')
as Niveau_Accueil,
t.mevo,
t.nb_lignes_entrantes,
t.uo_id,
p.uo_abrev,
p.uo_id_dga,
p.uo_abrev_dga,
p.uo_id_dir,
p.uo_abrev_dir,
p.uo_id_ser,
p.uo_abrev_ser
from bi_app_ods.dsit_telephonev3 t ,uo_public p
where
t.no_poste_callipso is not null
and
nvl(t.uo_id,529)=p.uo_id(+)
)
LOOP
NB := NB+1;
v_cursor:=
'P012a';
update
faits_4760 set
no_poste_callipso=v_P1.no_poste_callipso,
lib_poste=v_P1.lib_poste,
dsit_nosite=v_P1.dsit_nosite,
type_tel=v_P1.type_tel,
stat_accueil=v_P1.stat_accueil,
std_identifie=v_P1.std_identifie,
Niveau_Accueil=v_P1.Niveau_Accueil,
mevo=v_P1.mevo,
nb_lignes_entrantes=v_P1.nb_lignes_entrantes,
uo_id=v_P1.uo_id,
uo_abrev=v_P1.uo_abrev,
uo_id_dga=v_P1.uo_id_dga,
uo_abrev_dga=v_P1.uo_abrev_dga,
uo_id_dir=v_P1.uo_id_dir,
uo_abrev_dir=v_P1.uo_abrev_dir,
uo_id_ser=v_P1.uo_id_ser,
uo_abrev_ser=v_P1.uo_abrev_ser
where
no_poste_callipso=v_P1.no_poste_callipso and lib_poste is null;
Rewrite to
v_cursor:= 'P012a';
update faits_4760_2013_03 F
set(
lib_poste,
dsit_nosite,
type_tel,
stat_accueil,
std_identifie,
Niveau_Accueil,
mevo,
nb_lignes_entrantes,
uo_id
)=
(select
t.lib_poste,
t.dsit_nosite,
t.type_tel,
t.stat_accueil,
t.std_identifie,
decode(nvl(t.std_identifie,'Non'),'Oui','ACCUEIL','POSTE')
as Niveau_Accueil,
t.mevo,
t.nb_lignes_entrantes,
t.uo_id
from bi_app_ods.dsit_telephonev3 t
where
t.no_poste_callipso =f.no_poste_callipso
)
where no_poste_callipso is not null;
Commit;
2] Use DBMS_ERRLOG to help
debugging
One drawback of the previous
rewriting is that failures rollback the entire DML.
We can use the ERRLOG 10g
feature to have more information about the run and errors.
-- test
Update with Reject Management
drop table faits ;
drop table err$_FACTS ;
create table faits ( a int , b int , c NUMBER(7,2) , d varchar2(5) ) ;
insert into faits values ('1','1','1', 'a');
insert into faits values ('2','10','5', 'b');
insert into faits values ('3','20','15', 'c');
insert into faits values ('3','30','40', 'd');
insert into faits values ('3','30','40', 'e');
insert into faits values ('2','40','30', 'f');
alter table faits add constraint faits_check_c check(c >= 0);
exec dbms_errlog.create_error_log( 'FACTS' ) ;
select * from FACTS
A B C D
1 1 1 a
2 10 5 b
3 20 15 c
3 30 40 d
3 30 40 e
2 40 30 f
update faits set c=b-c -- ora-02290 constraint violation
select * from FACTS
A B C D
1 1 1 a
2 10 5 b
3 20 15 c
3 30 40 d
3 30 40 e
2 40 30 f
select * from err$_FACTS -- no rows
update faits set c=b-c LOG ERRORS ('Test Update') REJECT LIMIT UNLIMITED -- 4 rows updated out of 6
select * from FACTS -- 4 updates
A B C D
1 1 0 a
2 10 5 b
3 20 5 c
3 30 40 d
3 30 40 e
2 40 10 f
select * from err$_FACTS -- 2 rows
ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$ OR ORA_ERR_TAG$ A
B C D
---------------
------------------------------ -------------------- -- --------------------
----- ----- ----- -----
2290 ORA-02290: violation de contra
AAAnFHAAdAAJbkUAAD U Test Update 3 30 -10 d
intes (BI_APP_DTM.FACTS_CHECK_
C) de vérification
2290 ORA-02290: violation de contra
AAAnFHAAdAAJbkUAAE U Test Update 3 30 -10 e
intes (BI_APP_DTM.FACTS_CHECK_
C) de vérification
rollback ;
-- reject
limit N : N errors BEFORE Statement fails
update faits set c=b-c LOG ERRORS ('Test Update') REJECT LIMIT 0 -- ora-02290 constraint violation, no updates
update faits set c=b-c LOG ERRORS ('Test Update') REJECT LIMIT 1 -- ora-02290 constraint violation, no updates
select * from FACTS
A B C D
1 1 1 a
2 10 5 b
3 20 15 c
3 30 40 d
3 30 40 e
2 40 30 f
update faits set c=b-c LOG ERRORS ('Test Update') REJECT LIMIT 2 -- 4 rows updated
