probleme rowcount avec Forall

[juin29]

Bonjour,

Je suis en 10g et j'utilise le pl/sql.

J'utilise un bulk avec un limite 200 pour un de mes programmes de mises à jours. J'ai environ 300000 enregistrement à traiter.

suite à mon bulk dans des tableaux, j'utilise forall pour faire un update. Juste après, j'utilise sql%rowcount pour savoir si je fais un certain traitement qui doit se faire toute les 2000 enregistrements... mais voilà que le rowcount semble échappé des transactions à certain moment. c'est à dire qu'apres plusieurs milliers de record je remarque que le sql%rowcount me retourne 199 au lieu de 200... cela arrive à plusieurs reprise durant le traitement donc à la fin c'est comme si j'avais un décalage de 50 enregistrements...

Le probleme semble aléatoire d'une fois à l'autre... alors je ne sais plus quoi penser... est-ce un problème connu? est-ce que vous voyez une alternative?

merci

[mnitu]

Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute

[juin29]

ce que je comprend c'est que tu me dis d'utiliser le SQL%BULK_ROWCOUNT(i). Mais comme le I fait référence au counteur du bulk et bien ce n'est pas vraiment mon besoin... j'ai besoin de savoir combien d'enregistrement ont été updater avec mon FORALL update... en principe, il devrait y en avoir 200 jusqu'avant la fin à moins que les condition du update ne soit pas répondu... mais comme le where clause est le meme que celui du select de mon bulk... je ne vois pas pourquoi il en sauterais...

est-ce que je comprends bien ce que tu souhaitait m'expliquer... je ne suis pas sur que ce soit ca que j'ai besoin à moins que je ne comprenne pas bien comment ca fonctionne.

merci

[mnitu]

Oui c'est ça. Maintenant ça pourrait aller mieux avec un petit exemple.
Si je comprends bien, votre problème est que des enregistrements semble disparaitre du traitement.
Cella pourrait s'expliquer si des autres transactions modifient en même temps les données utilisées par votre traitement:
1) Vous chargez 200 lignes qui correspondent à vos critères dans un tableau sans les verrouiller.
2) Une autre transaction commit une modification pour une de colonnes utilisées comme critère de sélection d'une de vos lignes.
3) Vous faite l'update avec les mêmes critères que le select mais maintenant la ligne n'a plus la valeur de début dans la base.

[juin29]

ca aurait tres bien pu etre ca... mais c'est un environnement de dev dont je suis le seul utilisateur... donc... je ne comprend pas trop...

par contre... le sql%ROWCOUNT utilisé apres un update... retourne bien le nombre d'update effectué? donc si dans une boucle donnée l'update correspondant au where-clause correspond seulement à 199 sur 200, le sql%rowcount devrait me retourné 199 au lieu du 200 attendu pour cette fois là n'est-ce pas ?

Si on fait un parrallele avec le SQL%BULK_ROWCOUNT(i)... ne devrait-il pas toujours me retourné 200 puisque la limite du bulk est 200? si oui, ca n'a aucun rapport avec l'update je me trompe?

[mnitu]

Le SQL%ROWCOUNT ne peut être utilisé qu’après l’instruction FORALL. Il vous donne le nombre total, cumulative des enregistrements modifié par le FORALL. Voilà un exemple:

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> SET serveroutput ON
SQL> 
SQL> Declare
  2    Cursor crs IS
  3    SELECT department_id
  4      FROM hr.departments d;
  5    --
  6    Type dept_aa IS TABLE Of Integer INDEX BY Pls_Integer;
  7    l_tdept        dept_aa;
  8  Begin
  9    Open crs;
 10    Fetch crs  Bulk Collect INTO l_tdept LIMIT 10;
 11    --
 12    ForALL i IN 1..l_tdept.Count()
 13      UPDATE hr.departments d
 14        SET d.department_name = Upper(d.department_name)
 15       WHERE d.department_id = l_tdept(i);
 16    FOR i IN 1..l_tdept.count() Loop
 17      Dbms_Output.put_line('Lignes modifiées dans itération ('||To_Char(i)||') = '||To_Char(SQL%BULK_ROWCOUNT(i)));
 18    End Loop;
 19    --
 20    Dbms_Output.put_line('Total lignes modifiées = '||To_Char(SQL%ROWCOUNT));
 21    --
 22    Close crs;
 23    --
 24    Rollback;
 25  End;
 26  /
 
Lignes modifiées dans itération (1) = 1
Lignes modifiées dans itération (2) = 1
Lignes modifiées dans itération (3) = 1
Lignes modifiées dans itération (4) = 1
Lignes modifiées dans itération (5) = 1
Lignes modifiées dans itération (6) = 1
Lignes modifiées dans itération (7) = 1
Lignes modifiées dans itération (8) = 1
Lignes modifiées dans itération (9) = 1
Lignes modifiées dans itération (10) = 1
Total lignes modifiées = 10
 
PL/SQL procedure successfully completed
 
SQL>

[PpPool]

Bonjour

Ajoute un save exceptions à ton forall

Exemple :

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
DECLARE
…
   --[EXCEPTION_INIT pour le FORALL]
   forall_dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(forall_dml_errors, -24381);
