Erreur ORA-4030 dans Procédure PL/SQL

**nicolas.dron** · 07/01/2009, 17h49

Bonjour,

je génère du code XML via une procédure stockée sur un Oracle 10 g R1 sous linux Red hat.
Lors d'un fecth d'un curseur j'ai un problème mémoire sur un sort des données générant une erreur de type ORA-04030: mémoire de traitement manquante lors d'affectation de 4008 octets (kxs heap,kokeglb: kokegPinLob).

Ci-dessous mon curseur:
Cursor C_FICPT_AG is
(SELECT qry3b.codetare, XMLAgg((resultat3b) order by substr(resultat3b,1,50)).getClobVal() as resultat3 FROM (SELECT rz_f_ficpt_cara_genprel.codetare, (XMLElement(name "FGenPre_Caract_Compteur",
XMLElement(name "NumCarCo", rz_f_ficpt_cara_genprel.numcarco),
XMLElement(name "Car",rz_f_ficpt_cara_genprel.car),
XMLAgg(XMLElement(name "FGenPre_Valeur_Caract_Compteur",
XMLElement(name "NumPriEa", rz_f_ficpt_cara_genprel.numpriea),
XMLElement(name "ValCarCo",rz_f_ficpt_cara_genprel.valcarco)
) order by rz_f_ficpt_cara_genprel.numpriea)
) ) as resultat3b
FROM rz_f_ficpt_cara_genprel, rz_f_genprel
WHERE rz_f_ficpt_cara_genprel.codetare=rz_f_genprel.codetare and rz_f_genprel.cappli='AG' and substr(rz_f_genprel.codetare,1,2)=w_numdep
GROUP BY rz_f_ficpt_cara_genprel.codetare,numcarco,car) qry3b
GROUP BY qry3b.codetare);

sans l'order by en rouge cela fonctionne bien.

La table rz_f_ficpt_cara_genprel contient 97896 rows
La table rz_f_genprel contient 12113 rows

je fetch le résultat en bulk collect dans un tableau interne de type clob.
puis j'insere dans une table temporaire de clob.

ma sort_area est positionnée sur 2 Mo

merci de votre aide

**pifor** · 07/01/2009, 19h02

Il manque de la mémoire au niveau de la machine pour la PGA.
Essayez de diminuer la consommation d'autres services sur la machine et d'augmenter SORT_AREA_SIZE ou d'utiliser PGA_AGGREGATE_TARGET.

**nicolas.dron** · 08/01/2009, 14h00

merci de votre réponse,

La PGA_TARGET a été augmenté à 56 Mo et ça ne suffit pas, tjs la même erreur. le WORKAREA_SIZE_POLICY est bien en AUTO donc on l'utilise.
On l'augmente à 100 Mo

est-ce que ces paramètres sont dynamiques ??
le fait d'augmenter excessivement la PGA_AGGREGATE_TARGET peut il générer d'autre conflit sur la base ?

**pifor** · 08/01/2009, 17h54

Le paramètre PGA_AGGREGATE_TARGET est bien dynamique.

Mais en fait ORA-04030 signifie d'abord qu'il n'y a plus assez de mémoire au niveau Linux. Il faudrait d'abord comprendre pourquoi.

Y-a-t-il une seule instance oracle sur la machine ?
Ou d'autres services (serveur web/serveur d'applications) ?
Quelle est la taille de la mémoire de la machine ?

Que retournent pour chaque instance sur la machine:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
show sga;
select * from v$pgastat where name like '%alloc%';

**nicolas.dron** · 09/01/2009, 11h42

la machine linux est un red hat 4 ES,
la mémoire totale est de 8 Go
il n'y pas de sevice web/client sur la machine
il y a 3 instances actives.
il y a un moteur carto qui prend 1.2 go

voici les sga de chaque instances:
1ere 2.6go
2ème 1.6 go
3ème 800mo

La mémoire libre a été augmentée.
on a suivi le traitement qui prend environ 700 Mo de mémoire physique.

voici l'erreur maintenant dans la trace PL/SQL

ERREUR Ã* la ligne 1 :
ORA-04030: mÃ©moire de traitement manquante lors d'affectation de 22172 octets (PLS non-lib hp,PH1DRV ENGINE SPACE)
ORA-06512: Ã* "FLUXTDECL.RZ_PROC_P_FLUX_GENPREL", ligne 164
->curseur C_ACT_AG

ORA-06512: Ã* "FLUXTDECL.RZ_PROC_P_FLUX_GENPREL", ligne 1144
-> fetch curseur C_ACT_AG

Sans l'order by celà fonctionne...

je vous mets le sql lanceur et la procédure car je pense qu'il y a un problème dans le code.
j'ai enlevé deux autre curseurs qui font le même traitement et la phase de pré insertion des données dans les tables RZ_F....

la structure de la table RES_PREL_TEMP est (varchar2, clob, clob, clob, clob, clob)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SET SERVEROUTPUT ON     SIZE 60000
SET APPINFO OFF
SET HEAD OFF
SET FEED OFF
SET PAGESIZE 0
SET LONG 560
SET LIN 560
 
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT OSERROR
 
