Discussion :

[teddi]

Bonjour je suis face au problème suivant et je sollicite votre aide.
J'ai sur deux serveurs oracle (orcl1 et orcl2) la même table compagnie avec la structure suivante.

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
 
compagnie_orcl1
 
username | id | compagnie| seq
--------------------------------------
USER1     |us1id1| CMR| 123
USER1     |us1id1| RWD| 456
USER1     |us1id1| SING| 789
USER1     |us1id1| CNG | 1011
------------------------------------
USER2     |us2id2| SEN | 1213
USER2     |us2id2| CND | 1415
USER2     |us2id2| CGT | 1617
USER2     |us2id2| CNG | 1819
USER2     |us2id2| KPG | 2021
------------------------------------
USER3     |us3id3| RWD| 2223
USER3     |us3id3| CND | 2425
USER3     |us3id3| CID  | 2627
USER3     |us3id3| CNG | 2829
USER3     |us3id3| SING| 3031

sa copie sur le serveur2 (orcl2)

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
 
compagnie_orcl2
username | id | compagnie |seq
-------------------------------------
USER1     |us1id1| RWD|0010
USER1     |us1id1| SING|0011
USER1     |us1id1| CNG |0012
------------------------------------
USER2     |us2id2| SEN|0013
USER2     |us2id2| CNG|0014
USER2     |us2id2| KPG|0015
-----------------------------------
USER3     |us3id3| RWD|0016
USER3     |us3id3| CND|0017
USER3     |us3id3| SING|0018

J'aimerai pouvoir ajouter dans la table compagnie_orcl2 toutes les lignes de la table compagnie_orcl1 commencant par "C" et qui n' y sont pas encore.
merci pour votre aide

[skuatamad]

Code :

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

1
2
3
4
5
6
7
8
9
insert into compagnie_orcl2 (username, id, compagnie)
select c1.username, c1.id, c1.compagnie
  from compagnie_orcl1 c1
 where c1.compagnie like 'C%'
   and not exists (select 1
                     from compagnie_orcl2 c2
                    where c1.username  = c2.username 
                      and c1.id        = c2.id
                      and c1.compagnie = c2.compagnie)

[McM]

Si tu as un DBLINK, il est parfois plus rapide de le gérer en MINUS

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2@db2 (username, ID, compagnie)
SELECT username, ID, compagnie
FROM COMPAGNIE_ORCL1
WHERE (username, ID, compagnie) IN 
  (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL12@db2 WHERE compagnie LIKE 'C%'
  )

[teddi]

skuatamad et McM merci pour vos réponses.

skuatamad par ce qu'avec ta solution j'ai eu 278 lignes en retour je me suis décidée à faire l'insertion avec un curseur comme suit:

**** Correction j'ai actualisée la table en ajoutant la colonne seq que j'avais oubliée.****

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
33
34
35
36
37
38
39
40
41
42
43
 
create or replace function comp (p_error OUT VARCHAR2)
RETURN BOOLEAN
is
 
cursor compa_cur is
 
select * from compagnie_orcl1@orcl1 c1
 
where c1.compagnie like 'C%'
   and not exists (select 1
                     from compagnie_orcl2 c2
                    where c1.username  = c2.username 
                      and c1.id   = c2.id
                      and c1.compagnie = c2.compagnie);
 
compa_tmp compa_cur%ROWTYPE;
 
begin
 
open compa_cur ;
 
loop
 
fetch compa_cur into compa_tmp;
 exit when compa_cur%NOTFOUND;
 
insert into compagnie_orcl2 (USERNAME, 
                     compagnie,  
                     id, 
                     Seq)
 
values (compa_cur.username,
        compa_cur.compagnie,
         compa_cur.id,
        Seq_cmp.nextval);
    commit; 
 
   end loop;
 
        close compa_cur;
   return true; 
end comp;

seulement l'insertion ne fonctionne pas telle que je veux. c' est à dire que pour un enregistrement une seule des lignes commencant par 'C' sera insérée et pas les autres.
j'ai l'impression que la récursion ne fonctionne pas.

[Scriuiw]

Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.

Sinon la fonction compile ?
Je trouve étonnant qu'une fonction ait une valeur de retournée (RETURN BOOLEAN) et un paramètre sortant (p_error OUT VARCHAR2)
D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.

[teddi]

Si tu as un DBLINK, il est parfois plus rapide de le gérer en MINUS

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2@db2 (username, ID, compagnie)
SELECT username, ID, compagnie
FROM COMPAGNIE_ORCL1
WHERE (username, ID, compagnie) IN 
  (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL12@db2 WHERE compagnie LIKE 'C%'
  )

merci MCM
malheureusement je n'obtiens aucun résultat avec cette requête.

[McM]

Visiblement tu te positionnes sur la base 2, mon select est fait pour être utilisé sur la base 1, car c'est là où sont les données.
Mais tu peux changer

