oracle et xml

**r83** · 15/10/2009, 21h15

Bonjour,

j'ai les tables suivantes :
Produit(id prod, libelle, puht) pk:idprod
Commande(idcde, datecde) pk:idcde
Prodcde (idprod,idcde, qte) pk:idcde,idprod
plus les fk.

J'aimerais sortir un fichier xml du type :

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
 
<lescommandes>
	<commande id=123>
		<nomcli> Dupont</nomcli>
		<datecde> 12/03/2009</datecde>
		<lignesCde>
			<idprod id='P1'>
				<Qte>12</qtre>
			</idprod>
			<idprod id='P2'>
				<Qte>6</qtre>
			<idprod>
		<lignesCde>
	</commande>
		<commande id=124>
		<nomcli> Durand</nomcli>
		<datecde> 15/05/2009</datecde>
		<lignesCde>
			<idprod id='P3'>
				<Qte>5</qtre>
			</idprod>
			<idprod id='P2'>
				<Qte>4</qtre>
			<idprod>
			<idprod id='P2'>
			<Qte>4</qtre>
			<idprod>
		<lignesCde>
	</commande>
</lescommandes>

J'ai bien réussi à sortir un semblant de XML, mais les éléments se répètent.
Dans le curseur retourné, j'aimerais que les lignes ci-dessus apparaissent en l'état...
J'ai testé XMLElement et XMLagg, mais chaque ligne correspond au gropu by, ce que je ne veux pas.
Merci pour votre aide

**mnitu** · 16/10/2009, 08h58

Postez un petit jeu d'essaie: ordre sql de création des tables plus les inserts de quelques lignes pour chaque table, le résultat attendu et votre requête.

**r83** · 16/10/2009, 10h11

Bonjour,

Je donne le code en espérant que ce ne soit pas trop long :
Les tables :

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
 
drop table commande cascade constraints;
drop table produit cascade constraints;
drop table prodcde cascade constraints;
create table produit(
idprod  integer primary key,
libprod varchar(50) not null,
puprod decimal(6,2) not null
);
/
create table commande(idcde integer primary key, 
datecde date not null);
/
create table Prodcde (idprod integer references produit(idprod),
idcde integer references commande(idcde), 
qte integer default 1 not null,
constraint pk_prodcde primary key(idprod, idcde));
/
 
insert into produit (idprod,libprod,puprod)  values (100,'stylo',55);
insert into produit (idprod,libprod,puprod)  values (101,'gomme',114);
insert into produit (idprod,libprod,puprod)  values (102,'règle',13.9);
 
insert into commande(idcde, datecde) values (1234, sysdate);
insert into commande(idcde, datecde) values (1235, sysdate);
 
insert into prodcde(idcde, idprod, qte) values (1234,100,12);
insert into prodcde(idcde, idprod, qte) values (1234,101,8);
insert into prodcde(idcde, idprod, qte) values (1235,100,6);
insert into prodcde(idcde, idprod, qte) values (1235,101,14);
insert into prodcde(idcde, idprod, qte) values (1235,102,9);
 
commit;
select * from commande;
select * from prodcde;
select * from produit;

La requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT (XMLElement(name "commande", 
                  XMLAttributes(commande.idcde as id), 
                  XMLElement(name "datecommande",datecde), 
                  XMLElement(name "LigneCde",null),  
                  XMLForest(idprod as "id", libprod as "libelle", qte as "qteCommande")
	          )).getStringVal() as RESULTAT
FROM commande inner join prodcde on commande.idcde=prodcde.idcde
inner join produit on prodcde.idprod=produit.idprod
order by commande.idcde;

Le résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
RESULTAT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<commande ID="1234"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>101</id><libelle>gomme</libelle><qteCommande>8</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
<commande ID="1234"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>100</id><libelle>stylo</libelle><qteCommande>12</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
<commande ID="1235"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>102</id><libelle>règle</libelle><qteCommande>9</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
<commande ID="1235"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>100</id><libelle>stylo</libelle><qteCommande>6</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
<commande ID="1235"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>101</id><libelle>gomme</libelle><qteCommande>14</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
 
