Bug PL/SQL ? (forall, bulk collect into)
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:
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:
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
