Probleme de Compilation lors de l'utilisation BULK COLLECT

Version imprimable

Bonjour à tous,

Je fais appel à vous car j'ai un petit souci avec l'utilisation d'un bulk collect.
Actuellement j'essaie d'optimiser plusieurs procédures qui mettent actuellement des plombes afin d'obtenir les resultats.
Je ferais d'ailleurs surement encore appel à vous pour la procédure la plus longue.

Bref, initialement la requete dans la procédure PL/SQL était formé de la manière suivante : insert into (....,...,...) select.......

Afin de gagner un peu de temps j'ai voulu essayer le bulk collect.
Voici la procédure:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
CREATE OR REPLACE PROCEDURE FILL_DUES (in_reference_date IN DATE)
IS
  TYPE w_TYPE_ID_AFFAIRE is table of lf_dpd_dues.id_affaire%type;
  TYPE w_TYPE_DUE_DATE is table of lf_dpd_dues.due_date%type;
  TYPE w_TYPE_TOTAL_DUE is table of lf_dpd_dues.total_due%type;
  TYPE w_TYPE_SUM_TOTAL_DUE is table of lf_dpd_dues.sum_total_due%type;
  TYPE w_TYPE_AMOUNT_50PERCENT is table of lf_dpd_dues.amount_50_percent%type;  
 
  w_ID_AFFAIRE w_TYPE_ID_AFFAIRE;
  w_DUE_DATE w_TYPE_DUE_DATE;
  w_TOTAL_DUE w_TYPE_TOTAL_DUE;
  w_SUM_TOTAL_DUE w_TYPE_SUM_TOTAL_DUE;
  w_AMOUNT_50PERCENT w_TYPE_AMOUNT_50PERCENT;
 
  v_query  VARCHAR2(10000);	
 
BEGIN
  execute immediate 'truncate table lf_dpd_dues';
 
  v_query := '
  SELECT due.id_affaire AS id_affaire
           , due.due_date AS due_date
           , due.total_due AS total_due
           , due.sum_total_due AS sum_total_due
           , (due.sum_total_due - due.amount_50percent) AS amount_50percent
 FROM
      (SELECT
	id_affaire
	, due_date
	, total_due
	, sum(total_due) over (partition by id_affaire order by due_date) as sum_total_due
          , total_due * 0.5 as amount_50percent
       FROM
	(SELECT
	    cor.id_affaire AS id_affaire                
       	    -- , RB2_FIND_NEXT_BOOKING_DATE(to_date(mvt.date_comptable,''j'')) as due_date
                , to_date(mvt.date_comptable,''j'') AS due_date
	   , SUM(mvt.mt_ttc*s1.signe * -1) AS total_due
	FROM
	       lf_contract_object_retail cor
	      , mvtnew mvt
                   , signefac s1
             WHERE
                    mvt.id_affaire = cor.id_affaire                
                AND mvt.code_statut       = ''2''
	   AND upper(mvt.code_role_tiers) IN (''CLIE'',''RCLI'')
	   AND upper(mvt.code_operation) IN (''FACE'',''FCCE'')
	   AND mvt.mt_ttc*s1.signe * -1 > 0
	   AND to_date(mvt.date_comptable,''j'') <= ''28/02/2009''
                AND s1.code_operation = mvt.code_operation
                AND s1.code_sens_ni   = mvt.code_sens_ni
                AND cor.id_affaire > 0
	GROUP BY
	    cor.id_affaire
	,  to_date(mvt.date_comptable,''j'')
	)
           ) due
       ';
 
       EXECUTE IMMEDIATE v_query BULK COLLECT INTO w_ID_AFFAIRE, w_DUE_DATE, w_TOTAL_DUE, w_SUM_TOTAL_DUE, w_AMOUNT_50PERCENT;
 
       FORALL i IN w_ID_AFFAIRE.FIRST..w_ID_AFFAIRE.LAST
 
          INSERT INTO lf_dpd_dues
             (id_affaire
	, due_date
	, total_due
	, sum_total_due
	, amount_50_percent
	)
           VALUES
            (
             w_ID_AFFAIRE(i)
           , w_DUE_DATE(i)
           , w_TOTAL_DUE(i)
           , w_SUM_TOTAL_DUE(i)
           , w_AMOUNT_50PERCENT(i)
           );
 
COMMIT;
END FILL_DUES;

Le problème est donc tout simplement à la compilation. Ca plante sur le BULK COLLECT.

Error: PLS-00103: Symbole "BULK" rencontré à la place d'un des symboles suivants : . ( * @ % & = - + ; < / > at in mod not rem return returning

Je me suis inspiré pour effectuer cette requete du guide suivant sur le sql dynamique: http://sheikyerbouti.developpez.com/...mmediate/#L4.4