5 rows selected

et voilà ce que j'aurais aimé avoir (et avant tout si je peux espérer obtenir ça (en dehors de la mise en page

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
 
RESULTAT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<commande ID="1234">
	<datecommande>2009-10-16</datecommande>
	<LigneCde>
		<id>101</id>
		<libelle>gomme</libelle>
		<qteCommande>8</qteCommande>
	</LigneCde>	
	<LigneCde>	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
		<id>100</id>
		<libelle>stylo</libelle>
		<qteCommande>12</qteCommande>
	</LigneCde>
</commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
<commande ID="1235">
	<datecommande>2009-10-16</datecommande>
	<LigneCde>
		<id>102</id>
		<libelle>règle</libelle>
		<qteCommande>9</qteCommande>
	</LigneCde>
	<LigneCde>
		<id>100</id>
		<libelle>stylo</libelle>
		<qteCommande>6</qteCommande>
	</LigneCde>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
	<LigneCde>
		<id>101</id>
		<libelle>gomme</libelle>
		<qteCommande>14</qteCommande
	</LigneCde>
</commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
 
5 rows selected

merci pour vos réponses
Bonne journée

**mnitu** · 16/10/2009, 11h04

Voilà une solution

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
 
SELECT (XMLElement(name "commande", 
                  XMLAttributes(commande.idcde AS id), 
                  XMLElement(name "datecommande",datecde), 
                  (Select XmlAgg(XmlElement("LigneCde",
                                              XMLForest(produit.idprod AS "id", 
                                                        libprod AS "libelle", 
                                                        qte AS "qteCommande")
                                           )
                                 )                                 
                     From prodcde 
                          Inner Join produit
                       On (prodcde.idprod=produit.idprod) 
                    Where prodcde.idcde = commande.idcde
                  )
            )).extract('/').getstringval() AS RESULTAT
FROM commande 
ORDER BY commande.idcde
/

Y en a, bien sûr, des autres.

**Waldar** · 16/10/2009, 11h12

On peut se passer du sous-select scalaire en utilisant les propriétés d'agrégat de XMLAgg, ce qui donne je pense un meilleur plan d'exécution :

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
44
45
46
47
48
49
50
51
52
53
54
SELECT
    XMLElement(name "commande",
               XMLAttributes(cm.idcde AS id),
               XMLElement(name "datecommande", cm.datecde), 
               XMLAgg(XMLElement(name "LigneCde",
                                 XMLForest(pr.idprod AS "id",
                                           pr.libprod AS "libelle",
                                           pc.qte AS "qteCommande"))
	          )).getstringval() AS RESULTAT
FROM
    commande cm
    INNER JOIN prodcde pc
      ON pc.idcde = cm.idcde
    INNER JOIN produit pr
      ON pr.idprod = pc.idprod
GROUP BY
    cm.idcde,
    cm.datecde
ORDER BY
    cm.idcde asc;
 
 
<commande ID="1234">
  <datecommande>2009-10-16</datecommande>
  <LigneCde>
    <id>100</id>
    <libelle>stylo</libelle>
    <qteCommande>12</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>101</id>
    <libelle>gomme</libelle>
    <qteCommande>8</qteCommande>
  </LigneCde>
</commande>
 
<commande ID="1235">
  <datecommande>2009-10-16</datecommande>
  <LigneCde>
    <id>100</id>
    <libelle>stylo</libelle>
    <qteCommande>6</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>102</id>
    <libelle>règle</libelle>
    <qteCommande>9</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>101</id>
    <libelle>gomme</libelle>
    <qteCommande>14</qteCommande>
  </LigneCde>
</commande>

**r83** · 16/10/2009, 11h33

