Discussion :

[santana2006]

Bonjour,

J'ai la situation suivante :

3 tables dans une base de données Oracle :

1/ PRODUIT : id, code, description, label.
2/ PARAMETRE : id, nom, valeur

Relation plusieurs à plusieurs entre produit et parametre, donc :
3/ PRODUIT_PARAMETRE : table croisée : produit_id, parametre_id

Mon besoin est le suivant :

je veux, pour chaque produit, modifier la valeur du label, pour qu'elle prenne comme nouvelle valeur : la concaténation du code, de la description du produit, et de tous les couples nom-valeur des paramètres.

Exemple de résultat que je veux obtenir: Pour un produit (code='c1', description='blabla'), ayant 3 paramètres définis ((nom='p1', valeur='v1'), (nom='p2', valeur='v2'), (nom='p3', valeur='v3')), le résultat attendu est :

'c1, blabla / p1=v1, p2=v2, p3=v3'

Merci pour votre aide

[argoet]

Une petite procedure PL/SQL faira grandement L'affaire
A vous de l'adapter selon votre besoin

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
Declare
    V_New_Lab T_PRD.label%TYPE; 
Begin
    ------------------------------------------------------
    -- Pour L'ensemble des Produits
    ------------------------------------------------------
    For C_Prd in (Select id from T_Prd) 
    Loop 
        V_New_lab := '';
        ------------------------------------------------------
        -- Pour L'ensemble des Parametrages du produit 
        ------------------------------------------------------
        For C_Par in (Select P.nom,P.valeur from T_PAR P,T_Prd_Par PP
                      Where PP.prd_id = C_Prd.id) 
        Loop
             V_New_Lab := V_New_Lab || C_par.nom ||'='||C_Par.valeur||', ';
        End Loop;
        If length(V_New_Lab) > 1 Then
             Update T_Prd Set label = V_New_Lab Where id = C_Prd.id;
        End If;
    End Loop;
End;
/

[santana2006]

merci bien

mais mon problème c'est qu'il me faut une requete sql, car d'une part je ne maitrise pas les procédures stockées, et d'autre part parce que dans le contexte du projet dans lequel je travaille je dois fournir une requête.

Cette procédure peut être traduite en requête ?

[pacmann]

Salut !

La requête pour agréger :

Code :

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

1
2
3
4
5
6
7
 
select a.id, max(a.code) || ', ' || max(a.description) || 
replace(replace(xmlagg(xmlelement("x", b.nom || '=' || b.valeur)), '<x>', ', '), '</x>', ' ')
from produit a 
  JOIN produit_parametre c ON a.id = c.produit_id
  JOIN parametre b ON b.id_produit = c.id
GROUP BY a.id

=> Oracle permet l'agrégation d'éléments XML (qu'il faut donc d'abord créer avec la fonction XMLElement)
Après, il suffit de nettoyer les balises...

(Avant je m'emcombrais toujours de CONNECT BY pour faire ça, jusqu'à ce que je profite des recherches de Waldar )

Je te laisse utiliser ce morceau de requête pour faire l'update ensuite...

[Waldar]

Ce ne sont pas vraiment mes recherches, ce sont des liens que j'ai pompé ici ou là, ce qui est la façon classique de résoudre un problème de nos jours !

[santana2006]

good job

mais dans la requete il y a cette jointure :

Code :

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

JOIN parametre b ON b.id_produit = c.id

qui suppose que dans la table paramteres on a une clé etrangere liée à la table de produits

alors dans mon cas c un many to many, et la jointure est dans une table croisée

donc j'ai pas de clé etrangere ni dans la table produit ni la table parametres

[pacmann]

Hmmm, tu peux ajouter la table croisée dans la jointure pour faire le lien ?

Waldar : pomper des liens est pour moi une recherche comme une autre... finalement, on n'invente pas si souvent de nouvelles méthodes pour des problématique très courantes

Par contre, si je ne me trompe pas, petit inconvénient du XMLElement que j'ai rencontré en essayant de dessiner des coeurs : il y a eu des régressions entre Oracle 9 et 10 !
(J'ai passé pas mal de temps à chercher si c'étaient peut être des problèmes de charset, de nls machins, ...)

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
 
BANNER
-----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
 
SQL> SELECT xmlelement("x", chr(3)) from dual
  2  /
 
XMLELEMENT("X",CHR(3))
-----------------------------------------------------------
<x>♥</x>

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
 
SQL> select * from v$version
  2  /
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL> SELECT xmlelement("x", chr(3)) from dual
  2  /
ERROR:
ORA-31011: Echec d'analyse XML

It took 2 weeks to understand the real problem (My development skills are not as good as my DBA skills and I did not get any proper help from development that’s why it took longer than it needs ) . Basically application gets data from database converts it to XML (by DBMS_XMLGen) and writes it to a file (by DBMS_LOB). We tried every possible solution we found from google and metaling for the error stacks and finally we understand that XML generation was the problem. DBMS_XMLGEN.GETXML was not generating anything for some records but it was generating in 9i. To be honest The error stack (was using DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.format_error_backtrace) was not very clear to me thats why I spent too much time with other things.
According to the bug numbers below DBMS_XMLGEN.GETXML is not working same in 10R2 as it is working on 9i.
The way it handles nulls and special characters is buggy. Workaround it using DBMS_XMLQUERY instead.
Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR
Bug No. 8476233 DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2
Bug No. 6445329 GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS
Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR
Bug No. 8476233 DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2
Bug No. 6445329 GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

Comme je connais pas grand chose aux fonctionnalités XML, je ne suis pas sûr que mon problème rentre dans ces bugs (vu qu'il parle de GETXML()), mais ça y ressemble !

[santana2006]

ok

merci pour tous pour votre aide