Ma syntaxe semble est identique à celle utilisé pour la cas forall.
Je suis sur du Oracle 8i Edition Entreprise 8.1.4.7.0

Merci d'avance pour vos conseils.

26/03/2009, 15h01
orafrance

Essaye avec un VARCHAR2(256)

La requête fonctionne correctement sinon ?
26/03/2009, 15h16
ericz

Citation:

Envoyé par orafrance

Essaye avec un VARCHAR2(256)

La requête fonctionne correctement sinon ?

Bonjour orafrance.

Le fait de passer un VARCHAR(256) ne corrige pas le problème. J'ai toujours mon erreur de compilation liée au BULK.

Sinon ma requete fonctionne correctement. En l'executant en sql, ca passe nikel. Par contre dans ma procédure PL, je dois la lancer via un execute immediate car sinon il ne reconnait pas les fonction analytiques (over partition by..) sous oracle 8.

Mais quand j'executait la même requete sous la forme :

insert into lf_dpd_dues (id_affaire,..........)
select ... (puis requete du v_query directement)

Je n'avais pas de souci. Ce changement de syntaxe est surtout destiné à optimiser le temps de traitement.
26/03/2009, 15h19
orafrance

Et t'as pas pensé à faire une vue matérialisée plutôt ?
26/03/2009, 15h20
mnitu

De mémoire il n’a pas de BULK en dynamique avant Oracle9. Mais si vous remplacez un Select into … avec un bulk vous allez plutôt perdre du temps.
26/03/2009, 15h28
ericz

Citation:

Envoyé par mnitu

De mémoire il n’a pas de BULK en dynamique avant Oracle9. Mais si vous remplacez un Select into … avec un bulk vous allez plutôt perdre du temps.

Ah bon bah si c'est pour perdre du temps, je vais arreter de me pencher sur cette solution alors.
Pourtant j'avais cru comprendre que le bulk collect permettait de rappatrier en une seule fois l'ensemble des données et ainsi éviter des aller retour couteux entre le moteur PL et le moteur SQL, ce n'est pas ca?

Car cette requete m'insere environ 1.400.000 lignes.

Mais sinon dans une autre procédure j'ai réussit à placer un bulk collect mais ma requete n'etait pas placé dans un varchar. J'execute directement la requete et j'utilise ensuite un for...loop. Ce qu'ici je ne peut faire à cause des over partition by...
26/03/2009, 15h30
orafrance

Encore une fois, regarde du coté des vues matérialisées, ce que tu veux faire ressemble terriblement à ce que les MV proposent :)
26/03/2009, 15h34
ericz

Citation:

Envoyé par orafrance

Et t'as pas pensé à faire une vue matérialisée plutôt ?

Euh je ne connais pas trop ce concept de vue matérialisé. Quel en est le principe?
As tu un ptit lien interresant sur ce sujet?

Mais ma procédure prend en entrée un parametre date, je ne vais pas pouvoir appliquer ma requete pour créer cette vue matérialisé, non?

merci d'avance.
26/03/2009, 15h48
mnitu
Citation:

Envoyé par ericz

...
Pourtant j'avais cru comprendre que le bulk collect permettait de rappatrier en une seule fois l'ensemble des données et ainsi éviter des aller retour couteux entre le moteur PL et le moteur SQL, ce n'est pas ca?

Car cette requete m'insere environ 1.400.000 lignes.

Ce que voues dite est vrai si avant le traitement était dans une boucle de type
Code:

1 2 3 4 5 For crs In (Select ...) Loop insert into ... values (crs.champ1, ...) End Loop;
Mais quand vous faite un Insert Into ... Select from ... combiens des "allez retour couteux" pensez vous qu'il y en a ?

Citation:

...
Mais sinon dans une autre procédure j'ai réussit à placer un bulk collect mais ma requete n'etait pas placé dans un varchar. J'execute directement la requete et j'utilise ensuite un for...loop. Ce qu'ici je ne peut faire à cause des over partition by...

Comme je vous aie dit le problème est le BULK avec sql dynamique en Oracle 8 et non pas les analytyques qu'à la limite vous pouvez les cachez derrière une vue.

Citation:

Envoyé par ericz

Mais sinon dans une autre procédure j'ai réussit à placer un bulk collect mais ma requete n'etait pas placé dans un varchar. J'execute directement la requete et j'utilise ensuite un for...loop. Ce qu'ici je ne peut faire à cause des over partition by...