Super, je suis heureux d'apprendre que c'est faisable!!!
Je comprends mieux la solution de la solution de Waldar que celle de mnitu, où je n'arrive pas bien à déchiffrer la vue scalaire. Je vais travailler dessus.
Avez-vous un bon tuto là-dessus, je n'ai rien de très probant sur le net (en français???).
Je suppose que l'expression : .getstringval() sert à renvoyer le résultat sous forme de chaine de caractères, mais par définition il renverrait une chaine non?
Et pourquoi l'expression : .extract('/').getstringval()
Encore un grand merci pour votre aide
Bonne journée

**Waldar** · 16/10/2009, 12h06

Il y a deux pdf d'Atkins Ken qui sont très très bons, je n'ai plus le site sous la main.

**mnitu** · 16/10/2009, 13h02

Les liens se trouvent ici.

**r83** · 17/10/2009, 00h19

Bonsoir,

encore un petit souci, ma requête marche très bien en sql
Dans un package plsql, j'ai l'erreur

Code :

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

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: erreur de conversion des caractères en chiffres

avec la fonction .extract('/').getstringval()
et j'ai l'erreur

Code :

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

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit

avec la fonction .getstringval() seulement.....
Help.....
Merci, bonne fin de soirée
BR

**Waldar** · 17/10/2009, 17h43

Essayez de le récupérer en CLOB avec getclobval().

**r83** · 18/10/2009, 20h42

Bonsoir,

J'avais essayé, là, sous sqldevelopper il me dit :

Code :

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

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur

je fais un select
'<?xml version="1.0" encoding="iso-8859-1"?>'||
xmlElement(name......).extract('/').getclobval() into monclob from....

Et la requête a l'air de marcher
Merci pour l'aide...
...à suivre
Bonne soirée

**Waldar** · 19/10/2009, 09h57

Je crois que la partie extract('/') sert à faire la mise en page si vous êtes en 9i.
A partir de 10g ce n'est plus nécessaire.
Celà dit vous n'avez pas indiqué votre version !

De même, pour votre entête vous pouvez utiliser XMLRoot.
Il y a un manquement au niveau de l'encoding mais il reste contournable :

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT
    XMLRoot(
        XMLElement(name "commande",
               XMLAttributes(cm.idcde AS id),
               XMLElement(name "datecommande", cm.datecde), 
               XMLAgg(XMLElement(name "LigneCde",
                                 XMLForest(pr.idprod AS "id",
                                           pr.libprod AS "libelle",
                                           pc.qte AS "qteCommande"))
                   )),
            version '1.0" encoding="iso-8859-1') AS RESULTAT
FROM
    commande cm
    INNER JOIN prodcde pc
      ON pc.idcde = cm.idcde
    INNER JOIN produit pr
      ON pr.idprod = pc.idprod
GROUP BY
    cm.idcde,
    cm.datecde
ORDER BY
    cm.idcde ASC;
 
<?xml version="1.0" encoding="iso-8859-1"?>
<commande ID="1234">
  <datecommande>2009-10-19</datecommande>
  <LigneCde>
    <id>100</id>
    <libelle>stylo</libelle>
    <qteCommande>12</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>101</id>
    <libelle>gomme</libelle>
    <qteCommande>8</qteCommande>
  </LigneCde>
</commande>
 
 
<?xml version="1.0" encoding="iso-8859-1"?>
<commande ID="1235">
  <datecommande>2009-10-19</datecommande>
  <LigneCde>
    <id>100</id>
    <libelle>stylo</libelle>
    <qteCommande>6</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>102</id>
    <libelle>règle</libelle>
    <qteCommande>9</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>101</id>
    <libelle>gomme</libelle>
    <qteCommande>14</qteCommande>
  </LigneCde>
</commande>

**r83** · 19/10/2009, 11h03

Bonjour,

Merci pour votre réponse, je travaille sur une 10GXE en test, puis je testerai sur une 11G. J'ai donc enlevé le extract.
Je compile bien, l'exec sous sqldevelopper se passe bien, la requête en elle même renvoie un résultat correct.
Par contre je n'arrive plus à récupérer le résultat dans mon programme c# (erreur : Données non valides au niveau racine. Ligne 1, position 1.). Mais je vais essayer de chercher pourquoi.
Par contre mon souci principal concerne la quantité d'information retournée. En effet, si je mets une restriction suffisamment importante sur ma requête (where...), ça marche bien. Sinon, j'ai une erreur

Code :

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

ORA-19011: Tampon de chaînes de caractères trop petit

y-a-t-il un paramètre Oracle à modifier?
Dans mon cas, je renvoie une re cursor, mais j'ai aussi essayé de renvoyer un clob à l'appli, et j'ai le même problème.
Help!!! Je ne vois plus quoi faire, à moins de renvoyer directement un fichier XML à l'appli... mais comment?
Merci pour vos réponses, bonne journée

**Waldar** · 19/10/2009, 11h12

J'avais eu le même soucis avec un XML relativement long, pour contourner ce soucis j'ai d'abord effectué une requête dans un curseur qui me récupère mes id principaux, puis dans un second temps je récupèrai le XML en clob.

Par exemple ça donnerait quelque chose comme ça :

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
for c_commande in
(
SELECT DISTINCT
    cm.idcde
FROM
    commande cm
    INNER JOIN prodcde pc
      ON pc.idcde = cm.idcde
    INNER JOIN produit pr
      ON pr.idprod = pc.idprod
ORDER BY
    cm.idcde ASC;
)
 
loop
 
v_clob := null;
v_idcde := c_commande.idcde;
 
execute immediate 'SELECT
                        XMLRoot(
                            XMLElement(name "commande",
                                   XMLAttributes(cm.idcde AS id),
                                   XMLElement(name "datecommande", cm.datecde), 
                                   XMLAgg(XMLElement(name "LigneCde",
                                                     XMLForest(pr.idprod AS "id",
                                                               pr.libprod AS "libelle",
                                                               pc.qte AS "qteCommande"))
                                       )),
                                version ''1.0" encoding="iso-8859-1'').getClobVal() AS RESULTAT
                    FROM
                        commande cm
                        INNER JOIN prodcde pc
                          ON pc.idcde = cm.idcde
                        INNER JOIN produit pr
                          ON pr.idprod = pc.idprod
                    WHERE
                        cm.idcde = ' || v_idcde || '
                    GROUP BY
                        cm.idcde,
                        cm.datecde' into v_clob;

**mnitu** · 19/10/2009, 14h45

@Waldar
Pourquoi un Select dynamique et de plus sans variables de liaison ?

@R83
Pouvez-vous poster un exemple du code qui provoque l’anomalie ?

**Waldar** · 19/10/2009, 15h59

Vous avez bien raison mnitu !
J'ai encore beaucoup de progrès à faire en PL/SQL !

Le bout de code suivant doit être meilleur :

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
44
v_query := 'SELECT
                        XMLRoot(
                            XMLElement(name "commande",
                                   XMLAttributes(cm.idcde AS id),
                                   XMLElement(name "datecommande", cm.datecde), 
                                   XMLAgg(XMLElement(name "LigneCde",
                                                     XMLForest(pr.idprod AS "id",
                                                               pr.libprod AS "libelle",
                                                               pc.qte AS "qteCommande"))
                                       )),
                                version ''1.0" encoding="iso-8859-1'').getClobVal() AS RESULTAT
                    FROM
                        commande cm
                        INNER JOIN prodcde pc
                          ON pc.idcde = cm.idcde
                        INNER JOIN produit pr
                          ON pr.idprod = pc.idprod
                    WHERE
                        cm.idcde = :x
                    GROUP BY
                        cm.idcde,
                        cm.datecde';
 
 
FOR c_commande IN
(
SELECT DISTINCT
    cm.idcde
FROM
    commande cm
    INNER JOIN prodcde pc
      ON pc.idcde = cm.idcde
    INNER JOIN produit pr
      ON pr.idprod = pc.idprod
ORDER BY
    cm.idcde ASC;
)
 
