Script PL/SQL pour effectuer des contrôles de cohérence entre tables.

Version imprimable

Bonjour,

Dans le cadre d'un nettoyage d'une base de données Oracle, je dois effectuer des contrôles de cohérence entre deux tables.
Un comptage entre deux tables ne suffit pas à affirmer qu’il y a du « un pour un » entre ces deux tables.
C'est à dire que l’égalité du nombre de postes ne permet pas d’affirmer qu’il s’agit de la même liste de références.
Par conséquent, il faut bien effectuer les requêtes de sélection en utilisant l'ordre "minus" dans les deux sens.

Je souhaite également, à la suite de ces requêtes de sélection, mettre à jour un indicateur de cohérence dans une table
(champ "INDCOHAV" de la table CPT_PURGE).

Voici le code associé que je cherche à faire:
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 -- Codes erreurs Oracle pour quitter le programme des la premiere erreur rencontree WHENEVER SQLERROR EXIT 61 WHENEVER OSERROR EXIT 62 -- Controles INTRA SIG -- SIG_RECO05 -- Mise a jour de l'indice de coherence avant purge DECLARE nbre1 Number; nbre2 Number; BEGIN -- Toute reference dans la table SIG_RECO05 doit etre dans la table SIG_RECO04 et vice-versa SELECT ACO005_NUMFEU8 into nbre1 FROM SIG_RECO05 minus SELECT RECO04_NUMFEU8 FROM SIG_RECO04; SELECT RECO04_NUMFEU8 into nbre2 FROM SIG_RECO04 minus SELECT ACO005_NUMFEU8 FROM SIG_RECO05; IF (nbre1+nbre2=0) THEN EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=0 WHERE NOMTABLE='SIG_RECO05''; ELSE EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=1 WHERE NOMTABLE='SIG_RECO05''; END IF; END; / COMMIT; QUIT
Cela me retourne l'erreur suivante:

Citation:

fich.log" 30 lines, 1299 characters
--------------------------------------------------------
103609-Execution du fichier /ficsql/fich.sql via SQLPLUS

EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=0 WHERE NOMTABLE='SIG_RECO05'';
*
ERROR at line 9:
ORA-06550: line 9, column 70:
PLS-00103: Encountered the symbol "SIG_RECO05" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like LIKE2_ LIKE4_ LIKEC_ between into using || multiset bulk
member SUBMULTISET_
The symbol "* was inserted before "SIG_RECO05" to continue.
ORA-06550: line 11, column 70:
PLS-00103: Encountered the symbol "SIG_RECO05" when expecting one of the
following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like LIKE2_ LIKE4_ LIKE

103609-Code retour SQLPLUS 61
103609-Traitement du fichier /ficsql/fich.sql realise avec erreur

103609-fin d'execution du fichier /ficsql/fich.sql

Si certains pouvaient m'aiguiller sur ce point, je les remercie par avance.

Bonne journée.

Olivier

25/01/2011, 11h41
Waldar

Il faut doubler les apostrophes lorsqu'on construit une chaîne de caractère entre apostrophe justement :

Code:

EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=0 WHERE NOMTABLE=''SIG_RECO05''';
25/01/2011, 11h43
Garuda

Il faut doubler les quotes !

Code:

EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=0 WHERE NOMTABLE=''SIG_RECO05''';

PS : Si tu connais le nom de ta table, inutile de faire du sql dynamique !

Forcément, un select into variable ne doit renvoyer qu'une seule ligne

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Controles INTRA SIG
-- SIG_RECO05
-- Mise a jour de l'indice de coherence avant purge
DECLARE
  nbre1 Number := 0;
  nbre2 Number := 0;
BEGIN
	-- Toute reference dans la table SIG_RECO05 doit etre dans la table SIG_RECO04 et vice-versa
	SELECT COUNT(*) INTO nbr1	
  FROM (SELECT ACO005_NUMFEU8 FROM SIG_RECO05 MINUS SELECT RECO04_NUMFEU8 FROM SIG_RECO04);
  -- Peut être éviter de faire le 2ème sélect
  IF nbr1 = 0
  THEN 
    SELECT COUNT(*) INTO nbr2
    FROM (SELECT RECO04_NUMFEU8 INTO nbre2 FROM SIG_RECO04 MINUS SELECT ACO005_NUMFEU8 FROM SIG_RECO05);
	END IF;
 
	IF nbre1+nbre2 = 0 THEN
  	EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=0 WHERE NOMTABLE='SIG_RECO05'';
  ELSE
  	EXECUTE IMMEDIATE 'UPDATE CPT_PURGE SET INDCOHAV=1 WHERE NOMTABLE='SIG_RECO05'';
 END IF;
