Discussion :

[Tay94]

Bonjour,

Sur oracle 8i j'ai un petit soucis en PL/SQL : je créé un curseur à partir d'une table A pour mettre à jour un champ d'une table B en faisant une égalité sur 2 autres champs. Les 3 champs sont la PK de la table B, qui au passage fait 100 millions de lignes (mettre un not in la table B dans la clause where de l'update est trop lourd).

Problème, dans certains rares cas, le fait de mettre à jour le champ engendre une violation de la PK, il faut donc gérer l'exception DUP_VAL_ON_INDEX, sans arrêter la boucle sur le curseur. On ne fait rien dans ce cas, on tente juste de mettre à jour à partir de la ligne suivante du curseur.

Exemple ci-dessous :

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
DECLARE
 
CURSOR C1 is
SELECT ATT1,ATT2,ATT3
FROM MATABLE1;
 
C1ROW C1%ROWTYPE;
 
BEGIN;
 
OPEN C1;
 
LOOP
FETCH C1 INTO C1ROW;
EXIT WHEN C1%NOTFOUND;
 
UPDATE MATABLE2 T2
set T2.ATT3=C1ROW.ATT3
where T2.ATT1=C1ROW.ATT1 
and T2.ATT2=C1ROW.ATT2
and T2.ATT3<>C1ROW.ATT3
 
END LOOP;
 
EXCEPTION
	 WHEN DUP_VAL_ON_INDEX THEN NULL;
 
CLOSE C1;
 
COMMIT;
 
END;

Après tests en recette la boucle semble s'arrêter une fois l'exception générée, je m'y prendrais mal ?

Merci pour vos éclaircissements !

[mnitu]

Il faut mettre l'exception dans la boucle

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
 
OPEN C1;
 
LOOP
FETCH C1 INTO C1ROW;
EXIT WHEN C1%NOTFOUND;
Begin 
  UPDATE MATABLE2 T2
  SET T2.ATT3=C1ROW.ATT3
  WHERE T2.ATT1=C1ROW.ATT1 
  AND T2.ATT2=C1ROW.ATT2
  AND T2.ATT3<>C1ROW.ATT3;
EXCEPTION
	 WHEN DUP_VAL_ON_INDEX THEN NULL;
End; 
END LOOP;
 
CLOSE C1;
 
COMMIT;
 
END;

Mais, ce type de traitement est extrêmement inefficace.

[Tay94]

Merci pour cette réponse rapide, j'essaierai demain au boulot.

Par contre ce qui me surprend c'est qu'une variable en PLSQL est locale au bloc PLSQL, or dans le BEGIN compris dans la boucle, les variables C1ROW.ATT1, 2 et 3 ont été définies dans le bloc englobant.

[mnitu]

Merci pour cette réponse rapide, j'essaierai demain au boulot.

Par contre ce qui me surprend c'est qu'une variable en PLSLQ est locale au bloc PLSQL, or dans le BEGIN compris dans la boucle, les variables C1ROW.ATT1, 2 et 3 ont été définies dans le bloc englobant.

Il y a un réponse, indirecte c'est vrai, dans le tutoriel.

[Tay94]

Ok ça marche globalement, par contre ce qui m'embête c'est que je commit toutes les 20000 lignes lues de mon curseur, or si j'ai l'exception générée à la 19000ème ligne, j'ai un rollback sur ce qui a été fait sur les 18999 lignes lues précédemment.

Ai je un autre choix que de commiter ligne à ligne ?

[sgora]

Le problème de ton traitement est qu'il semble modifier la valeur d'un champ qui sert de clé primaire, ce qui est extrêmement et formellement déconseillé. Je ne vois pas l'utilité fonctionnelle d'une telle manip, par contre j'en vois les dangers (comme la création de doublons).

[Tay94]

Je n'ai pas forcément le choix : dans une grosse table une des colonnes contient des valeurs fausses car le traitement qui mettait à jour la table était erroné.

J'ai corrigé le traitement, maintenant je dois faire un traitement de reprise sur cette table, pas de chance la colonne qui contient des données fausses fait partie de la PK.

Je pourrais à la limite créer une nouvelle colonne sans la rattacher à la PK, alimenter les mêmes données puis mettre à jour.

Mais je préfère mettre à jour la colonne, car c'est une table de reporting à but purement statistique, et que le pourcentage d'erreurs dans la colonne est relativement faible.

[sgora]

Ok, je vois. Le problème, c'est qu'en 8i il n'y a pas de manière élégante de s'en sortir.

Une autre option serait de désactiver la contrainte, faire la mise à jour par SQL, puis lancer un traitement de dédoublonnage afin d'éliminer les lignes parasites, et enfin réactiver la contrainte. Mais si la table est grosse, ça risque de prendre autant sinon plus de temps que ta solution.

[mnitu]

Ok ça marche globalement, par contre ce qui m'embête c'est que je commit toutes les 20000 lignes lues de mon curseur, or si j'ai l'exception générée à la 19000ème ligne, j'ai un rollback sur ce qui a été fait sur les 18999 lignes lues précédemment.

Ai je un autre choix que de commiter ligne à ligne ?

Oui, faire un seul commit à la fin.
Sinon utilisez des savepoint pour faire rollback seulement de la ligne qui pose le problème.

[Tay94]

Mais si je fais un seul commit à la fin tout ce qui a été fait avant l'erreur sera rollback ?

De plus même en faisant des savepoint à chaque ligne lue de mon curseur, si je ne commit pas encore depuis 10000 lignes il va revenir au dernier commit car si mon update plante le rollback est implicite.

Même en mettant dans l'exception rollback to savepoint, le rollback a pour moi déjà eu lieu et il me retourne au dernier commit.

[sgora]

Non, dans ce cas tu fais un commit après l'update, tu lances ton traitement qui va faire delete des doublons, tu fais un nouveau commit, puis tu réactives la PK qsuand la table est propre. Mais attention, le traitement de dédoublonnage peut être très long, car dans ce cas tu dois faire une jointure de la table sur elle-même.

[Tay94]

Ma réponse était adressée à mnitu .

Désactiver la PK ça ne m'arrange pas, car je n'ai pas les droits !

Par contre comme le nombre de ligne impactées par l'update est faible, je pense utiliser la variable SQL%ROWCOUNT, et je commite chaque fois qu'elle est égale à 1.

Ainsi je ne commite pas pour chaque ligne lue de mon curseur, uniquement quand une ligne est mise à jour.

[mnitu]

Mais si je fais un seul commit à la fin tout ce qui a été fait avant l'erreur sera rollback ?

De plus même en faisant des savepoint à chaque ligne lue de mon curseur, si je ne commit pas encore depuis 10000 lignes il va revenir au dernier commit car si mon update plante le rollback est implicite.

Même en mettant dans l'exception rollback to savepoint, le rollback a pour moi déjà eu lieu et il me retourne au dernier commit.

Avez vous faite un test ou c’est seulement ce que vous pensez ?
Votre insert échoue à cause d’un doublon. Dans ce cas il y a une rollback implicite de ce dernier inserts seul, c’est toute à fait normal. Ensuite une exception DUP_VAL_ON_INDEX est générée que vous interceptez dans votre code PL/SQL et que vous décidez de la bloquer avec votre instruction NULL.

Code :

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

1
2
3
 
WHEN DUP_VAL_ON_INDEX THEN
		NULL;

Il n’y pas de propagation de l’exception et donc l’état de votre base ne change pas : tout le travail fait avant reste à l’état à valider par un commit ou à invalider par un rollback.

[Tay94]

Je pensais qu'un Rollback annulait tout ce que la transaction n'avait pas commité...

Ainsi si je fais :

Code :

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

1
2
WHEN DUP_VAL_ON_INDEX THEN
		COMMIT;

En cas de doublons dans mon update, je vais commiter les update avant celui qui est problématique ?

[sgora]

D'accord pour pour SQL%ROWCOUNT, j'avais cru que tu voulais utiliser le C1%ROWCOUNT.

Et pour le rollback, mnitu a raison, PL/SQL crée un savepoint implicite avant toute instruction SQL, donc si tu fais tes updates un par un, si le dernier plante, on va remonter jusqu'au savepoint, donc on ne perdra en principe aucune ligne traitée auparavant.