Ce sous select doit te ramener tous les éléments distincts, mais tu as rajouté la colonne seq. Quelle est la PK ?

Code :

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

1
2
3
4
(SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

Si la PK est username, id, compagnie

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2 (username, ID, compagnie, seq)
SELECT username, ID, compagnie, seq
FROM COMPAGNIE_ORCL1@orcl1
WHERE (username, ID, compagnie) IN 
  (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

si la pk est username,id,compagnie,seq

Code :

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

1
2
3
4
5
6
7
8
INSERT INTO COMPAGNIE_ORCL2 (username, ID, compagnie, seq)
SELECT username, ID, compagnie, seq
FROM COMPAGNIE_ORCL1@orcl1
WHERE (username, ID, compagnie,seq) IN 
  (SELECT username, ID, compagnie,seq FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie,seq FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

[teddi]

Scriuiw merci également pour ta réponse.
je ne suis malheureusement pas sûre d'avoir compris grand chose. pourrais-tu être stp plus explicite?

Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
aurais-tu un exemple stp?


Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.
le COMMIT est en fin de boucle. où devrais-je le mettre sinon?

Sinon la fonction compile ?
oui la fonction compile.


D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.
n'étant pas forcément un as de la programmation j'essaie justement par ce post de comprendre mon erreur.

[teddi]

McM la PK est seq.

effectivement je me positionne sur la base2. merci je ressaie ton select.

Il fonctionne à merveille merci. Je parle du select. l'insertion est pour moi encore floue.

[McM]

Pourquoi floue ?

Un insert select, c'est facile, tu exécutes que le selec, tu vois toutes les lignes.. et bien si tu fais un insert select, toutes les lignes du select seront insérées.*

* bien sûr sous réserve qu'il n'y ait pas une erreur sur une ligne (contrainte, etc..)

[teddi]

merci MCM.
floue par ce que la fonction que j'ai postée ne fonctionne pas.

mais si tu es d'avis que seul l'insert tel que skuatamad et toi avez proposé suffit et qu'une fonction est dans ce cas est obsolète. dans ce cas tout est clair.
Mais ton insert ne prend pas en compte la PK SEQ c' est pour ca que je pensais qu'un curseur était indispensable. surtout pour gérer la récursion.

[McM]

Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
aurais-tu un exemple stp?

ton curseur comparé à insert/Select tels que proposés

Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.
le COMMIT est en fin de boucle. où devrais-je le mettre sinon?

pas de commit dans une boucle (sauf cas très rares), il faut le mettre à la fin du traitement
exemple

Code :

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

1
2
3
4
loop
  insert into compagnie_orcl2 (USERNAME, ..);
end loop;
    commit;

D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.
n'étant pas forcément un as de la programmation j'essaie justement par ce post de comprendre mon erreur.

A titre uniquement informatif (car comme dit plus haut, un insert/select sera plus performant qu'une boucle), voici comment serait le curseur.
1/ Privilégier le FOR LOOP que le OPEN CURSEUR qui doit être fermé, affecté, etc...
2/ Toujours penser à simplifier : Less code, less bug.

exemple de fonction qui retourne le nombre d'enregistrements créés

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
CREATE OR REPLACE FUNCTION comp
RETURN NUMBER
IS
	v NUMBER := 0;
BEGIN
  FOR rc IN (SELECT username, ID, compagnie, seq
              FROM COMPAGNIE_ORCL1@orcl1 c1
              WHERE c1.compagnie LIKE 'C%'
              AND NOT EXISTS (SELECT 1
                            FROM COMPAGNIE_ORCL2 c2
                            WHERE c1.username  = c2.username 
                            AND c1.ID   = c2.ID
                            AND c1.compagnie = c2.compagnie
                            AND c1.seq = c2.seq)) -- rajout du seq, il faut tous les cas 
  LOOP
		v := v + 1;
   INSERT INTO compagnie_orcl2 (USERNAME, COMPAGNIE, ID, SEQ)
   VALUES (rc.username, rc.compagnie, rc.ID, rc.seq); -- pourquoi avoir mis nextval ? 
  END LOOP;
  RETURN v; 
END comp;

Pourquoi avoir mis un nextval dans seq ? Si tu doit mettre la séquence de la base locale, alors la définition du problème n'est pas bonne, et il manquerait la règle de gestion pour cette colonne.

[teddi]

merci infiniment pour ces précision McM.

le traitement de la seq n' est pas encore très claire mais peut etre qu'il te faut d'abord ma reponse.

j' ai besoin du nexval par ce que orcl2 etant une coopie de orcl1, la sequence est definie séparément et independament sur les 2 bases.
ce qui fait que je ne peux pas tout simplement récupérer les valeurs de seq sur orcl1 dans orcl2.

[Scriuiw]

[QUOTE]Pourquoi utiliser un curseur alors qu'une instruction ensembliste fait l'affaire et sera bien plus efficace ?
aurais-tu un exemple stp?
Voir l'exemple de McM ou Skuatamad

Par ailleurs l'abus de COMMIT est à éviter, dans votre code vous en effectuez un à chaque boucle ce qui est très contre-productif.
le COMMIT est en fin de boucle. où devrais-je le mettre sinon?

Non le COMMIT est dans la boucle et ce n'est vraiment pas préconisé, il devrait plutôt après le "END LOOP;"

Sinon la fonction compile ?
oui la fonction compile.

D'ailleurs, mise à part une erreur, votre fonction retournera toujours vrai qu'il y ait eu ou non des lignes insérées, l'intérêt s'en trouve limité.
n'étant pas forcément un as de la programmation j'essaie justement par ce post de comprendre mon erreur.

Quel est l'intérêt pour vous d'avoir une fonction ?
Y a t-il un besoin spécifique ?

[teddi]

Merci pour ta réponse.
Les exemples envoyés par McM et Skuamat sont parfaits et je les ai déjà considérés.

Pour répondre à ta question: j'ai pensé utiliser une fonction par ce que j'avais la séquence à gérer.
Sachant que ce sont 2 séquences créés séparément l'une de l'autre. Il ne m'est donc pas possible de récupérer les valeurs de la séquence sur orcl1 et de les ajouter dans orcl2.

[tbc92]

Je ne sais pas si tu as les droits suffisants pour créer un TRIGGER sur ta table ( celle dans laquelle tu écris).
Si oui, voici une piste.

Tu fais ce INSERT/SELECT :

Code :

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

1
2
3
4
5
6
7
8
9
INSERT INTO COMPAGNIE_ORCL2 (username, ID, compagnie, seq)
SELECT username, ID, compagnie, NULL As seq
FROM COMPAGNIE_ORCL1@orcl1
WHERE (username, ID, compagnie) IN 
  (SELECT username, ID, compagnie FROM COMPAGNIE_ORCL1@orcl1  WHERE compagnie LIKE 'C%'
  MINUS
  SELECT username, ID, compagnie FROM COMPAGNIE_ORCL2 WHERE compagnie LIKE 'C%'
  )

La seule chose qui m'intéresse, c'est ce que j'ai mis en rouge : Tu mets NULL dans la colonne SEQ ... Pour le reste, j'ai repris telle quelle la requête de MCM, je n'ai pas vérifié si elle te convenait.

MAIS Avant de lancer ce Insert/Select , tu crées un Trigger sur ta table, pour dire :

Si on essaie d'insérer une ligne avec NULL dans la colonne SEQ, alors remplacer NULL par Seq_cmp.nextval

Le trigger en question traitera ton insert, mais il s'appliquera aussi à chaque fois que tu tenteras d'insérer une ligne avec NULL dans la colonne SEQ.

[teddi]

tbc92 merci beaucoup pour cette nouvelle piste. je n'avais pas pensé au trigger du tout.

voici comment j' ai l'intention de l'écrire. Ce serait ok?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE OR REPLACE TRIGGER secure_comp
 BEFORE INSERT OR UPDATE OF seq ON COMPAGNIE_ORCL2 
FOR EACH ROW
 
BEGIN
 
 IF INSERTING  THEN  :new.seq := Seq_cmp.nextval;
   ELSE 
    IF :old.seq IS NULL THEN :new.seq := Seq_cmp.nextval;
   END IF;
 END IF;
END;

[Scriuiw]

Attention s'il y a des intégrations en masse, le trigger va écrouler les performances.
Et faire une insertion à NULL dans une PK, je trouve l'idée scabreuse...

[teddi]

merci Scriuiw.

Attention s'il y a des intégrations en masse, le trigger va écrouler les performances.
Et faire une insertion à NULL dans une PK, je trouve l'idée scabreuse...

Qu'est ce que tu me proposes donc pour gérer ma séquence. c' est en fait le seul point qui me manque.

[McM]

Attention s'il y a des intégrations en masse, le trigger va écrouler les performances.
Et faire une insertion à NULL dans une PK, je trouve l'idée scabreuse...

Un trigger comme ça qui écroule les perfs ? Je ne pense pas.

Une insertion à NULL, oui, ou sans la définir (bon, généralement c'est une seule colonne ID), mais ça m'arrive.

Ensuite le trigger est mal formé je pense

Code :

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

1
2
3
IF INSERTING  THEN  :new.seq := Seq_cmp.nextval;
ELSIF :old.seq IS NULL THEN :new.seq := Seq_cmp.nextval;
END IF;

Premier test : Si tu insères une ligne avec une valeur dans seq, tu vas la remplacer par seq_cmp.nextval
Second test : Si tu updates une ligne qui a seq à NULL.. normalement c'est pas possible si c'est la PK, et ensuite tu remplaces la valeur que tu mets dans l'update par seq_cmp.nextval.

Pas très logique