Discussion :

[wondersonic]

Bonsoir,
Je viens de rencontrer un problème "fascinant" en PL/SQL.

J'ai une application qui reçoit des messages à traiter. Chaque message est identifié par une clé fonctionnelle. Lorsqu'un message entre dans le système, on vérifie d'abord qu'il n'a pas déjà été traité. Pour ce faire, on a une table (unique_checks) qui possède une clé primaire : la clé fonctionelle. Les nouveaux messages (qui arrivent par lots) sont testés par rapport aux clés déjà traitées en tentant une insertion de sa propre clé dans la table unique_checks. Si le message existe déjà, on reçoit une erreur ORA-00001 sinon, le message est inséré.

Je suis sur une base Oracle 9.2.0.8 (PA-RISC 64 bits, Enterprise edition) et j'utilise les fonctionnalités PL/SQL forall et bulk collect into.

Le testcase est le suivant :

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
SET SERVEROUTPUT ON SIZE 1000000
 
DROP TABLE unique_checks;
 
CREATE TABLE unique_checks ( my_key VARCHAR2( 1000 ) NOT NULL PRIMARY KEY );
 
SET FEEDBACK OFF
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763680802012114');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763670802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763710802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763650802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763730802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763660802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763640802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763720802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763700802012121');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393800810802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393800890802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392277590802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392277610802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392277600802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445140802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394444970802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445040802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394444980802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394444960802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445000802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445020802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445060802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445010802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394444990802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445110802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445070802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013391664580802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445100802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394444950802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445050802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394445080802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394830080802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392433980802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392433970802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392433960802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434910802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434900802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434890802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434010802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434000802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392433990802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434020802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434030802012116');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434880802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013392434920802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394830060802012122');
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013394222540802012115');
SET FEEDBACK ON
COMMIT;
 
DROP TABLE new_entries;
 
CREATE TABLE new_entries ( my_key VARCHAR2( 1000 ) NOT NULL );
 
SET FEEDBACK OFF
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013393763680802012114');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013393763670802012121');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013393763710802012121');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013393763650802012121');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013393763730802012121');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013393800880802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392277590802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392434920802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013394830080802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392433980802012116');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392433970802012116');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392433960802012116');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392434910802012116');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392434900802012116');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392434890802012116');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013394830070802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013394830050802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322980802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392321310802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322990802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392321330802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322950802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322960802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322940802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322930802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392321300802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322920802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013394799060802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392322970802012122');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013392321280802012115');
INSERT INTO new_entries(my_key) VALUES ('CCP_SETT_CONF00000013386441650802012114');
SET FEEDBACK ON
COMMIT;
 
DECLARE
     TYPE refCursorType IS REF CURSOR;
     -- declare a weak ref cursor
     select_cursor refCursorType;
 
     type data_array_type is table of unique_checks%rowtype index by binary_integer;
 
     data data_array_type;
 
     errors NUMBER;
     dml_errors EXCEPTION;
     PRAGMA exception_init(dml_errors, -24381); -- do not change the exception number!
 
BEGIN
     OPEN select_cursor for 'select my_key from new_entries order by my_key';
 
     LOOP
          -- bulk fetch
          fetch select_cursor BULK COLLECT INTO data LIMIT 1000;
 
          BEGIN
             -- try to insert into the unique messages table
             FORALL i IN 1..data.COUNT SAVE EXCEPTIONS
                insert into unique_checks values data(i);
          EXCEPTION
              WHEN dml_errors THEN
                   -- if it fails...
                   errors := SQL%BULK_EXCEPTIONS.COUNT;
                   FOR j IN 1..errors LOOP
                       dbms_output.put_line( j || ': error ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE || ' for row with key: ' || data(j).my_key );
                       -- and if it is a violation of an unique constraint...
                   end loop;
          END;
 
          exit when select_cursor%notfound;
 
     END LOOP;
 
     CLOSE select_cursor;
 
end;
 
/
 
ROLLBACK;

En sortie, sortent les messages qui ont produits une erreur ORA-00001 lors de l'insertion dans la table unique_checks avec la même clé primaire. Pour ce test, j'ai en sortie :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1: error 1 for row with key: CCP_SETT_CONF00000013386441650802012114   <---- notez cette ligne
2: error 1 for row with key: CCP_SETT_CONF00000013392277590802012122
3: error 1 for row with key: CCP_SETT_CONF00000013392321280802012115
4: error 1 for row with key: CCP_SETT_CONF00000013392321300802012115
5: error 1 for row with key: CCP_SETT_CONF00000013392321310802012115
6: error 1 for row with key: CCP_SETT_CONF00000013392321330802012115
7: error 1 for row with key: CCP_SETT_CONF00000013392322920802012115
8: error 1 for row with key: CCP_SETT_CONF00000013392322930802012115
9: error 1 for row with key: CCP_SETT_CONF00000013392322940802012115
10: error 1 for row with key: CCP_SETT_CONF00000013392322950802012115
11: error 1 for row with key: CCP_SETT_CONF00000013392322960802012115
12: error 1 for row with key: CCP_SETT_CONF00000013392322970802012122
13: error 1 for row with key: CCP_SETT_CONF00000013392322980802012122
14: error 1 for row with key: CCP_SETT_CONF00000013392322990802012122

Ce que je ne comprends pas, c'est pourquoi je reçois une ORA-00001 pour la ligne de la table new_entries avec la clé 'CCP_SETT_CONF00000013386441650802012114' ?

En effet, cette valeur n'est insérée qu'une seule fois ; dans la table new_entries uniquement.

Qu'ai-je loupé ? Serait-ce un bug ?

Merci pour vos lumières.
WS

[laurentschneider]

moi je vois dans ton code :

Code :

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

1
2
 
INSERT INTO unique_checks(my_key) VALUES ('CCP_SETT_CONF00000013393763680802012114');

si tu utilisais moins d'insert et des clés plus courtes afin de tester si le bug est dans plsql ou devant le clavier tu aurais sans doute pu trouver ça, non

[laurentschneider]

bon, l'erreur n'était pas là.

en fait, avec DATA(j).mykey tu retournes pour la 1ère erreur la ligne 1, pour la 2e erreur la ligne 2, et non la ligne où il y avait l'erreur !

[DAB.cz]

Je pense tu dois utiliser %BULK_EXCEPTIONS(i).ERROR_INDEX:

Code :

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

dbms_output.put_line( j || ': error ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE || ' for row with key: ' || DATA(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).my_key );

DAB

[wondersonic]

Oui, merci c'est exactemment cela !