[Oracle9.]problème lors de l'utilisation d'un buffer avec BULK COLLECT

Bonjour,
pour traiter des lignes de ma table , j'utilise la BULK COLLECT pour allimenter le buffer, et je parcours le buffer pour réaliser des inserts .

Mon probleme c'est que le buffer contient les meme enregistrement lors de la premiere et la dexième itérations, ce qui donne une insertion deux fois des meme lignes

voila un extrait :

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

TYPE cache_tt IS TABLE OF my_table%ROWTYPE INDEX BY PLS_INTEGER;   my_cache cache_tt; l_row PLS_INTEGER; ...   begin   ... remplisssage de la table my_table   WHILE performed LOOP performed :=FALSE; SELECT * BULK COLLECT INTO my_cache FROM my_table WHERE status = 0 AND ROWNUM<5;   if my_cache.count > 0 then DBMS_OUTPUT.PUT_LINE('Count my_cache :'|| my_cache.count);   FOR l_row IN my_cache.FIRST .. my_cache.LAST LOOP performed := TRUE; ... procedure_insert(my_cache(l_row).co_id ....) ... END LOOP;   COMMIT; end if; END LOOP;

---

il insert deux fois les 4 premières lignes !! puis une fois le reste ..:roll:

quelqu'un à une idée !!

SELECT DISTINCT si tu as des doublons ;)

Non j'ai pas des doublons !!

et tu l'incrémentes où l_row ?

Tu peux pas simplement faire un INSERT AS SELECT... ton code parait bien compliqué pour si peu de chose :koi:

je l'inscrimente pas !!!
j'utilise

Code:

---

1 2 3 4

FOR l_row IN my_cache.FIRST .. my_cache.LAST LOOP ... END LOOP;

---

le reste du code c'est plus compliquer, j'utilise des point de sauvegarde et je traite les exception !! afin de faire un roolback au point de sauvegarde !!

ha oui... au temps pour moi :oops:

bah honnétement, je crains que le problème ne vienne pas des lignes que tu as citée :?

le reste n'a rien a voir avec l'insert !!
et le problème c'est qu'il m'insert deux foix les 4 premieres lignes (donc le contenu de mon buffer), le problème c'est que le buffer ne s'incrimente pas lors de la premiere itération , mais tout marche bien aprés , c'est a dire qu'il insert correctement 4 par 4 par la suite !!!

Code:

---

1 2	SELECT * BULK COLLECT INTO my_cache FROM my_table WHERE STATUS = 0 AND ROWNUM<5;

---

Est-ce qu'il n'y pas un problème avec le fait que vous utilisez ROWNUM sans clause ORDER BY ? S'il n'y a pas de clause ORDER BY, les 5 "premières" lignes d'une requête sont purement aléatoires... Et si vous utilisez ORDER BY et ROWNUM , voir
http://oracle.developpez.com/faq/?page=3-1#rownum

Citation:

---

Envoyé par Fiora

le reste n'a rien a voir avec l'insert !!
et le problème c'est qu'il m'insert deux foix les 4 premieres lignes (donc le contenu de mon buffer), le problème c'est que le buffer ne s'incrimente pas lors de la premiere itération , mais tout marche bien aprés , c'est a dire qu'il insert correctement 4 par 4 par la suite !!!

---

rien ne me dit qu'il n'y a pas un bout de code qui fait qu'on revient en arrière :?

je réalise des testes, et je rend compte que mon programme marche parfaitement si la table est déja remplie avant de lancer le script, mais dans le cas ou je remplie en début de programme il insert deux fois le premier buffer

Mais franchement , je vois pas la relation !!

voila le code de remplissage du table :
select count(*) into i from my_table;

Code:

---

1 2 3 4 5 6 7 8

if i=0 then insert into my_table select ..... , 0 AS status FROM CONTRACT_ALL co .... and ROWNUM < 10 order by co.co_id; end if;   SELECT COUNT(1) INTO i FROM my_table WHERE status=0;   IF i > 0 THEN ....

---

SQL%ROWCOUNT permet d'éviter ce genre d'écriture scabreuse si le but est de récupérer uniquement les lignes insérées ;)

oui,
mais j'utilise le meme script en mode reprise , si y'aura des rallback , je patch le status des lignes concernées !

si pour ça y'a le test :

Code:

---

1 2 3 4

SELECT COUNT(1) INTO i FROM my_table WHERE status=0;   IF i > 0 THEN ...

---

et le

Code:

---

