Can you advise me in how optimize this batch script :
CREATE OR REPLACE PACKAGE BODY ADMIN_WAFA.PCK_Trait_Pre IS
--------------------------------------------------------------------------------
FUNCTION Fun_Traiter_Prelevement(P_CONTROL_ANO VARCHAR2) RETURN VARCHAR2 IS
-------------------------------------------------------------------------------------------------------------------------
---curseur prelevement-----
CURSOR Prov_Cursor IS
SELECT *
FROM Prov_Prelevement;
---------------------------
Code_Tier NUMBER;
v_sql VARCHAR2(1000);
--------------- ?????????????????????????????? ------------------------------------
BEGIN
/***********************
Calcul des STATS
************************/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ADMIN_WAFA',
TABNAME => 'PROV_PRELEVEMENT',
DEGREE => 8,
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
ESTIMATE_PERCENT => 20);
END;
/************************
Resoudre PB PERF
************************/
-- alter session set optimizer_mode=RULE;
EXECUTE IMMEDIATE ('alter session set optimizer_mode=RULE');
--Début FZ HANOUNOU
IF(P_CONTROL_ANO ='O') THEN
Proc_Test_Anomalie;
END IF;
--Fin FZ HANOUNOU
---------------------------------------------
--Modification par Karim EL HALOUI
--le 15/12/2010
--pour la mise à jour de la vue matérialisée
---------------------------------------------
-- suppression
BEGIN
EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW vue_anomalie_ie_aff');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- création
v_sql:='CREATE MATERIALIZED VIEW vue_anomalie_ie_aff
AS
SELECT a.ano_num_aff,a.ano_ide_reg,a.ano_dat_ech
FROM anomalie a
where a.ano_pret_a>45';
execute immediate v_sql;
------------------ ?????? -------------------
v_sql:='alter FUNCTION F_ECH_ANOM compile';
execute immediate v_sql;
v_sql:='alter package PKG_CTRL_PROMESSE_REG compile body';
execute immediate v_sql;
---------------------------------------------
v_nom_fonction:='Proc_Traiter_Prelevement';
open Prov_cursor;
LOOP
FETCH Prov_cursor INTO Prov_record;
EXIT WHEN Prov_cursor%NOTFOUND OR Prov_cursor%NOTFOUND is NULL;
----------------------------------
-- recherche du code_societe
----------------------------------
select s.code_societe
into v_code_societe
from societes s
where s.lib_societe=prov_record.prov_societe;
---------------------------------------------
IF not Fun_Test_New_aff THEN
IF Fun_Test_New_tier THEN
Code_Tier:=Fun_Ajout_Tier;
ELSE
Code_Tier:=Fun_Cle_Tier;
END IF;
ELSE
Code_Tier:=Fun_Ajout_Tier;
END IF;
Proc_Ajout_Prelevement(Code_Tier);
END LOOP;
execute immediate('truncate table prov_prelevement');
v_nom_fonction:='proc_generation_interne';
pck_ban.proc_generation_interne;
--------------------------------------------------------------------
v_nom_fonction:='proc_saisie_imp_bqe_interne';
proc_saisie_imp_bqe_interne;
v_nom_fonction:='pck_ban.proc_generation_doti';
pck_ban.proc_generation_doti;
update /*+choose*/ prelevement p
set p.pre_dat_ech=to_char(sysdate,'DD/MM/YYYY')
where exists (select null from vacation v
where v.pre_ide_reg=p.pre_ide_reg)
and p.pre_ide_reg<0
and exists (select null from tiers t
where p.tier_cle=t.tier_cle
and t.tier_code_societe=51
and t.tier_nom_agence='REPRCTX')
and p.pre_dat_ech>to_char(sysdate,'DD/MM/YYYY');
------Fin forcage
return ('OK');
--exception
--when others then
--return(v_nom_fonction);
--rollback;
END Fun_Traiter_Prelevement;
FUNCTION FUN_AJOUT_TIER RETURN NUMBER IS
----------------------------------------------------------------------------------------------------------------------------------
Code_Seq NUMBER;
v_temp_tiers number :=0;
BEGIN
v_nom_fonction:='FUN_AJOUT_TIER';
-----------------------------------------
-- recherche s'il s'agit affaire credor ou non
--------------------------------------
select count(*)
into v_temp_tiers
from wdv_aff_credor w
where w.ie_affaire=Prov_record.prov_pre_num_aff;
-----------------
if v_temp_tiers<>0 then
v_provenance:='C';
else
v_provenance:='W';
end if;
if substr(Prov_record.Prov_pre_rib_pay,1,3)='DOT' then
v_matricule:=ltrim(substr(Prov_record.Prov_pre_rib_pay,7),'0');
-- pour la doti on prend tjs les 7 premiers caracteres
v_ref_externe:=substr(Prov_record.prov_pre_num_aff,1,7);
elsif substr(Prov_record.Prov_pre_rib_pay,1,2)='GE' then
v_ref_externe:=Prov_record.prov_pre_num_aff;
v_matricule:=ltrim(substr(Prov_record.Prov_pre_rib_pay,7),'0');
else
v_ref_externe:=Prov_record.prov_pre_num_aff;
end if;
---------------------------------------
-- le cas d'une ancienne affaire
-- est traite dans la function fun_test_new_tiers
----------------------------------------
insert into tiers (tier_cle,tier_mod_reg,tier_nom_pay
,tier_pre_pay,tier_adr_pay
,tier_nom_cli,tier_pre_cli
,tier_dat_pre_ech,tier_dat_der_ech
,tier_pret_k,tier_pret_n
,tier_rib_pay,tier_pret_a,tier_tiers_impute
,tier_nom_agence,tier_code_categ
,tier_code_societe
,tier_ref_externe
,tier_code_provenance
,tier_matricule)
values(Seq_Tier.NEXTVAL,Prov_record.Prov_pre_mod_reg,Prov_record.Prov_pre_nom_pay
,Prov_record.Prov_pre_pre_pay,Prov_record.Prov_pre_adr_pay
,Prov_record.Prov_pre_nom_cli,Prov_record.Prov_pre_pre_cli
,Prov_record.Prov_pre_dat_pre_ech,Prov_record.Prov_pre_dat_der_ech
,Prov_record.Prov_pre_pret_k,Prov_record.Prov_pre_pret_n
,Prov_record.Prov_pre_rib_pay,Prov_record.Prov_pre_pret_a
,Prov_record.prov_pre_tiers_impute,Prov_record.prov_pre_nom_agence
,Prov_record.prov_pre_code_categ
,v_code_societe
,v_ref_externe
,v_provenance
,v_matricule);
select Seq_Tier.currVAL into Code_Seq from dual;
RETURN(Code_Seq);
END FUN_AJOUT_TIER;
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
--)))))))))))))))))))))))))))))))))))))))))))))))))))
--Fonction qui retourne le dernier numero de tiers pour un numero d'affiare
--Modifie par :
--Date Modification :
-------------------------------------------------------------------------------------------------------------------------
FUNCTION FUN_CLE_TIER RETURN NUMBER IS
-------------------------------------------------------------------------------------------------------------------------
Code_Seq NUMBER;
BEGIN
v_nom_fonction:='FUN_CLE_TIER';
SELECT max(tier_cle) into Code_Seq FROM prelevement WHERE pre_num_aff=prov_record.prov_pre_Num_Aff;
RETURN(Code_Seq);
END FUN_CLE_TIER;
FUNCTION FUN_TEST_NEW_AFF RETURN BOOLEAN IS
-------------------------------------------------------------------------------------------------------------------------
v_num NUMBER;
BEGIN
v_nom_fonction:='FUN_TEST_NEW_AFF';
SELECT count(pre_num_aff)
INTO v_num
FROM prelevement
WHERE pre_num_aff=prov_record.prov_pre_num_aff;
IF v_num=0 THEN
v_aff_nouv:=1;
RETURN(TRUE);
ELSE
v_aff_nouv:=0;
RETURN(FALSE);
END IF;
END FUN_TEST_NEW_AFF;
FUNCTION FUN_TEST_NEW_TIER
RETURN BOOLEAN IS
-------------------------------------------------------------------------------------------------------------------------
tier_Chaine varchar2(2000);
Prov_Chaine varchar2(2000);
V_TIER_CLE number(10);
---curseur du tiers------------------------
/*CURSOR tier_cursor (p_cur_pre_num_aff prelevement.pre_num_aff%type) IS
SELECT tier_nom_pay,
tier_pre_pay,
tier_adr_pay,
tier_rib_pay,
tier_pret_a,
tier_tiers_impute,
tier_code_categ,
tier_code_societe,
tier_ref_externe
FROM tiers
WHERE tier_cle = (SELECT max(tier_cle) FROM prelevement WHERE pre_num_aff=p_cur_pre_num_aff);*/
CURSOR tier_cursor (p_tier_cle prelevement.tier_cle%type) IS
SELECT tier_nom_pay,
tier_pre_pay,
tier_adr_pay,
tier_rib_pay,
tier_pret_a,
tier_tiers_impute,
tier_code_categ,
tier_code_societe,
tier_ref_externe
FROM tiers
WHERE tier_cle = p_tier_cle;
tier_record tier_cursor%ROWTYPE;
-------------------------------------------
BEGIN
v_nom_fonction:='FUN_TEST_NEW_TIER ';
---Optimistaion
V_TIER_CLE:=NULL;
SELECT max(tier_cle)
INTO V_TIER_CLE
FROM prelevement
WHERE pre_num_aff=prov_record.prov_pre_num_aff;
---
open tier_cursor(/*prov_record.prov_pre_num_aff,*/V_TIER_CLE);
FETCH tier_cursor INTO tier_record;
--------------------------------------
v_ref_externe:=tier_record.tier_ref_externe;
--------------------------------------
-- test sur = des champs
-- selection de la ligne tiers
tier_Chaine :=rtrim(tier_record.tier_nom_pay)
||rtrim(tier_record.tier_pre_pay)
||rtrim(tier_record.tier_adr_pay)
||rtrim(tier_record.tier_tiers_impute)
||xtrim(tier_record.tier_code_societe)
||rtrim(tier_record.tier_rib_pay)
||xtrim(tier_record.tier_pret_a);
-- selection de la ligne provisoir
Prov_Chaine :=rtrim(Prov_record.Prov_pre_nom_pay)
||rtrim(Prov_record.Prov_pre_pre_pay)
||rtrim(Prov_record.Prov_pre_adr_pay)
||rtrim(Prov_record.prov_pre_tiers_impute)
||xtrim(v_code_societe)
||rtrim(Prov_record.Prov_pre_rib_pay)
||xtrim(Prov_record.Prov_pre_pret_a);
IF Prov_Chaine <> tier_Chaine THEN
RETURN(true);
ELSE
RETURN(False) ;
END IF;
END FUN_TEST_NEW_TIER;
PROCEDURE PROC_AJOUT_PRELEVEMENT (p_tier_cle tiers.tier_cle%type) IS
-------------------------------------------------------------------------------------------------------------------------
Code_Tp_Pre varchar2(1);
CaseText VARCHAR2(2);
v_ban_code varchar2(6);
v_ge_code varchar2(6);
BEGIN
v_nom_fonction:='PROC_AJOUT_PRELEVEMENT';
CaseText:=(UPPER(SUBSTR(Prov_record.prov_pre_rib_pay,1,2)));
IF CaseText='DO'OR CaseText='GE' THEN
Code_Tp_Pre:=SUBSTR(CaseText,1,1);
IF Code_Tp_Pre= 'G' THEN
v_ge_code:=SUBSTR(Prov_record.prov_pre_rib_pay,4,3);
END IF;
ELSE
Code_Tp_Pre:='B';
v_ban_code:=SUBSTR(Prov_record.prov_pre_rib_pay,1,3);
END IF;
---ajout dans la table prelevement
insert into prelevement (TPRE_CODE ,
BAN_CODE,
GE_CODE ,
pre_num_aff,
pre_dat_ech,
tier_cle,
pre_ide_reg,
pre_nbr_ech_tot,
pre_num_ord_ech,
pre_nbr_ech_rest,
pre_aff_nouveau,
statut_libre)--ajout de cette colonne lors PeP du CAM
values (Code_Tp_Pre,
v_ban_code,
v_ge_Code,
Prov_record.prov_pre_num_aff,
Prov_record.prov_pre_dat_ech,
p_tier_cle,
Prov_record.prov_pre_ide_reg,
Prov_record.prov_pre_nbr_ech_tot,
Prov_record.prov_pre_num_ord_ech,
Prov_record.prov_pre_nbr_ech_rest,
v_aff_nouv,
Prov_record.Prov_Afft_Reg);
---ajout dans la table trace prelevement
insert into trace_prelevement (PRE_IDE_REG,ANX_SEQ)
values(Prov_record.PROV_PRE_IDE_REG,Prov_record.PROV_ANX_SEQ);
END PROC_AJOUT_PRELEVEMENT;
PROCEDURE Proc_Test_Anomalie IS
--------------------------------------------------------------------------------------------------------------------------------------------
--curseur controle anomalie---------------------debut
CURSOR Controle_Anomalie_Cursor IS
SELECT *
FROM controle_anomalies_para
where rtrim(CHAMP_TABLE)='PROV_PRELEVEMENT' and rtrim(cont_statut)='EXPL';
Controle_Anomalie_record Controle_Anomalie_Cursor%ROWTYPE;
---------------------------------------------------
sqlTexte varchar2(14000);
V_MODE VARCHAR2(15);
V_VIEW VARCHAR2(1000);
BEGIN
v_nom_fonction:='Fun_Test_Anomalie';
--/*+choose*/
--DEBUT Gestion de MATERIALIZED VIEW
-- suppression
BEGIN
EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW VUE_CDM_PROM_RPAT');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- création
V_VIEW:='CREATE MATERIALIZED VIEW VUE_CDM_PROM_RPAT AS
SELECT /*+choose*/ W.Ie_Affaire,w.pre_ide_reg
FROM WDV_CDM_PROM W
WHERE W.STATUT_PROM IN (''PCRE'', ''PVAL'', ''PENV'', ''PPRE'')
AND W.AFFT_REG IN (''RPAT'', ''IRPT'')
AND NOT EXISTS (SELECT NULL FROM WDV_PROM_EXCLUSION X WHERE X.IE_AFFAIRE = W.IE_AFFAIRE)';
execute immediate V_VIEW;
--FIN Gestion de MATERIALIZED VIEW
open Controle_Anomalie_cursor;
LOOP
FETCH Controle_Anomalie_cursor INTO Controle_Anomalie_record;
EXIT WHEN Controle_Anomalie_cursor%NOTFOUND OR Controle_Anomalie_cursor%NOTFOUND is NULL ;
IF Controle_Anomalie_record.cont_commentaire in ('PROM_RPAT===AFFAIRE A UNE OU PLUSIEURS ECH EN ANOMALIE',
'PROM_RPAT===ENCA NON IMPUTE NON DECAISSE SUR EKIP',
'PROM_RPAT===IMPAYE NON INJECTE DANS EKIP',
'PROM_RPAT===MNT PROM SAISI EST DIFFERENT DU MNT RECALCULE') THEN
V_MODE:='/*+rule*/';
ELSE
V_MODE:='/*+choose*/';
END IF;
sqltexte:= 'INSERT INTO anomalie (ANX_SEQ,tpre_code,ANO_MOD_REG,ANO_IDE_REG,ANO_NUM_AFF,ANO_NOM_PAY,ANO_PRE_PAY,ANO_ADR_PAY
,ANO_NOM_CLI,ANO_PRE_CLI,ANO_DAT_PRE_ECH,ANO_DAT_DER_ECH,ANO_DAT_ECH,ANO_PRET_K,ANO_PRET_N
,ANO_NBR_ECH_TOT,ANO_NUM_ORD_ECH,ANO_NBR_ECH_REST,ANO_RIB_PAY,ANO_PRET_A,ANO_MOTIF,ano_nom_agence
,ano_code_categ,ano_tiers_impute,ano_societe)
select '||V_MODE||' Prov_ANX_SEQ,decode(substr(prov_pre_rib_pay,1,3),''GE '',''G'',''DOT'',''D'',''B''),
prov_pre_mod_reg,prov_pre_ide_reg,prov_pre_num_aff,prov_pre_nom_pay
,prov_pre_pre_pay,prov_pre_adr_pay,prov_pre_nom_cli,prov_pre_pre_cli,prov_pre_dat_pre_ech
,prov_pre_dat_der_ech,prov_pre_dat_ech,prov_pre_pret_k,prov_pre_pret_n,prov_pre_nbr_ech_tot
,prov_pre_num_ord_ech,prov_pre_nbr_ech_rest,prov_pre_rib_pay,prov_pre_pret_a
,'''||rtrim(Controle_Anomalie_record.CONT_COMMENTAIRE)||''',prov_pre_nom_agence,prov_pre_code_categ,prov_pre_tiers_impute,prov_societe
from prov_prelevement '
||' where '||rtrim(Controle_Anomalie_record.CONT_CONDITION);
sqltexte:=rtrim(sqltexte);
EXECUTE IMMEDIATE sqltexte;
sqltexte:='delete '||V_MODE||' prov_prelevement where '|| rtrim(Controle_Anomalie_record.CONT_CONDITION);
EXECUTE IMMEDIATE sqltexte;
end loop ;
-- delete prov_prelevement where (prov_pre_num_aff,prov_pre_dat_ech) in(select ano_num_aff,ano_dat_ech from anomalie);
End proc_Test_Anomalie;
-------------------------------------------------------------------------------------------------------------------------
END;
/
Thanks a lot