-- Connexion Ã* la base
CONNECT &1/&2
 
--TRUNCATE TABLE RZ_F_GENPREL;
 
--TRUNCATE TABLE RZ_F_CPT_GENPREL;
 
--TRUNCATE TABLE RZ_F_FICPT_CARA_GENPREL;
 
--TRUNCATE TABLE RZ_F_ACT_CARA_GENPREL;
 
TRUNCATE TABLE RES_PREL_TEMP;
 
alter session set optimizer_mode = ALL_ROWS;
 
/*test infructueux*/
--alter session set workarea_size_policy = manual;
--alter session set hash_area_size = 20000000;
--alter session set sort_area_size = 20000000;
 
EXECUTE RZ_PROC_P_FLUX_GENPREL ('&3')
 
-- Sortie de Sqlplus
EXIT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
CREATE OR REPLACE PROCEDURE Rz_Proc_P_FLUX_GENPREL (matricule IN varchar2)
IS
BEGIN
 DECLARE
 
 
 TYPE   TYP_TAB_RES1 IS TABLE OF RES_PREL_TEMP.CODETARE%TYPE ;
 TYPE   TYP_TAB_RES2 IS TABLE OF RES_PREL_TEMP.RESULT4%TYPE ;
 
 
 tabrec_AG1 TYP_TAB_RES1;
 tabrec_AG2 TYP_TAB_RES2;
 
 
 -- declaration des variables
 DirFic          VARCHAR2(50);
 DirLog          VARCHAR2(50);
 NomFichier      VARCHAR2(50);
 NomLog          VARCHAR2(50);
 vo_errm         VARCHAR2(256);
 vo_FileDesc     UTL_FILE.FILE_TYPE;
 vo_FileDescLog  UTL_FILE.FILE_TYPE;
 
 -- declaration des variables du fichier
 v_lob_loc            CLOB;
 v_concat_lob_loc     CLOB;
 v_buffer             VARCHAR2(32767);
 v_amount             BINARY_INTEGER;
 v_offset             NUMBER(38) := 1;
 v_out_file           UTL_FILE.FILE_TYPE;
 length               INTEGER;

 -- declaration des variables du xml 
 ligne1           VARCHAR2(200);
 ligne2           VARCHAR2(200);
 ligne3           VARCHAR2(200);
 ligne4           VARCHAR2(200);
 ligne5           VARCHAR2(200);
 ligne6           VARCHAR2(200);
 ligne7           VARCHAR2(200);
 ligne8           VARCHAR2(200);
 ligne9           VARCHAR2(200);
 ligne10          VARCHAR2(200);
 wnumseq_suivi    NUMBER(9);
 wnumseq_err      NUMBER(9);
 w_eta            VARCHAR2(2);
 w_codtypred      VARCHAR2(3);
 
 w_codage     VARCHAR2(2);
 w_numtypfo   VARCHAR2(5);
 w_numfor     NUMBER(3);
 w_libfor     VARCHAR2(50);
 w_anndec     NUMBER(4);
 w_anndec1     NUMBER(4);
 w_anndec2     NUMBER(4);
 w_anndec3     NUMBER(4);
 w_nenv1     NUMBER(3);
 w_nenv2     NUMBER(3);
 w_nenv3     NUMBER(3);
 w_dlimde     VARCHAR2(10);
 w_sysdate    Date;
 w_numdep     VARCHAR2(2);
 w_matricule     VARCHAR2(9);
 
 --Variable en dur pour le flux
 w_nomins  VARCHAR2(60);
 w_refins  VARCHAR2(60);
 w_telins  VARCHAR2(20);
 w_mailins VARCHAR2(60);
 w_usermv VARCHAR2(10);
 -- variables indices
 v_nbrows INTEGER;
 j    INTEGER;
 
 -- declaration des curseurs 
 Cursor  C_DEP_AG is
 --Select distinct substr(codetare,1,2) numdep from rz_f_genprel Where rz_f_genprel.cappli='AG';
 Select distinct codetare mat from rz_f_genprel Where rz_f_genprel.cappli='AG';
 
   Cursor  C_FICPT_AG is 
   (SELECT qry3b.codetare, XMLAgg((resultat3b) order by substr(resultat3b,1,40)).getClobVal() as resultat3
                    FROM (SELECT rz_f_ficpt_cara_genprel.codetare, (XMLElement(name "FGenPre_Caract_Compteur",
                                 XMLElement(name "NumCarCo", rz_f_ficpt_cara_genprel.numcarco),
                                 XMLElement(name "Car",rz_f_ficpt_cara_genprel.car),
                                          XMLAgg(XMLElement(name "FGenPre_Valeur_Caract_Compteur",
                                                   XMLElement(name "NumPriEa", rz_f_ficpt_cara_genprel.numpriea),
                                                   XMLElement(name "ValCarCo",rz_f_ficpt_cara_genprel.valcarco)
                    ) order by rz_f_ficpt_cara_genprel.numpriea)
                    ) 
                           ) as resultat3b
                    FROM rz_f_ficpt_cara_genprel, rz_f_genprel
					WHERE rz_f_ficpt_cara_genprel.codetare=rz_f_genprel.codetare and rz_f_genprel.cappli='AG' and
					/*substr(rz_f_genprel.codetare,1,2)=w_numdep*/
					 rz_f_genprel.codetare=w_matricule
                    GROUP BY rz_f_ficpt_cara_genprel.codetare,numcarco,car) qry3b
                    GROUP BY qry3b.codetare);

    
  Cursor  C_ACT_AG is
  (SELECT qry4b.codetare, XMLAgg((resultat4b) order by substr(resultat4b,1,40)).getClobVal() as resultat4 
	             FROM (SELECT rz_f_act_cara_genprel.codetare, (XMLElement(name "FGenPre_Caract_Activite",
                                 XMLElement(name "NumCarAc", rz_f_act_cara_genprel.numcarac),
                                 XMLElement(name "Car",rz_f_act_cara_genprel.car),
                                 XMLAgg(XMLElement(name "FGenPre_Valeur_Caract_Activite",
                                 XMLElement(name "NumPriEa", rz_f_act_cara_genprel.numpriea),
                                 XMLElement(name "ValCarAc",rz_f_act_cara_genprel.valcarac)
								 ) order by rz_f_act_cara_genprel.numpriea)
								 )
                           ) as Resultat4b
                     FROM rz_f_act_cara_genprel,rz_f_genprel  
                     WHERE rz_f_act_cara_genprel.codetare=rz_f_genprel.codetare and rz_f_genprel.cappli='AG' and
					 /*substr(rz_f_genprel.codetare,1,2)=w_numdep*/
					  rz_f_genprel.codetare=w_matricule
					 GROUP BY rz_f_act_cara_genprel.codetare,numcarac,car) qry4b
                     GROUP BY qry4b.codetare);
	                 
   
    
     
   CURSOR cur_flux_genprel IS
   SELECT '<FormulaireGeneralPrelevement>'||result1||result2||result3||result4||result5||'</FormulaireGeneralPrelevement>' as resultat0
   FROM RES_PREL_TEMP
   WHERE
   RES_PREL_TEMP.codetare = NVL(matricule, RES_PREL_TEMP.codetare);
   
