Erreur Ora-32044 : cycle détecté avec WITH

Version imprimable

Salutation,

je crée actuellement un script qui doit la permettre de remplir une table a partir des données d'une autre table.

Dans la premiere table, j'ai
matricule | MemberOf
-------------------------------
0000001   | toto, tata, titi
0000002   | toto
0000003   | titi, tota
et je voudrais obtenir
matricule | MemberOf
-------------------------------
0000001   | toto
0000001   | tata
0000001   | titi
0000002   | toto
0000003   | titi
0000003   | tota
après mettre renseigné on m'a conseillé une boucle récursive qui découpe ma chaine Memberof en fonction des virgules... le problème est que lors de l'execution de ma requete je tombe sur cette erreur "ORA-32044: cycle détecté lors de l'exécution de la requête WITH récursive"

et que je comprend pas tres bien d'ou peut venir ce défaut...
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 WITH Recup (matricule, Unmemberof, LesAutresYga) AS ( SELECT MATRICULE, CASE WHEN INSTR(',', MEMBEROF)>0 THEN LTRIM(SUBSTR(MEMBEROF,1, INSTR(',', MEMBEROF) -1)) ELSE MEMBEROF END AS UNMEMBEROF, LTRIM(SUBSTR(MEMBEROF, INSTR(',', MEMBEROF) +1, LENGTH(MEMBEROF) - INSTR(',', MEMBEROF))) AS LESAUTRESYGA FROM COMPTES_AD UNION ALL SELECT COMPTES_AD.MATRICULE, CASE WHEN INSTR(',' , Recup.LesAutresYga)>0 THEN LTRIM(SUBSTR(Recup.LesAutresYga,1, INSTR(',',Recup.LesAutresYga) -1)) ELSE Recup.LesAutresYga END, CASE WHEN INSTR(',' , Recup.LesAutresYga)>0 THEN LTRIM(SUBSTR(Recup.LesAutresYga, INSTR(',', Recup.LesAutresYga) +1, LENGTH(Recup.LesAutresYga) - INSTR(',',Recup.LesAutresYga))) ELSE NULL END FROM COMPTES_AD INNER JOIN Recup ON Recup.Matricule = COMPTES_AD.Matricule WHERE LesAutresYga IS NOT NULL ) SELECT DISTINCT * FROM RECUP
merci de votre aide.

Edit : version d'oracle : inconnue (ce n'est pas moi qui gere cela mais je dirais 10g ou 11g...)

02/12/2013, 10h44
skuatamad

Regardez [Oracle 10g]Transformer les éléments d'une liste en enregistrement

PS : Si vous pouvez faire du WITH récursif c'est que vous êtes en 11GR2, mais vous pouvez le vérifier en exécutant :

Code:

select * from v$version

Sinon, je te propose :

Code:

1
2
3
4
5
6
7
8
9
10
11
with sample_data as
(
 select '0000001' matrice, 'toto, tata, titi' memberof from dual union all
 select '0000002', 'toto' from dual union all
 select '0000002', 'titi, tota' from dual
)
select matrice, cast(t.column_value.extract('//text()') as varchar2(10)) val
  from sample_data,
  table( xmlsequence( xmltype(
    '<x><x>' || replace(replace(memberof,' ',null), ',', '</x><x>') || '</x></x>'
  ).extract('//x/*'))) t;

Citation:

MATRICE VAL
------- ----------
0000001 toto
0000001 tata
0000001 titi
0000002 toto
0000002 titi
0000002 tota

Note : replace(memberof,' ',null) pour supprimer les espaces ;)

02/12/2013, 10h53
nid4mail

Bonjour,

merci à toi pour ta reponse.

concernant la version j'obtiens : Oracle Database 11g Release 11.2.0.3.0 - 64bit Production. donc oui je pense que c'est la 11gR2. pour la liste je vais regarder ca et je tiens au jus...

Citation:
Envoyé par orafrance
Sinon, je te propose :
Code:

1 2 3 4 5 6 7 8 9 10 11 with sample_data as ( select '0000001' matrice, 'toto, tata, titi' memberof from dual union all select '0000002', 'toto' from dual union all select '0000002', 'titi, tota' from dual ) select matrice, cast(t.column_value.extract('//text()') as varchar2(10)) val from sample_data, table( xmlsequence( xmltype( '<x><x>' || replace(replace(memberof,' ',null), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;
Note : replace(memberof,' ',null) pour supprimer les espaces ;)
Cette solution est bien sauf que j'ai 1650 matricule et un nombre de memberOf aléatoires compris entre 0 et 16000 pour chaque matricule...

02/12/2013, 10h59
skuatamad
Citation:

Envoyé par nid4mail

Cette solution est bien sauf que j'ai 1650 matricule et un nombre de memberOf aléatoires compris entre 0 et 16000 pour chaque matricule...

La partie :
Code:

1 2 3 4 5 6 WITH sample_data AS ( SELECT '0000001' matrice, 'toto, tata, titi' memberof FROM dual union ALL SELECT '0000002', 'toto' FROM dual union ALL SELECT '0000002', 'titi, tota' FROM dual )
sert à simuler des données pour tester la requête, vous n'avez pas à l'utiliser dans votre requête finale.

En effet, la requête serait quelque chose de ce style:

Code:

1
2
3
4
5
SELECT matrice, cast(t.column_value.extract('//text()') AS varchar2(10)) unmemberof
  FROM comptes_ad,
  TABLE( xmlsequence( xmltype(
    '<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>'
  ).extract('//x/*'))) t;

Citation:
Envoyé par orafrance
En effet, la requête serait quelque chose de ce style:
Code:

1 2 3 4 5 SELECT matrice, cast(t.column_value.extract('//text()') AS varchar2(10)) unmemberof FROM comptes_ad, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;
Et dans mon With je ferais un simple

Code:

Select Matricule, MemberOf from Comptes_AD dual

?

(je debute avec Oracle je patauge un peu beaucoup (merci de votre patience en tout cas)

non, pas de WITH du coup :)

Essaye la requête telle quelle déjà, et vois ce qui ne va pas... ensuite, nous t'aiderons à l'adapter à ton besoin final ;)

Edit :

Je parle bien de celle-ci :
Code:

1 2 3 4 5 SELECT matrice, cast(t.column_value.extract('//text()') AS varchar2(10)) unmemberof FROM comptes_ad, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;

Je te dois tout de même des explications:
Code:

1 2 3 4 5 6 WITH sample_data AS ( SELECT '0000001' matrice, 'toto, tata, titi' memberof FROM dual union ALL SELECT '0000002', 'toto' FROM dual union ALL SELECT '0000002', 'titi, tota' FROM dual )
Cela m'évite juste de faire un CREATE TABLE sample_data et des INSERT. C'est pour créer un échantillon de données répondant à ton hypothèse de départ.
Code:

1 2 TABLE( xmlsequence( xmltype('<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;
Cette partie de la requête supprime les espaces de la colonne MEMBEROF et remplace les virgules par </x><x>, récupère chacun des éléments (extract) d'une valeurs en XML (XMLTYPE) et retourne le tout en forme de table (TABLE)
Code:

1 2 3 4 5 6 7 8 9 10 WITH sample_data AS ( SELECT '0000001' matrice, 'toto, tata, titi' memberof FROM dual union ALL SELECT '0000002', 'toto' FROM dual union ALL SELECT '0000002', 'titi, tota' FROM dual ) SELECT t.* FROM sample_data, TABLE( xmlsequence( xmltype('<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;
donne donc :

Citation:

COLUMN_VALUE
--------------------------
<x>toto</x>
<x>tata</x>
<x>titi</x>
<x>toto</x>
<x>titi</x>
<x>tota</x>

6 rows selected.

Récupérons juste le texte (column_value.extract('//text()')) et la matrice et voila la requête et le résultat qui est dans mon premier post:
Code:

1 2 3 4 5 6 7 8 9 10 11 WITH sample_data AS ( SELECT '0000001' matrice, 'toto, tata, titi' memberof FROM dual union ALL SELECT '0000002', 'toto' FROM dual union ALL SELECT '0000002', 'titi, tota' FROM dual ) SELECT matrice, cast(t.column_value.extract('//text()') AS varchar2(10)) val FROM sample_data, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;
Ne reste plus qu'à travailler sur la table au lieu de l'échantillon, et on arrive à :
Code:

1 2 3 4 5 SELECT matrice, cast(t.column_value.extract('//text()') AS varchar2(10)) val FROM comptes_ad, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(memberof,' ',NULL), ',', '</x><x>') || '</x></x>' ).extract('//x/*'))) t;

Un grand merci à ORA-FRANCE et SKUATAMAD qui m'ont tiré une vilaine épine du pied par un code tout simple!!

Voici le code tel qu'il marche pour moi :
Code:

1 2 3 4 5 6 SELECT matricule, cast(t.column_value.extract('//text()') AS varchar2(50)) unmemberof FROM COMPTES_AD, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(MEMBEROF,' ',NULL), ',', '</x><x>') || '</x></x>' ).EXTRACT('//x/*'))) T ORDER BY MATRICULE;
Bon et a present si je peux abuser je souhaiterai que cette table ne retiennent que les memberof qui commence par yga_toto...

