Parser un champ avec retour chariot en plusieurs lignes [Résolu]

[z980x]

Bonjour à tous,

Voici ma requête et ce que j'obtiens avec:

Code :

1
2
3
4
5
6
7
8
9
SELECT NOM, LISTE_REL FROM PERSONNES;
 
NOM   |  LISTE_REL
NOM1 | REL11
           REL12
           REL13
NOM2 | REL21
           REL22
           REL23

Pour chaque ligne, j'ai un CLOB qui contient plusieurs informations, séparées par un retour chariot.

J'aimerais obtenir ce résultat:

NOM   |  LISTE_REL
NOM1 | REL11
NOM1 | REL12
NOM1 | REL13
NOM2 | REL21
NOM2 | REL22
NOM2 | REL23

J'ai essayé pas mal de trucs, mais rien ne marche comme il faut... A l'aide !

Merci d'avance !

Philippe

[skuatamad]

Regarde Transformer les éléments d'une liste en enregistrement
Avec des retours chariots (correspondant à chr(10)) ça peut donner :

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SQL> COLUMN liste_rel format a30
SQL> DROP TABLE personnes
  2  /
 
TABLE dropped.
 
SQL> CREATE TABLE PERSONNES (nom varchar2(20), liste_rel clob)
  2  /
 
TABLE created.
 
SQL> INSERT INTO PERSONNES VALUES ('NOM1', 'REL11
  2  REL12
  3  REL13')
  4  /
 
1 row created.
 
SQL> INSERT INTO PERSONNES VALUES ('NOM2', 'REL21
  2  REL22
  3  REL23')
  4  /
 
1 row created.
 
SQL> SELECT * FROM personnes
  2  /
 
NOM                  LISTE_REL
-------------------- ------------------------------
NOM1                 REL11
                     REL12
                     REL13
 
NOM2                 REL21
                     REL22
                     REL23
 
 
SQL> SELECT t.nom, x.column_value AS liste_rel
  2    FROM personnes t
  3   CROSS JOIN TABLE(
  4               cast(multiset(
  5                       SELECT substr( chr(10)||liste_rel||chr(10),
  6                                     instr( chr(10)||liste_rel||chr(10), chr(10), 1, rownum )+1,
  7                                     instr( chr(10)||liste_rel||chr(10), chr(10), 1, rownum+1 )
  8                                     -instr( chr(10)||liste_rel||chr(10), chr(10), 1, rownum )-1
  9                                    )
 10                         FROM dual
 11                      connect BY level <= length(liste_rel)-length(REPLACE(liste_rel,chr(10),''))+1
 12                            ) AS sys.odcivarchar2list )
 13                         ) x
 14  /
 
NOM                  LISTE_REL
-------------------- ------------------------------
NOM1                 REL11
NOM1                 REL12
NOM1                 REL13
NOM2                 REL21
NOM2                 REL22
NOM2                 REL23
 
6 rows selected.
 
SQL>

[z980x]

Tout d'abord, merci beaucoup pour ta réponse.

Quand j'exécute simplement ta requête, voici l'erreur que j'ai:

ORA-00904: "LISTE_REL" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Erreur à la ligne 11, colonne 86

(C'est le LISTE_REL du REPLACE)

Je ne comprends pas pourquoi j'ai ça, j'ai essayé en préfixant avec "t.", mais rien ne change. :-(

J'ai testé le même code que celui présent dans la page du lien que tu as envoyé, en faisant exactement les mêmes instructions, et j'obtiens la même erreur, au même endroit. :-(

Mes compétences en SQL sont assez limitées, alors c'est peut être une erreur très simple, mais...

Merci d'avance !

Philippe

[skuatamad]

Quelle est la version d'oracle ? pour le savoir :

Code :

SELECT * FROM v$version

[z980x]

Sur mon PC perso:

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


Tu crois que c'est parce que c'est une XE?

[skuatamad]

Que donne :

Code :

DESC sys.odcivarchar2list

[z980x]

Voilà m'sieur:

Code :

1
2
3
4
5
DESC sys.odcivarchar2list
user type definition                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-----------------------------------------------------------
TYPE ODCIVarchar2List                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
 AS VARRAY(32767) OF VARCHAR2(4000);

[z980x]

Salut skuatamad,

je viens de tester en "conditions réelles", c'est à dire sur une vraie base, avec un vrai Oracle, et magie, ça marche du feu de dieu !

Merci infiniment !

Philippe

[skuatamad]

Sur une 11GXE ça marche (je ne sais pas pour la 10GXE):

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
SQL> CREATE TABLE PERSONNES (nom varchar2(20), liste_rel clob)
  2  /
 
TABLE created.
 
SQL> INSERT INTO PERSONNES VALUES ('NOM1', 'REL11
  2  REL12
  3  REL13')
  4  /
 
1 row created.
 
SQL> INSERT INTO PERSONNES VALUES ('NOM2', 'REL21
  2  REL22
  3  REL23')
  4  /
 
1 row created.
 
SQL> SELECT * FROM personnes
  2  /
 
NOM                  LISTE_REL
-------------------- ------------------------------
NOM1                 REL11
                     REL12
                     REL13
 
NOM2                 REL21
                     REL22
                     REL23
 
 
SQL> SELECT t.nom, x.column_value AS liste_rel
  2    FROM personnes t
  3   CROSS JOIN TABLE(
  4               cast(multiset(
  5                       SELECT substr( chr(10)||liste_rel||chr(10),
  6                                     instr( chr(10)||liste_rel||chr(10), chr(10), 1, rownum )+1,
  7                                     instr( chr(10)||liste_rel||chr(10), chr(10), 1, rownum+1 )
  8                                     -instr( chr(10)||liste_rel||chr(10), chr(10), 1, rownum )-1
  9                                    )
 10                         FROM dual
 11                      connect BY level <= length(liste_rel)-length(REPLACE(liste_rel,chr(10),''))+1
 12                            ) AS sys.odcivarchar2list )
 13                         ) x
 14  /
 
NOM                  LISTE_REL
-------------------- ------------------------------
NOM1                 REL11
NOM1                 REL12
NOM1                 REL13
NOM2                 REL21
NOM2                 REL22
NOM2                 REL23
 
6 rows selected.
 
SQL> SELECT * FROM v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS FOR 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
SQL>