AND ROWNUM < 10

---

c'est juste pour prendre un échantillon de 10 ligne

Citation:

---

Envoyé par Fiora

Bonjour,
pour traiter des lignes de ma table , j'utilise la BULK COLLECT pour allimenter le buffer, et je parcours le buffer pour réaliser des inserts .

Mon probleme c'est que le buffer contient les meme enregistrement lors de la premiere et la dexième itérations, ce qui donne une insertion deux fois des meme lignes

voila un extrait :

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

TYPE cache_tt IS TABLE OF my_table%ROWTYPE INDEX BY PLS_INTEGER;   my_cache cache_tt; l_row PLS_INTEGER; ...   begin   ... remplisssage de la table my_table   WHILE performed LOOP performed :=FALSE; SELECT * BULK COLLECT INTO my_cache FROM my_table WHERE status = 0 AND ROWNUM<5;   if my_cache.count > 0 then DBMS_OUTPUT.PUT_LINE('Count my_cache :'|| my_cache.count);   FOR l_row IN my_cache.FIRST .. my_cache.LAST LOOP performed := TRUE; ... procedure_insert(my_cache(l_row).co_id ....) ... END LOOP;   COMMIT; end if; END LOOP;

---

il insert deux fois les 4 premières lignes !! puis une fois le reste ..:roll:

quelqu'un à une idée !!

---

J'ai l'impression qu'il y a un bins avec ta variable performed, elle est testée au niveau du loop et pas avant l'insert. Elle mise à jour à TRUE lors de ton premier passage dans la boucle, elle n'est réinitialisée juste après le début de boucle mais ça ne l'empèche pas de rentrer dans la 2ieme boucle... du coup tu as droit à un 2ieme passage avec le meme insert.
(bon ok c'est pas très net mon explication, peut etre il faudrait avoir l'essemble du code pour comprendre....)

La variable performed sert a empecher de boucler s'il y'a plus le ligne a remplir en cache

Code:

---

SELECT * BULK COLLECT INTO my_cache FROM my_table WHERE STATUS = 0 AND ROWNUM<5;

---

J'ai trouvé la faute!!!
Il me manque un COMMIT apres l'insertion

Code:

---

1 2 3 4

if i=0 then insert into my_table select co.co_id, .... 0 AS status FROM CONTRACT_ALL co, ... WHERE ... and ROWNUM < 10 order by co.co_id; COMMIT; end if;

---

Mais ça n'émpèche que j'ai pas compris le fonctionnement !! pourquoi le

Code:

---

SELECT * BULK COLLECT

---

réagi comme ça quand c'est pas commiter

Merci bcp pour votre aide, c'est sympa
;)

:koi:

C'est bizarre, à moins que ta procédure d'insertion soit déclarée en "AUTONOMOUS"....

Effectivement, ma procedure d'insertion est en PRAGMA AUTONOMOUS_TRANSACTION

et j'utilise le ROLLBACK vers des points de sauvegares

t'as une explication du probleme ?!!

je parle du procedure d'insertion appeler au milieu de la boucle !!