END;

Pourquoi un execute immediate à la fin ?

25/01/2011, 14h32
Reivilo35

Merci pour vos réponses.

Je regarde et teste les différentes remarques.

Cdlt,
Olivier

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
declare
  l_nomtab  CPT_PURGE.NOMTABLE%TYPE := 'SIG_RECO05';
Begin  
  UPDATE CPT_PURGE 
     SET INDCOHAV =  (Select Count(*)
                        From dual
                       Where Exists (Select Null
                                       FROM SIG_RECO04 a
                                            FULL OUTER JOIN
                                            SIG_RECO05 b
                                         On a.RECO04_NUMFEU8 = c.ACO005_NUMFEU8
                                      Where a.rowid Is Null
                                         Or b.rowid Is null         
                                     )       
   WHERE NOMTABLE= l_nomtab;
End;   
/

Et ça a l'avantage de ne pas imposer le traitement mono-utilisateur que les autres solutions implique.

25/01/2011, 16h44
Reivilo35

Effectivement, il fallait doubler les apostrophes pour que le libellé de la table soit reconnu.

De même, comme le nom de ta table est connu, j'ai essayé en enlevant le "execute immediate" et ça fonctionne tout aussi bien.

Merci à Waldar, Garuda et McM pour toutes ces bonnes remarques qui m'ont permis de débloquer la situation.

Pour l'idée de mnitu, je vais regarder ce que ça donne, mais la solution précédente me sied déjà très bien.

Bonne journée.

Cdlt,
Olivier
25/01/2011, 18h09
Reivilo35

A mnitu:

Merci pour l'idée de la jointure externe, ça répond bien à ce que j'ai à faire.

Par contre, serait-il possible d'avoir quelques précisions:

- que signifie le %TYPE dans la déclaration de variable ?
- comment comprendre la notion de table DUAL ?

Pour le reste, voici ce que j'ai compris et corrigez-moi le cas échéant:

- par rapport au LEFT/RIGHT OUTER JOIN qui regarde les éléments manquants de l'une ou l'autre des tables impactées, le FULL OUTER JOIN prend en compte les occurrences manquantes des deux tables à la fois,
- le rowid est l'occurrence au niveau structure de la table et si celui-ci est nul, alors cela signifie, après la jointure effectuée, qu'il existe des occurrences manquantes dans une table par rapport à l'autre,
- et enfin d'après la requête, cela signifie que le résultat (INDCOHAV) pourra prendre comme valeur 0 (cad qu'il y a exactement les mêmes valeurs dans les deux tables), mais également 1, 2, 3,... selon le nombre d'occurrences (rowid) remontées par la requête.

Merci d'avance pour les réponses et les éclaircissements à ma compréhension.

Bonne soirée.

Cdlt,
Olivier

%type : prend la définition de ta colonne en base. Comme ça si ça change (genre un varchar2(50) qui passe en varchar2(100)), tu n'as pas besoin de modifier toutes tes procédures, scripts, etc.. Pratique pour la maintenance, mais moins lisible.

DUAL : Table systeme qui ne contient qu'une seule ligne, une seule colonne.
Mnitu l'utilise pour avoir soit 0 soit 1

Ca devrait (sauf erreur de code) être équivalent à :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 DECLARE l_nomtab VARCHAR2(30) := 'SIG_RECO05'; BEGIN UPDATE CPT_PURGE SET INDCOHAV = (SELECT COUNT(*) FROM (SELECT 1 FROM SIG_RECO04 a FULL OUTER JOIN SIG_RECO05 b ON a.RECO04_NUMFEU8 = b.ACO005_NUMFEU8 WHERE ROWNUM = 1 AND (a.RECO04_NUMFEU8 IS NULL OR b.ACO005_NUMFEU8 IS NULL) ) ) WHERE NOMTABLE= l_nomtab; END;

26/01/2011, 09h56
Reivilo35

Merci McM pour tes réponses.

Bonne journée.

Cdlt,
Olivier