BEGIN
 DirFic        := '/home/exploit/FLUXTELEDECLA/XML/';
 DirLog        := '/home/exploit/FLUXTELEDECLA/';
 NomFichier    := 'F18_FGenPre.xml';
 NomLog        := 'RZ_PROC_P_FLUX_GENPREL.LOG';

  -- initialisation des variables du xml 
 ligne1 := '<?xml version="1.0" encoding="UTF-8"?>';
 ligne2 := '<!--Sample XML file generated by XMLSpy v2008 sp1 (http://www.altova.com)-->';
 ligne3 := '<FORMULAIRES_FGenPre xsi:noNamespaceSchemaLocation="F18_FGenPre.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
 ligne4 := '<Agence><CodAge>';
 ligne5 := '</CodAge></Agence>';
 ligne6 := '<Formulaire><AnnRed>';
 ligne7 := '</AnnRed>';
 ligne8 := '<DatLimDe>';
 ligne9 := '</DatLimDe>';
 ligne10 := '</Formulaire></FORMULAIRES_FGenPre>'; 
 
 wnumseq_suivi    := 0;
 wnumseq_err      := 0;
 w_eta            := 'OK';
 w_numfor         := 18;
 w_mailins        := 'service.dr@eau-loire-bretagne.fr';
 
 -- Ouverture du fichier LOG
 --
 BEGIN
   vo_FileDescLog := UTL_FILE.FOPEN (  DirLog,NomLog,'A');
   EXCEPTION
     WHEN UTL_FILE.INVALID_PATH THEN
       UTL_FILE.put_line (vo_FileDescLog,'Nom de repertoire ou emplacement invalide : ' ||DirLog||NomLog||'.');
     WHEN UTL_FILE.INVALID_MODE THEN
       UTL_FILE.put_line (vo_FileDescLog,'Mode de gestion du fichier ' ||DirLog||NomLog||' invalide.');
     WHEN UTL_FILE.INVALID_OPERATION THEN
       UTL_FILE.put_line (vo_FileDescLog,'Le fichier ' ||DirLog||NomLog||' ne peut pas etre ouvert.');
     WHEN OTHERS THEN
       vo_errm := SQLERRM;
       UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ouverture du fichier ' ||DirLog||NomLog||' : ' || vo_errm);
 END;
 
 -- Recuperation de la date et heure du jour  
 SELECT sysdate INTO w_sysdate
 FROM dual;

 --  Récupération du code Agence
 BEGIN
   SELECT CodAge INTO w_codage FROM RZ_R_AGE;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
     w_codage:='';
   WHEN OTHERS THEN
     w_eta := 'KO';  
     vo_errm := SQLERRM;
     SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
     INSERT INTO RZ_W_ERREUR (NumSeqErr, RefFlux, DatErr, RefClef, CodErr, DesErr) 
       VALUES (wnumseq_err, NomFichier, w_sysdate, null, 'Lecture RZ_R_AGE', vo_errm);
     COMMIT;  
 END; 
    
 ----------------------------------------------------------------------------------------------------------------------
 --
 -- Création du fichier Xml                       
 --
 BEGIN
    NomFichier := w_codage||'_'||NomFichier;
    v_out_file := UTL_FILE.FOPEN(DirFic,NomFichier,'W',32767);
 
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
      UTL_FILE.put_line (vo_FileDescLog,'Nom de repertoire ou emplacement invalide : ' ||DirFic||NomFichier||'.');
    WHEN UTL_FILE.INVALID_MODE THEN
      UTL_FILE.put_line (vo_FileDescLog,'Mode de gestion du fichier ' ||DirFic||NomFichier||' invalide.');
    WHEN UTL_FILE.INVALID_OPERATION THEN
      UTL_FILE.put_line (vo_FileDescLog,'Le fichier ' ||DirFic||NomFichier||' ne peut pas etre ouvert.');
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    WHEN OTHERS THEN
      vo_errm := SQLERRM;
    UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ouverture du fichier ' ||DirFic||NomFichier||' : ' || vo_errm);
 END;
 
 --
 ----------------------------------------------------------------------------------------------------------------------
 --*************************************
 -- Ecriture du début de suivi du flux
 --*************************************
 BEGIN
  --log
  BEGIN 
    UTL_FILE.put_line (vo_FileDescLog,'Debut Flux Xml FGenPrel');
    EXCEPTION
      WHEN OTHERS THEN
        w_eta := 'KO';
        vo_errm := SQLERRM;
        UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ecriture du fichier Xml' ||DirFic||NomFichier||' : ' || vo_errm);
  END;
  
  -- table de suvi
  BEGIN
    SELECT id_w_suivi.NEXTVAL INTO wnumseq_suivi FROM dual;
    EXCEPTION
      WHEN OTHERS THEN
        w_eta := 'KO';  
        vo_errm := SQLERRM;
        UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la lecture de la sequence SUIV' ||DirFic||NomFichier||' : ' || vo_errm);
  END;
 
  BEGIN
    INSERT INTO RZ_W_SUIVI (IdSuiv,RefFlux,DatDeb,DatFin,CodErr,CodEta) 
      VALUES (wnumseq_suivi,NomFichier,w_sysdate,null,null,w_eta);
    EXCEPTION
       WHEN OTHERS THEN
         w_eta := 'KO';  
         vo_errm := SQLERRM;
         UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''insertion au suivi' ||DirFic||NomFichier||' : ' || vo_errm);
  END;
  COMMIT;
 END;

 --*****************************  
 -- Récupération du code agence                   
 --*****************************  
 BEGIN
   --  Récupération du type et code formulaire
   BEGIN
     SELECT NumTypFo,libFor INTO w_numtypfo,w_libfor FROM RZ_R_FORMULAIRE where numfor=w_numfor;
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
       w_numtypfo:='';
    w_libfor:='';
     WHEN OTHERS THEN
       w_eta := 'KO';  
       vo_errm := SQLERRM;
       SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
       INSERT INTO RZ_W_ERREUR (NumSeqErr, RefFlux, DatErr, RefClef, CodErr, DesErr) 
         VALUES (wnumseq_err, NomFichier, w_sysdate, null, 'Lecture RZ_R_FORMULAIRE', vo_errm);
       COMMIT;  
   END; 
 
 --***************************************************  
 -- Alimentation de la table de référence RZ_R_REFINS  
 --*************************************************** 
 
 BEGIN
   -- Suppression des données en cours sur RZ_R_REFINS 
   BEGIN
     DELETE FROM RZ_R_REFINS WHERE CodTypRed IN ('AR', 'IR', 'CR');
     EXCEPTION
       WHEN OTHERS THEN
         w_eta := 'KO';  
         vo_errm := SQLERRM;
         SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
         INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
           VALUES (wnumseq_err, NomFichier,w_sysdate,NULL, 'Delete RZ_R_REFINS', vo_errm);  
         COMMIT;
   END;
   
   -- Insertion des données en cours sur RZ_R_REFINS pour l'application AR 
   BEGIN
     w_usermv := 'DRVCSM';
  w_codtypred := 'AR';
     INSERT INTO RZ_R_REFINS (CodTypRed,NomIns,RefIns,TelIns,MailIns) 
    (SELECT w_codtypred,substr(nom,1,15),null,'02.'||tel, w_mailins FROM RARSECT WHERE usermv=w_usermv);
     EXCEPTION
     WHEN OTHERS THEN
       w_eta := 'KO';  
       vo_errm := SQLERRM;
       SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
       INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
         VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RARSECT RZ_R_REFINS',vo_errm);  
       COMMIT;
   END;
   
   -- Insertion des données en cours sur RZ_R_REFINS pour l'application AR 
   BEGIN
     w_usermv := 'PI1';
     w_codtypred := 'IR';
     INSERT INTO RZ_R_REFINS (CodTypRed,NomIns,RefIns,TelIns,MailIns) 
    (SELECT w_codtypred, substr(nom,1,15),NULL, '02.'||tel, w_mailins FROM RIRSECT WHERE usermv=w_usermv);
     EXCEPTION
     WHEN OTHERS THEN
       w_eta := 'KO';  
       vo_errm := SQLERRM;
       SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
       INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
         VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RIRSECT RZ_R_REFINS',vo_errm);  
       COMMIT;
   END;
   
   -- Insertion des données en cours sur RZ_R_REFINS pour l'application IR 
   BEGIN
     w_usermv := 'DRVBDT';
     w_codtypred := 'CR';
     INSERT INTO RZ_R_REFINS (CodTypRed,NomIns,RefIns,TelIns,MailIns) 
    (SELECT w_codtypred, substr(nom,1,15),null, '02.'||tel, w_mailins FROM RCRSECT WHERE usermv=w_usermv);
     EXCEPTION
     WHEN OTHERS THEN
       w_eta := 'KO';  
       vo_errm := SQLERRM;
       SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
       INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
         VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RCRSECT RZ_R_REFINS ',vo_errm);  
       COMMIT;
   END;
   COMMIT;  
 END;
 
 -- recherche de l'année active
 SELECT MAX(rdannee) INTO w_anndec1 FROM rrdtpre WHERE ccatusa = 'AG' AND cinit = '1';
 SELECT MAX(rdannee) INTO w_anndec2 FROM rrdtpre WHERE ccatusa = 'IN' AND cinit = '1';
 SELECT MAX(rdannee) INTO w_anndec3 FROM rrdtpre WHERE ccatusa = 'CL' AND cinit = '1';
 
 --  Récupération du numéro d'envoi max
 SELECT MAX(nenv) INTO w_nenv1 FROM rarsuiv WHERE rdannee=w_anndec1 AND nenv<>9;
 SELECT MAX(nenv) INTO w_nenv2 FROM rirsuiv WHERE rdannee=w_anndec2 AND nenv<>9;
 SELECT MAX(nenv) INTO w_nenv3 FROM rcrsuiv WHERE rdannee=w_anndec3 AND nenv<>9;
 
 --  Récupération de la date limite de retour des déclarations   
 BEGIN     
   SELECT TO_CHAR(MAX(dretexig), 'YYYY-MM-DD')
  INTO w_dlimde
   FROM rarusag a, rarsuiv b
   WHERE a.rdannee  = w_anndec1
  AND a.rdannee  = b.rdannee 
  AND a.matusa  = b.matusa 
  AND a.bcalc    = 'DE'
  AND b.denvdecl IS NOT NULL 
  AND b.dretde   IS NULL 
  AND b.nenv     = w_nenv1; 
   EXCEPTION
      WHEN OTHERS THEN
       w_eta := 'KO';  
       vo_errm := SQLERRM;
       Select id_w_erreur.nextval into wnumseq_err from dual;
    INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
    VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Lecture RARSUIV',vo_errm);
    COMMIT;      
 END;
   
 
 
  BEGIN
   --*********************************************************************-- 
   -- Requete alimentation de la table tempo de stockage des flux GENPREL -- 
   --*********************************************************************--

   BEGIN
   -- insertion resultat 1
     INSERT INTO RES_PREL_TEMP (CAPPLI,CODETARE, RESULT1)
      (SELECT cappli, codetare, XMLElement(name "Formulaire_Entete",
                                   XMLElement(name "CodEtaRe", rz_f_genprel.codetaco),
                                   XMLElement(name "CodEtaCo", rz_f_genprel.codetare),
                                   XMLElement(name "CodBar", rz_f_genprel.codbar),
                                   XMLElement(name "NomIns", rz_f_genprel.nomins),
                                   XMLElement(name "RefIns", rz_f_genprel.refins),
                                   XMLElement(name "TelIns", rz_f_genprel.telins),
                                   XMLElement(name "MaiIns", rz_f_genprel.mailins),
                                   XMLElement(name "TxtLibDe", rz_f_genprel.txtlibde)
             ).getClobVal() as Resultat1 
       FROM rz_f_genprel);
	   /*ORDER BY codetare;*/
	   EXCEPTION
       WHEN OTHERS THEN
          w_eta := 'KO';  
          vo_errm := SQLERRM;
          SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
          INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
          VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Insert RES_PREL_TEMP',vo_errm);  
          COMMIT;
	END;
	COMMIT;

		-- insertion resultat 2
	BEGIN

    UPDATE RES_PREL_TEMP alias set 
	RESULT2 = 	(SELECT qry2.resultat2 
	             FROM
	               (SELECT codetare, XMLAgg(XMLElement(name "FGenPre_Prise_Eau",
                                 XMLElement(name "NumPriEa", rz_f_cpt_genprel.numpriea),
                                 XMLElement(name "RefAgeCo", rz_f_cpt_genprel.refageco),
                                 XMLElement(name "DesOu", rz_f_cpt_genprel.desou),
                                 XMLElement(name "ComOu", rz_f_cpt_genprel.comou),
                                 XMLElement(name "CodNatRe", rz_f_cpt_genprel.codnatre),
                                 XMLElement(name "CodUsa1", rz_f_cpt_genprel.codusa1),
                                 XMLElement(name "PctUsa1", 100),
                                 XMLElement(name "IndComDa", rz_f_cpt_genprel.indcomda)
                          ) order by rz_f_cpt_genprel.numpriea
                  ).getClobVal() as Resultat2
                  FROM rz_f_cpt_genprel GROUP BY codetare) qry2
	              WHERE alias.codetare=qry2.codetare)
    WHERE ROWID IN
    (SELECT RES_PREL_TEMP.ROWID
    FROM RES_PREL_TEMP, (SELECT codetare, XMLAgg(XMLElement(name "FGenPre_Prise_Eau",
                                 XMLElement(name "NumPriEa", rz_f_cpt_genprel.numpriea),
                                 XMLElement(name "RefAgeCo", rz_f_cpt_genprel.refageco),
                                 XMLElement(name "DesOu", rz_f_cpt_genprel.desou),
                                 XMLElement(name "ComOu", rz_f_cpt_genprel.comou),
                                 XMLElement(name "CodNatRe", rz_f_cpt_genprel.codnatre),
                                 XMLElement(name "CodUsa1", rz_f_cpt_genprel.codusa1),
                                 XMLElement(name "PctUsa1", 100),
                                 XMLElement(name "IndComDa", rz_f_cpt_genprel.indcomda)
                          ) order by rz_f_cpt_genprel.numpriea
                  ).getClobVal() as Resultat2
                  FROM rz_f_cpt_genprel GROUP BY codetare) qry2  
    WHERE
    RES_PREL_TEMP.codetare=qry2.codetare);
	EXCEPTION
    WHEN OTHERS THEN
          w_eta := 'KO';  
          vo_errm := SQLERRM;
          SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
    INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
    VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD1 RES_PREL_TEMP',vo_errm);  
    COMMIT;
	END;
	COMMIT;  

	BEGIN
	-- insertion resultat 5
    
	UPDATE RES_PREL_TEMP alias set 
	RESULT5 =	(SELECT XMLElement(name "Formulaire_Piece_Jointe",
                                   XMLElement(name "NumOrdPi", rz_f_genprel.numordpi),
                                   XMLElement(name "TypPieJo", rz_f_genprel.typpiejo),
                                   XMLElement(name "NomPieJo", rz_f_genprel.nompiejo)
                   ).getClobVal() as resultat5 
                   FROM rz_f_genprel where rownum='1');
	EXCEPTION
    WHEN OTHERS THEN
          w_eta := 'KO';  
          vo_errm := SQLERRM;
          SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
    INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
    VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD5 RES_PREL_TEMP',vo_errm);  
    COMMIT;
	END;
	COMMIT;
   
   	    -- insertion resultat 3
	BEGIN

	   	-- utilisation du curseur --
		FOR i IN C_DEP_AG LOOP
	      --w_numdep := i.numdep;
		  w_matricule := i.mat;
		-- utilisation du curseur --
		  Open C_FICPT_AG;
           BEGIN		  
		    Fetch C_FICPT_AG BULK COLLECT Into tabrec_AG1,tabrec_AG2;
		    EXCEPTION
            WHEN OTHERS THEN
              w_eta := 'KO';  
              vo_errm := SQLERRM;
              SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
              INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
              VALUES (wnumseq_err,NomFichier,w_sysdate,null,'FETCH1 RES_PREL_TEMP',vo_errm);  
		      Commit;
			  EXIT;
		   END;
		  Close C_FICPT_AG; 
        
		  -- insertion de lignes à partir de la collection AG--
		  BEGIN
          Forall i in tabrec_AG1.first..tabrec_AG1.last
            update RES_PREL_TEMP set result3 = tabrec_AG2(i) where  RES_PREL_TEMP.codetare=tabrec_AG1(i) and RES_PREL_TEMP.cappli='AG';
			EXCEPTION
            WHEN OTHERS THEN
              w_eta := 'KO';  
              vo_errm := SQLERRM;
              SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
              INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
              VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD2-1 RES_PREL_TEMP',vo_errm);  
		    Commit;
          END;
		  COMMIT;
		END LOOP;
		
		
	
	-- insertion resultat 4
	BEGIN
		
	   	-- utilisation du curseur --
		FOR i IN C_DEP_AG LOOP
	      --w_numdep := i.numdep;
		  w_matricule := i.mat;
		  
		-- utilisation du curseur --
		  Open C_ACT_AG;
		  BEGIN
		  Fetch C_ACT_AG BULK COLLECT Into tabrec_AG1,tabrec_AG2;
		  EXCEPTION
            WHEN OTHERS THEN
              w_eta := 'KO';  
              vo_errm := SQLERRM;
              SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
              INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
              VALUES (wnumseq_err,NomFichier,w_sysdate,null,'FETCH4 RES_PREL_TEMP',vo_errm);  
		      Commit;
			  EXIT;
		  END;
		  Close C_ACT_AG; 
        
		  -- insertion de lignes à partir de la collection AG--
		  BEGIN
          Forall i in tabrec_AG1.first..tabrec_AG1.last
            update RES_PREL_TEMP set result4 = tabrec_AG2(i) where  RES_PREL_TEMP.codetare=tabrec_AG1(i) and RES_PREL_TEMP.cappli='AG';
		    EXCEPTION
            WHEN OTHERS THEN
              w_eta := 'KO';  
              vo_errm := SQLERRM;
              SELECT id_w_erreur.NEXTVAL INTO wnumseq_err FROM dual;
              INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
              VALUES (wnumseq_err,NomFichier,w_sysdate,null,'UPD3-1 RES_PREL_TEMP',vo_errm);  
		    Commit;
          END;
		  COMMIT;
		END LOOP;

 END;
 COMMIT;
 
 --**************************************
 -- Requete alimentation du flux GenPrel 
 --**************************************
 BEGIN
   BEGIN
    v_nbrows:=0;
    /*FOR i IN cur_flux_genprel LOOP
      v_concat_lob_loc:=v_concat_lob_loc||i.resultat0;
      v_nbrows:=v_nbrows+1 ;   
    END LOOP;*/
 
    v_lob_loc:=ligne1||ligne2||ligne3||ligne4||w_codage||ligne5||ligne6||w_anndec1||ligne7||ligne8||w_dlimde||ligne9||v_concat_lob_loc||ligne10;
         
    EXCEPTION
    WHEN OTHERS THEN
       w_eta := 'KO';  
       vo_errm := SQLERRM;
       Select id_w_erreur.nextval into wnumseq_err from dual;
    INSERT INTO RZ_W_ERREUR (NumSeqErr,RefFlux,DatErr,RefClef,CodErr,DesErr) 
      VALUES (wnumseq_err,NomFichier,w_sysdate,null,'Lecture cur_flux_genprel',vo_errm);
    COMMIT;     
   END;

 END;

 v_amount := 32766;
 v_offset:=1;
 -- +-------------------------------------------------------------+
 -- | OPENING THE LOB IS OPTIONAL                                 |
 -- +-------------------------------------------------------------+
 BEGIN
   DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);
   EXCEPTION
   WHEN OTHERS THEN
     vo_errm := SQLERRM;
     UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ouverture du LOB' ||DirFic||NomFichier||' : ' || vo_errm);
 END;
 
 BEGIN
   length := dbms_lob.getlength(v_lob_loc);
   IF length IS NULL THEN
     UTL_FILE.put_line (vo_FileDescLog,'LOB NULL' ||DirFic||NomFichier||' : ' || vo_errm);
   ELSE
     UTL_FILE.put_line (vo_FileDescLog,'Taille du LOB Nb occurence XML'||DirFic||NomFichier||' : ' || length ||' '||v_nbrows);
   END IF;
 END;

 -- +-------------------------------------------------------------+
 -- | WRITE CONTENTS OF THE LOB TO A FILE                         |
 -- +-------------------------------------------------------------+
 BEGIN
   j:=1;
   LOOP
     BEGIN
       DBMS_LOB.READ(lob_loc    => v_lob_loc,
                     amount     => v_amount,
                     offset     => v_offset,
                     buffer     => v_buffer);
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
         EXIT;
       WHEN OTHERS THEN
         vo_errm := SQLERRM;
         UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la lecture du LOB' ||DirFic||NomFichier||' : ' || vo_errm);
         EXIT;
  END;
     
     v_offset := v_offset + v_amount;
  
  
     BEGIN
    UTL_FILE.PUT_LINE(file => v_out_file,
                    buffer => v_buffer);
    
       EXCEPTION
         WHEN OTHERS THEN
           vo_errm := SQLERRM;
           UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de l''ecriture du fichier ' ||DirFic||NomFichier||' : ' || vo_errm);
     END;
  
     BEGIN
       UTL_FILE.FFLUSH(file => v_out_file);
       EXCEPTION
         WHEN OTHERS THEN
           vo_errm := SQLERRM;
           UTL_FILE.put_line (vo_FileDescLog,'Erreur lors du fflush1 du fichier ' ||DirFic||NomFichier||' : ' || vo_errm);
     END;
     j:=j+v_amount;
     EXIT WHEN j>=length;
   END LOOP;
 
   BEGIN
     UTL_FILE.FFLUSH(file => v_out_file);
     EXCEPTION
       WHEN OTHERS THEN
         vo_errm := SQLERRM;
         UTL_FILE.put_line (vo_FileDescLog,'Erreur lors du fflush2 du fichier ' ||DirFic||NomFichier||' : ' || vo_errm);
   END;
 
   BEGIN
     UTL_FILE.FCLOSE(v_out_file);
     EXCEPTION
       WHEN UTL_FILE.INVALID_PATH THEN
         UTL_FILE.put_line (vo_FileDescLog,'Nom de repertoire ou emplacement invalide : ' ||DirFic||NomFichier||'.');
       WHEN UTL_FILE.INVALID_MODE THEN
         UTL_FILE.put_line (vo_FileDescLog,'Mode de gestion du fichier ' ||DirFic||NomFichier||' invalide.');
       WHEN UTL_FILE.INVALID_OPERATION THEN
         UTL_FILE.put_line (vo_FileDescLog,'Le fichier ' ||DirFic||NomFichier||' ne peut pas etre ferme.');
       WHEN OTHERS THEN
         vo_errm := SQLERRM;
         UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la fermeture du fichier ' ||DirFic||NomFichier||' : ' || vo_errm);
   END;
 
   -- +-------------------------------------------------------------+
   -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
   -- +-------------------------------------------------------------+
   BEGIN
     DBMS_LOB.CLOSE(v_lob_loc);
     EXCEPTION
       WHEN OTHERS THEN
         vo_errm := SQLERRM;
         UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la fermeture du LOB ' ||DirFic||NomFichier||' : ' || vo_errm);
   END;
 END;
  
 -- Cloture au suivi 
 BEGIN
   IF w_eta='OK' THEN
     vo_errm :=NomFichier||' longueur:'||length||' Nb lignes:'||v_nbrows;
   END IF;
   UPDATE RZ_W_SUIVI SET DatFin=sysdate,CodErr=vo_errm,CodEta=w_eta WHERE idsuiv=wnumseq_suivi; 
   EXCEPTION
     WHEN OTHERS THEN
       vo_errm := SQLERRM;
       UTL_FILE.put_line (vo_FileDescLog,'Erreur lors de la mise à jour au suivi' ||DirFic||NomFichier||' : ' || vo_errm);
 END;
 COMMIT; 
 
 -- Fermeture du fichier LOG 
 BEGIN
   UTL_FILE.fclose (vo_FileDescLog);
   EXCEPTION
   WHEN OTHERS THEN
     RETURN;
 END;
 --