loop
 
v_clob := NULL;
v_idcde := c_commande.idcde;
 
execute immediate v_query INTO v_clob USING v_idcde;

**mnitu** · 19/10/2009, 16h13

C'est vrai, c'est mieux mais, je vous propose plutôt cette version

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
44
45
 
declare
  v_clob  Clob;
Begin
  FOR c_commande IN
  (
  SELECT DISTINCT
      cm.idcde
  FROM
      commande cm
      INNER JOIN prodcde pc
        ON pc.idcde = cm.idcde
      INNER JOIN produit pr
        ON pr.idprod = pc.idprod
  ORDER BY
      cm.idcde ASC
  )
 
  loop   
    --
    SELECT
        XMLRoot(
            XMLElement(name "commande",
                   XMLAttributes(cm.idcde AS id),
                   XMLElement(name "datecommande", cm.datecde), 
                   XMLAgg(XMLElement(name "LigneCde",
                                     XMLForest(pr.idprod AS "id",
                                               pr.libprod AS "libelle",
                                               pc.qte AS "qteCommande"))
                       )),
                version '1.0 encoding="iso-8859-1"').getClobVal() AS RESULTAT
      INTO v_clob
      FROM
          commande cm
          INNER JOIN prodcde pc
            ON pc.idcde = cm.idcde
          INNER JOIN produit pr
            ON pr.idprod = pc.idprod
      WHERE
          cm.idcde = c_commande.idcde
      GROUP BY
          cm.idcde,
          cm.datecde;
  End Loop;
End;

Par contre pour l'instant je ne vois pas pourquoi il faut morceller le XML. Et je me demande aussi pourquoi on promene ce XML, à priori assez grand, par le reseau entre le serveur et le client. Pour quoi faire ?

**Waldar** · 19/10/2009, 16h35

Pour r83 je ne sais pas, dans mon cas effectivement le trimballer ne sert à rien, la procédure stockée l'écrit directement dans un fichier .xml sur un lecteur local avec utl_file.

Edit : et pour l'histoire du morcellement, faire directement le curseur avec les éléments XML au bout d'un moment on tombe sur l'erreur indiquée précédement : par exemple, ça fonctionne avec rownum <= 20 mais ça plante à 25.

J'ai trouvé cette solution pour contourner le problème, mais encore une fois si vous avez mieux

**mnitu** · 19/10/2009, 17h16

Nous sommes d’accord, je pense, qu’employer XmlElement implique des limites ; voilà un extrait du tutoriel évoqué

Size Limitations (due to XMLType)
XMLType nodes limited to 64K
If using getStringVal()
– XML Size limited to 4K from SQL
– XML Size limited to 32K from PL/SQL
If using getClobVal(), XML Node Size limited to 64K

Donc si ces limites posent des problèmes il faut employer une autre méthode. Récemment j’ai utilisé le découpage d’une table via les plages des rowids en adaptant une méthode proposé Tom Kyte pour le DYP (Do-It-Yourself Parallelism) mais ça revient à peu près à la même chose.
Mais, ma question c’est pour avertir que si c’est pour écrire le contenu du XML dans un fichier il faut s’y prendre très probablement autrement.

**Waldar** · 19/10/2009, 17h35

Je pense que vous avez bien pointé le problème.
Par contre en utilisant cette méthode j'arrive à générer des .xml de 1 ou 2Mo, qui font plus que les 64k annoncé.

Peut-être est-ce du au fait que je n'utilise pas XMLType (juste XMLRoot) ?
Je google sur le sujet mais sans succès pour le moment.

Comment feriez-vous pour écrire directement dans un fichier (en l'occurence n fichiers pour mon cas, désolé r83 je vampirise un peu votre sujet mais je pense que ça pourrait vous servir donc je ne recrée pas un post).

oracle et xml

Oracle

Discussions similaires

Partager

Partager