Est ce qu' écrit comme ça, ça ne marche pas ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
CREATE OR REPLACE PROCEDURE FILL_DUES (in_reference_date IN DATE)
IS
  TYPE w_TYPE_ID_AFFAIRE IS TABLE of lf_dpd_dues.id_affaire%type;
  TYPE w_TYPE_DUE_DATE IS TABLE of lf_dpd_dues.due_date%type;
  TYPE w_TYPE_TOTAL_DUE IS TABLE of lf_dpd_dues.total_due%type;
  TYPE w_TYPE_SUM_TOTAL_DUE IS TABLE of lf_dpd_dues.sum_total_due%type;
  TYPE w_TYPE_AMOUNT_50PERCENT IS TABLE of lf_dpd_dues.amount_50_percent%type;
 
  w_ID_AFFAIRE w_TYPE_ID_AFFAIRE;
  w_DUE_DATE w_TYPE_DUE_DATE;
  w_TOTAL_DUE w_TYPE_TOTAL_DUE;
  w_SUM_TOTAL_DUE w_TYPE_SUM_TOTAL_DUE;
  w_AMOUNT_50PERCENT w_TYPE_AMOUNT_50PERCENT;
 
  v_query  VARCHAR2(10000);	
 
BEGIN
  execute immediate 'truncate table lf_dpd_dues';
 
  v_query := '
  SELECT due.id_affaire AS id_affaire
           , due.due_date AS due_date
           , due.total_due AS total_due
           , due.sum_total_due AS sum_total_due
           , (due.sum_total_due - due.amount_50percent) AS amount_50percent
 FROM
      (SELECT
	id_affaire
	, due_date
	, total_due
	, sum(total_due) over (partition by id_affaire order by due_date) as sum_total_due
          , total_due * 0.5 as amount_50percent
       FROM
	(SELECT
	    cor.id_affaire AS id_affaire                
       	    -- , RB2_FIND_NEXT_BOOKING_DATE(to_date(mvt.date_comptable,''j'')) as due_date
                , to_date(mvt.date_comptable,''j'') AS due_date
	   , SUM(mvt.mt_ttc*s1.signe * -1) AS total_due
	FROM
	       lf_contract_object_retail cor
	      , mvtnew mvt
                   , signefac s1
             WHERE
                    mvt.id_affaire = cor.id_affaire                
                AND mvt.code_statut       = ''2''
	   AND upper(mvt.code_role_tiers) IN (''CLIE'',''RCLI'')
	   AND upper(mvt.code_operation) IN (''FACE'',''FCCE'')
	   AND mvt.mt_ttc*s1.signe * -1 > 0
	   AND to_date(mvt.date_comptable,''j'') <= ''28/02/2009''
                AND s1.code_operation = mvt.code_operation
                AND s1.code_sens_ni   = mvt.code_sens_ni
                AND cor.id_affaire > 0
	GROUP BY
	    cor.id_affaire
	,  to_date(mvt.date_comptable,''j'')
	)
           ) due
       ';
 
 
       open cur for v_query;
       loop
           fetch cur BULK COLLECT into w_ID_AFFAIRE, w_DUE_DATE, w_TOTAL_DUE, w_SUM_TOTAL_DUE, w_AMOUNT_50PERCENT limit 100;
           Exit When cur%NOTFOUND ;
           if (w_ID_AFFAIRE.FIRST is not null) then
           FORALL i IN w_ID_AFFAIRE.FIRST..w_ID_AFFAIRE.LAST
                  INSERT INTO lf_dpd_dues (id_affaire, due_date, total_due, sum_total_due, amount_50_percent)
                  VALUES (w_ID_AFFAIRE(i), w_DUE_DATE(i), w_TOTAL_DUE(i), w_SUM_TOTAL_DUE(i), w_AMOUNT_50PERCENT(i));
           end if;
       end loop;
       close cur;
 
       COMMIT;
 
END FILL_DUES;

Sinon utilise bulk collect pour améliorer les perfs par rapport à un curseur normal, mais insert select est l'écriture la plus performante.

Regarde peut être du côté du hint /*+ append*/ pour bypasser le redo (à valider avec ton dba ;))

26/03/2009, 16h01
mnitu

What's New in PL/SQL et cherchez "Enhancements to Bulk Operations "
26/03/2009, 16h21
ericz

skuatamad j'ai testé ta modification, et ca compile effectivement bien. Par contre à l'execution j'ai un "curseur non valide".

Mais bon à ce que je comprend, cela restera plus performant si je reste sur mon insert..select .. de base.
Il vaut mieux que je continue à optimiser les requetes tout simplement.
26/03/2009, 17h29
orafrance

Citation:

Envoyé par ericz

Euh je ne connais pas trop ce concept de vue matérialisé. Quel en est le principe?
As tu un ptit lien interresant sur ce sujet?

C'est une table qui est rafraichie régulièrement par Oracle avec les données qui résultent d'une requête SQL. Comme une vue mais le résultat est stocké ;)

Citation:

Envoyé par ericz

Mais ma procédure prend en entrée un parametre date, je ne vais pas pouvoir appliquer ma requete pour créer cette vue matérialisé, non?

en effet :?