BEGIN
…
BEGIN        
   FORALL I IN  TAB_DBA_M.FIRST.. TAB_DBA_M.LAST SAVE EXCEPTIONS
     INSERT INTO T_DBA(TDBA_M, TDBA_SZ,TDBA_SU) VALUES( TAB_DBA_M (I) ,TAB_DBA_SZ (I),TAB_DBA_SU (I) );
    COMMIT;
 
   EXCEPTION
     WHEN forall_dml_errors THEN
       l_error_count_insert := SQL%BULK_EXCEPTIONS.count;
       DBMS_OUTPUT.put_line('Nombre d''erreur(s)  : ' || l_error_count_insert);
       FOR i IN 1 .. l_error_count_insert LOOP
         DBMS_OUTPUT.put_line('Erreur remontée: ' || i ||
          ' Index de la collection : ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
       END LOOP;
   END;
 
END;

[s.houali]

Envoyé par juin29

Bonjour,

Je suis en 10g et j'utilise le pl/sql.

J'utilise un bulk avec un limite 200 pour un de mes programmes de mises à jours. J'ai environ 300000 enregistrement à traiter.

suite à mon bulk dans des tableaux, j'utilise forall pour faire un update. Juste après, j'utilise sql%rowcount pour savoir si je fais un certain traitement qui doit se faire toute les 2000 enregistrements... mais voilà que le rowcount semble échappé des transactions à certain moment. c'est à dire qu'apres plusieurs milliers de record je remarque que le sql%rowcount me retourne 199 au lieu de 200... cela arrive à plusieurs reprise durant le traitement donc à la fin c'est comme si j'avais un décalage de 50 enregistrements...

Le probleme semble aléatoire d'une fois à l'autre... alors je ne sais plus quoi penser... est-ce un problème connu? est-ce que vous voyez une alternative?

merci

Le problème est probablement dû au fait que vous utilisez cursor%notfound pour sortir de votre boucle, ce qui engendrerait la perte d’enregistrements non traités.

Voici un exemple simple montrant pourquoi des enregistrements échappent au traitement :

Code :

1
2
 
CREATE TABLE test_table (col1 number, col2 varchar2(20));

Alimenter la table juste pour notre test :

Code :

1
2
3
4
5
6
7
8
9
 
begin
FOR i IN 1..17
loop
INSERT INTO test_table (col1, col2)
VALUES (i, 'ligne '||i);
end loop;
commit;
end;

Maintenant essayons de voir pourquoi des enregistrements ne sont pas traités :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
declare
type num_tab IS TABLE of number INDEX BY pls_integer;
n_t num_tab;
 
cursor c 
IS
SELECT TT.COL1
FROM test_table tt;
begin
open c;
loop
fetch c bulk collect INTO n_t LIMIT 10;
exit when c%notfound;
--exit when n_t.count = 0;
FOR i IN 1..n_t.count
loop 
dbms_output.put_line(n_t(i));
end loop;
end loop;
close c;
 
end;

1er cas : Condition de sortie : exit when c%notfound;

1
2
3
4
5
6
7
8
9
10
Nous remarquons que les sept derniers enregistrements ne sont pas traités.
A la deuxième itération, il y’a sortie de la boucle, il n’a pas d’enregistrement 18ème, les 10 enregistrements suivants ne sont donc pas traités.

2ème cas : Condition de sortie : exit when n_t.count=0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Nous remarquons que la totalité des enregistrements est traitée.

[Pomalaix]

Envoyé par s.houali

Le problème est probablement dû au fait que vous utilisez cursor%notfound pour sortir de votre boucle

Il est vrai que le point que vous soulevez est un attrape couillon de première classe : "LIMIT n" provoque un %NOTFOUND s'il ne reste plus assez de lignes à ramener pour atteindre n.

Néanmoins, ce n'est pas tant le %NOTFOUND en soi qui est un problème, c'est surtout une question de localisation.
Si on le met après la portion de code qui traite le lot de lignes ramenées, et non juste après le FETCH, ça peut marcher même quand le lot de lignes n'est pas complet (inférieur à n).

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
set serveroutput on

declare
	type num_tab IS TABLE of number INDEX BY pls_integer;
	n_t num_tab;
 
cursor c IS
	SELECT TT.COL1
	FROM test_table tt;

begin
	open c;
	loop
		fetch c bulk collect INTO n_t LIMIT 10;
		FOR i IN 1..n_t.count
		loop 
			dbms_output.put_line(n_t(i));
		end loop;
		exit when c%notfound;
	end loop;
	close c;
 end;
 /

Il n'en reste pas moins que pratiquer ainsi oblige à alourdir le code si on veut traiter aussi les cas limites (table vide).

Votre test de sortie sur le COUNT de la collection étant simple et universel, il n'y a pas de raison de s'en priver !

D'ailleurs, il est amusant de voir que Feuerstein pousse le bouchon encore plus loin en recommandant carrément, si j'ai bien compris, de ne plus utiliser le test avec %NOTFOUND dès lors qu'on utilise du BULK COLLECT, et ce avec ou sans LIMIT.
http://www.oracle.com/technetwork/is...ql-095155.html