EDIT en gros tous mes memberOF c'est pas toto tata etc mais ils commencent tous par yga_dossier_XXXX (avec un nombre de X variant)

Code:

WHERE memberof LIKE 'yga_toto%'

Et le tour est joué... le SQL c'est facile comme de l'anglais : OÙ memberof COMME... % c'est l'équivalent de * pour les fichiers sous DOS ;)
Code:

1 2 3 4 5 6 7 SELECT matricule, cast(t.column_value.extract('//text()') AS varchar2(50)) unmemberof FROM COMPTES_AD, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(MEMBEROF,' ',NULL), ',', '</x><x>') || '</x></x>' ).EXTRACT('//x/*'))) T WHERE memberof LIKE 'yga_toto%' ORDER BY MATRICULE;

02/12/2013, 11h29
nid4mail

Citation:

Envoyé par orafrance

Code:

WHERE memberof LIKE 'yga_toto%'

Et le tour est joué... le SQL c'est facile comme de l'anglais : OÙ memberof COMME... % c'est l'équivalent de * pour les fichiers sous DOS ;)

Ce n'est pas tout a fait ce que je veux puisque il me retourne toutes les lignes de tous les matricules qui ont au moins un yga_dossier...
moi je voudrais que les lignes de yga_dossier

exemple soit mon matricule 001
je recupere 001|yga_dossier_toto et 001|yga_fichier_alpha (or je veux pas la ligne de alpha...)
02/12/2013, 11h32
orafrance

Dans ce cas faut me dire pourquoi tu veux toto et pas alpha... c'est cette condition ensuite qu'il faudra formaliser en SQL ;)
02/12/2013, 11h34
nid4mail

Citation:

Envoyé par orafrance

Dans ce cas faut me dire pourquoi tu veux toto et pas alpha... c'est cette condition ensuite qu'il faudra formaliser en SQL ;)

parce que l'un c'est yga_dossier_XXX et que l'autre c'est yga_fichier_YYYY je veux que les dossiers
02/12/2013, 11h38
orafrance

ha oui... ben LIKE 'yga_dossier%'
02/12/2013, 11h48
nid4mail

j'ai mis :

Code:

WHERE unmemberof LIKE 'yga_dossier%'

mais il ne connait pas unmemberof si je mets memberof il me ramene tout y compris les fichiers. D'apres ce que je comprends, il ramene tout les matricules qui ont au moins un yga_dosier dans la chaine initiale avant le decoupage or moi je voudrais que le test se fasse apres le decoupage en fait...

Ha oui, j'ai compris (oui... enfin :mouarf:), on ressort le WITH alors :mrgreen:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
WITH ALL_COMPTES_AD AS
(
SELECT matricule, cast(t.column_value.extract('//text()') AS varchar2(50)) unmemberof
  FROM COMPTES_AD,
  TABLE( xmlsequence( xmltype(
    '<x><x>' || REPLACE(REPLACE(MEMBEROF,' ',NULL), ',', '</x><x>') || '</x></x>'
  ).EXTRACT('//x/*'))) T
)
SELECT * 
  FROM ALL_COMPTES_AD 
WHERE unmemberof LIKE 'yga_dossier%'
ORDER BY MATRICULE;

Citation:
Envoyé par orafrance
Ha oui, j'ai compris (oui... enfin :mouarf:), on ressort le WITH alors :mrgreen:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 WITH ALL_COMPTES_AD AS ( SELECT matricule, cast(t.column_value.extract('//text()') AS varchar2(50)) unmemberof FROM COMPTES_AD, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(MEMBEROF,' ',NULL), ',', '</x><x>') || '</x></x>' ).EXTRACT('//x/*'))) T ) SELECT * FROM ALL_COMPTES_AD WHERE unmemberof LIKE 'yga_dossier%' ORDER BY MATRICULE;
ET CA MARCHE!! le probleme est resolu!! mes respects! plus qu'a insere ce resultat dans ma table Droits_Dossier et le tour est joué!!

je te remercie du fond du coeur pour ce dépatouillage!!

02/12/2013, 12h01
orafrance

Penses au bouton résolu, content d'avoir pu t'aider :ccool: