Exception mais continuer boucle 8i

Version imprimable

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:

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 !

Il faut mettre l'exception dans la boucle

Code:

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.

12/04/2010, 21h18
Tay94

Portée des variables en PLSQL

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.
13/04/2010, 09h21
mnitu

Citation:

Envoyé par Tay94

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.
13/04/2010, 10h49
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 ?
13/04/2010, 11h16
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).
13/04/2010, 11h25
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.
13/04/2010, 11h29
mnitu

Citation:

Envoyé par 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 ?

Oui, faire un seul commit à la fin.
Sinon utilisez des savepoint pour faire rollback seulement de la ligne qui pose le problème.
13/04/2010, 11h36
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.
13/04/2010, 11h38
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.
13/04/2010, 11h48
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.
13/04/2010, 11h55
Tay94

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

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.
13/04/2010, 13h18
sgora

Ah, désolé ! :calim2:

Euh, par contre pour %ROWCOUNT, il va évoluer de 0 à n en fonction du balayage des lignes par ton curseur, pas en fonction des lignes que tu mets à jour ou pas.

SQL%ROWCOUNT

Ci-dessous mon SQL%ROWCOUNT va bien me récupérer le nombre de lignes que ma clause update va mettre à jour, et ce à chaque fois que je FETCH mon curseur ?

0 si pas de correspondance, 1 si tout va bien, plus que 1 est impossible au vu de mes données.

Je souhaite simplement éviter de commiter à chaque fois que je lis une ligne de mon curseur mais aussi être sûr d'avoir commité juste avant un rollback du à des doublons.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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; IF SQL%ROWCOUNT=1 THEN COMMIT; END IF; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN null; END; END LOOP;

13/04/2010, 13h45
mnitu
Citation:

Envoyé par 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.

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:

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.
13/04/2010, 13h56
Tay94
Je pensais qu'un Rollback annulait tout ce que la transaction n'avait pas commité...

Ainsi si je fais :
Code:

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 ?
13/04/2010, 13h56
sgora

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

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.
14/04/2010, 15h01
Tay94

bilan

Merci bien le traitement est OK avec un commit toutes les 5000 lignes lues du curseur, et les performances loin d'être mauvaises même avec un begin dans la boucle.

J'étais pas au courant sur le savepoint implicite de tout ordre DML en plsql, ça m'apprendra à lire plus souvent la doc !
14/04/2010, 16h06
mnitu

Citation:

Envoyé par Tay94

Merci bien le traitement est OK avec un commit toutes les 5000 lignes lues du curseur, et les performances loin d'être mauvaises même avec un begin dans la boucle.

J'étais pas au courant sur le savepoint implicite de tout ordre DML en plsql, ça m'apprendra à lire plus souvent la doc !

Ce n'est pas le begin dans la boucle qui plombe les perfs; c'est vrai ma formulation était ambiguë. C'est la fait de faire une boucle dans lequel vous faite un update ligne à ligne.

[Edit]
Bref, à force de vouloir prêcher toujours la bonne parole d'Oracle j'oublie parfois le bon sens. Donc si c'est un traitement de «*reprise*» donc occasionnel et vous n'avez aucune pression sur le temps de réponse, votre solution est tout à fait correcte.
[/Edit]