je parle pas du remplissage de la table (la ou j'ai oublier le COMMIT)

Citation:

---

Envoyé par Fiora

Effectivement, ma procedure d'insertion est en PRAGMA AUTONOMOUS_TRANSACTION

---

J'aurais du être devin... ;)

Explication:
Une procédure autonome se comporte comme si c'était quelqu'un d'autre qui se connectais sur une autre session.
Tu ne fais pas de commit apres ton remplissage de table donc la procédure autonome vois ta table comme elle était avant. Exactement comme si quelqu'un d'autre faisait un select alors que toi tu n'a pas fait de commit...

Encore une fois, la clé était dans le code qu'on pensais sans importance...:aie:

C'est plus claire ton explication

mais mon problème c'était pas ça, vu que le problème persiste même si je désactive l'appel au procedure qui fait le AUTONOMOUS_TRANSACTION ...

pour être plus claire, je sauvegarde les valeur de mon buffer dans une variable (donc oubliant l'insertion au milieu de la boucle), et j'ai lancer le pas à pas

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

insert into my_table select co.co_id, .... 0 AS status FROM CONTRACT_ALL co .. WHERE .... and ROWNUM < 10 order by co.co_id; --COMMIT; end if;     WHILE performed LOOP performed :=FALSE; SELECT * BULK COLLECT INTO my_cache FROM my_table WHERE status = 0 AND ROWNUM<5;   if my_cache.count > 0 then DBMS_OUTPUT.PUT_LINE('Count my_cache :'|| my_cache.count);   FOR l_row IN my_cache.FIRST .. my_cache.LAST LOOP performed := TRUE; SAVEPOINT P1;   BEGIN my_co_id := my_cache(l_row).co_id; ...   END;   END LOOP;   --COMMIT; end if; END LOOP;

---

en désactivant les deux COMMIT, je tembe dans une boucle infini qui boucle sur le premier buffer (qui contien 4 lignes) !!

Je pense que le problème viens du faite que le BULK COLLECT ramène les meme ligne si la table n'est pas commiter ( ce qui explique qu'il boucle deux fois juste la première selection dans mon premier programme, vu que le deuxième commit était activé ), pour que ça marche il faut activer les deux commit ! :P

:koi:

Ca m'etonne un peu je dois dire......

Il faudrait essayer sans le COMMIT ni SAVEPOINT, j'ai peur qu'il empile frénétiquement les transactions si tu mets ce dernier dans une boucle...

Le fait de faire un commit te dépilierait tout d'un coup et te masquerait le problème... (mais là j'essaye encore de jouer au devin, c'est pas clair que ça marche à tous les coups! ;))

alors la ! c'est raté
:cry:
j'ai relancé sans les commit ni sauvepoint , et c'est pareil, une boucle infinie

fausse hypothèse

dans ta session courante, il voit les lignes insérées et continue la boucle... ça parait logique non ?

Oui, avec le code tel qu'il est, c'est normal qu'il y ait une boucle infinie, ce qui est étonnant c'est plutot l'inverse, à savoir qu'un commit fasse sortir de la boucle... j'ai l'impression qu'il y a encore "un détail qui tue" caché parmis tes points de suspensions.... ;)

Citation:

---

Envoyé par remi4444

... j'ai l'impression qu'il y a encore "un détail qui tue" caché parmis tes points de suspensions.... ;)

---

Voila mon programme (sans les déclaration et la création de table ...), la partie qui nous intéresse:

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 33 34 35 36 37 38 39 40 41 42

i:=0;   select count(*) into i from my_table;   if i=0 then insert into my_table select co.co_id,0 AS status FROM CONTRACT_ALL co WHERE .... and ROWNUM < 10 order by co.co_id; . --COMMIT; end if;   SELECT COUNT(1) INTO i FROM my_table WHERE status=0;   IF i > 0 THEN   WHILE performed LOOP performed :=FALSE; SELECT * BULK COLLECT INTO my_cache FROM my_table WHERE status = 0 AND ROWNUM<5;   if my_cache.count > 0 then DBMS_OUTPUT.PUT_LINE('Count my_cache :'|| my_cache.count);   FOR l_row IN my_cache.FIRST .. my_cache.LAST LOOP performed := TRUE; --SAVEPOINT P1;   BEGIN v_co_id := my_cache(l_row).co_id;   /*   return_code := appel_function;     IF return_code!=2 THEN ROLLBACK TO P1; END IF;*/ END;   END LOOP;   --COMMIT; end if; END LOOP;

---

comme il est, ça boucle en infinie !, pour que ça marche il faut commiter la table (soit au debut , soit dans la boucle)

Ma conclusion: le BULK COLLECT ramene le meme bloc de lignes pour une sélection sur une table non commiter ;)

Merci pour votre aides

8O Mais c'est normal qu'il boucle infini puisque tu fais toujours la même requête de remplissage sans rien changer de ta table my_table dans ta boucle.Ce qui est étonnant c'est que le commit fasse sortir d'une telle boucle... ça serais quand meme plus rassurant de comprendre ce qui se passe...

Remarque:
Attention sur ta requête de remplissage initiale, j'ai peur que tu te leurres sur le fonctionnement du rownum. Telle que tu as écrit ta requête, elle fait d'abord le comptage et après le "ORDER BY", tu n'as donc pas les 9 premieres ligne dans l'ordre co_id. Pour avoir ça il faut le faire en 2 temps.

http://oracle.developpez.com/faq/?page=3-1#rownum

Ahhh si j'ai peut etre une idée! est-ce que tu as déclaré des table en temporaire sans préciser "ON COMMIT PRESERVE ROWS" ??

Citation:

---

Envoyé par remi4444

Ahhh si j'ai peut etre une idée! est-ce que tu as déclaré des table en temporaire sans préciser "ON COMMIT PRESERVE ROWS" ??

---

Non, j'utilise pas de table temporaire !