END;

END Rz_Proc_P_FLUX_GENPREL;
/

**pifor** · 09/01/2009, 12h00

Il faudrait avoir le résultat de la requête sur PGASTAT car la mémoire utilisée par une instance c'est la SGA (en général fixe) + la PGA (variable et qui peut dépasser PGA_AGGREGATE_TARGET). La PGA est utilisée pour les variables PL/SQL (en particulier pour les collections PL/SQL) et pour trier les résultats des requêtes ou pour faire des jointures.

Si on veut limiter la consommation mémoire, il faut limiter l'utilisation des variables PL/SQL ou modifier les requêtes. Vous pouvez aussi essayer de réduire PGA_AGGREGATE_TARGET mais ceci ne peut pas jouer sur la partie PL/SQL et risque de ralentir considérablement les requêtes qui vont utiliser plus d'espace dans le tablespace temporaire (attention à la saturation du tablespace temp. dans ce cas).

**nicolas.dron** · 09/01/2009, 13h39

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
résultat de la requête sur PGASTAT 
 
aggregate PGA target parameter                                    536870912 bytes
aggregate PGA auto target                                         431603712 bytes
global memory bound                                                26843136 bytes
total PGA inuse                                                    57303040 bytes
total PGA allocated                                               129869824 bytes
maximum PGA allocated                                             895769600 bytes
total freeable PGA memory                                          18808832 bytes
PGA memory freed back to OS                                      1.6334E+10 bytes
total PGA used for auto workareas                                         0 bytes
maximum PGA used for auto workareas                                21721088 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                               8463360 bytes
over allocation count                                                126587
 
bytes processed                                                  5.5256E+10 bytes
extra bytes read/written                                         1.4603E+10 bytes
cache hit percentage                                                  79.09 percent

.

ce que je n'explique pas c'est que sans l'order by sur le XmlAgg() ça fonctionne.
L'order d'une chaine Xml via un substr d'une chaine de caractère qui fait environ 2000 cara est il très gourmand en mémoire ??

Peut être le fetch dans un tableau de clob n'est pas approprié !

**nicolas.dron** · 09/01/2009, 18h07

je me réponds à moi même ....

Le fetch dans un tableau de clob n'est pas approprié.
consommation énorme de mémoire sur le serveur.

pour résoudre le pb j'ai enlevé le Xmlagg() de mon résultat 3b et j'ai fait un fetch tous simple dans un clob puis j'ai pipé ce clob .
mis à jour ma table de clob.

bilan plus de sort d'une chaine de caractère ni d'aggrégate.

temps de traitement 1 minutes,

merci pour votre aide,

cdt

Erreur ORA-4030 dans Procédure PL/SQL

PL/SQL Oracle

Discussions similaires

Partager

Partager