Curseur & violation de contrainte & poursuite du traitement

[carreau]

bonjour,

J'essaye d'insérer des lignes dans une table munie d'une contrainte d'intégrité.
Lorsque les lignes existent déjà, l'insertion plante (très bien).
Mais lorsque j'essaye d'insérer des lignes de manière répétitive avec un curseur dans un bloc de code, l'exécution s'arrête au premier échec d'insertion.

Comment gérer cette exception et poursuivre l'exécution à la valeur suivante du curseur ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
CURSOR c_sect IS
SELECT id_secteur FROM t_secteur;
BEGIN
  FOR c IN c_sect LOOP
    dbms_output.put_line( To_char( c.id_secteur ) ) ;
    INSERT INTO t_user (id_secteur,id_agent)
    SELECT c.id_secteur,id_agent
    FROM agent
    WHERE id_agent IN('toto','tata') ;
  END LOOP ;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN ROLLBACK;
END ;
/

Evidemment, une solution plus propre consisterait à faire une bonne sélection

Code :

1
2
3
4
5
6
7
8
9
10
    SELECT c.id_secteur,id_agent
    FROM agent
    WHERE id_agent IN('toto','tata')
    AND NOT EXISTS(
      SELECT id_secteur,id_agent
      FROM t_user
      WHERE id_secteur=c.id_secteur
      AND id_agent IN('toto','tata')
      )
    );

Mais je me pose la question de la gestion de l'exception

Code :

1
2
3
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN ?? ;

des possibilités et moyens d'arrêter ou poursuivre un curseur

Merci d'avance

[Mohamed.Houri]

Bonjour,

Vous pouvez:

(a) soit encapsuler votre traitement de LOOP dans un BEGIN/EXCEPTION/END bloc afin que chaque itération en erreur soit retenue dans l'exception de ce bloc; par contre prenez soin de bien gérer les exceptions dans ce cas. En effet il faut que vous vous posiez la question essentielle qui est de savoir quoi faire avec les insertions en erreur dans ce cas.

(b) soit, si vous êtes sur une version 10g ou plus, d'utiliser le "DML error logging" dont vous trouverez des exemples partout sur internet. Si vous optez pour cette solution pensez alors dans ce cas à lire ce simple article que j'ai écris sur les restrictions du DML error logging

http://hourim.wordpress.com/2011/02/...-restrictions/

[Waldar]

J'ajouterai qu'une simple jointure annule aussi l'intérêt du curseur :

Code :

1
2
3
4
5
6
7
8
9
10
INSERT INTO t_user (id_secteur, id_agent)
SELECT sec.id_secteur
     , agn.id_agent
  FROM agent agn
       CROSS JOIN t_secteur sec
       LEFT OUTER JOIN t_user usr
         ON usr.id_secteur = sec.id_secteur
        AND usr.id_agent   = agn.id_agent
 WHERE agn.id_agent IN ('toto','tata')
   AND usr.id_user IS NULL; -- la PK ou une colonne non nulle de la table t_user

[mnitu]

Ou utilisez ForAll avec Save Exceptions: "Handling FORALL Exceptions After FORALL Statement Completes"

[Scriuiw]

Comme dis plus haut, il suffit de gérer cette exception au niveau de 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
23
24
 
DECLARE
CURSOR c_sect IS
SELECT id_secteur FROM t_secteur;
BEGIN
  FOR c IN c_sect LOOP
    BEGIN    
      dbms_output.put_line( To_char( c.id_secteur ) ) ;
      INSERT INTO t_user (id_secteur,id_agent)
      SELECT c.id_secteur,id_agent
      FROM agent
      WHERE id_agent IN('toto','tata') ;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
       dbms_output.put_line('DUP_VAL_ON_INDEX : Id secteur '||c.id_secteur||' / Id agent '||c.id_agent);
    END;
  END LOOP ;
EXCEPTION
   WHEN OTHERS THEN 
     dbms_output.put_line('Erreur globale : '||SQLERRM);
     dbms_output.put_line('Transaction annulée');
     ROLLBACK;
END ;
/