Discussion :

[Moriceot]

Bonjour,

Je fais de la migration de données et je fais face à un cas particulier. Un champs d'une table a été utilisé pour stocker les informations de plusieurs enregistrements. A moi de récupérer la valeur de ce champs et de créer un nouvel enregistrement pour chaque lignes dans ma table cible. J'ai réalisé une fonction qui réalise bien cette action.

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
/*------------------------------------------------------------------------------
  FONCTION : Découpage d'une chaîne de caractère, via un caractère de séparation, dans une liste
-------------------------------------------------------------------------------*/
--
-- Version 001.001
--
CREATE OR REPLACE
FUNCTION SPLIT_STRING_INTO_LIST(separator IN VARCHAR2, string_param IN VARCHAR2) RETURN TABLE_OF_VARCHAR2 AS
 
string_to_split VARCHAR2(32767) := string_param || separator;
separator_index NUMBER;
l_index NUMBER := 1;
list_splitted TABLE_OF_VARCHAR2 := TABLE_OF_VARCHAR2();
 
BEGIN
 
	LOOP
		separator_index := INSTR(string_to_split, separator, l_index);
		EXIT
			WHEN separator_index = 0;
		list_splitted.EXTEND;
		list_splitted(list_splitted.COUNT) := TRIM(SUBSTR(string_to_split,l_index,separator_index - l_index));
		l_index := separator_index + 1;
	END LOOP;
 
  RETURN list_splitted;
 
END SPLIT_STRING_INTO_LIST;

Mais je suis incapable d'utiliser ma fonction dans un bloc PL-SQL si mon champs String est une variable.
Le fonctionnement est le suivant:
1- Je boucle sur mon champs source à l'aide d'un cursor pour récupérer le champs à découper
2- Ma fonction renvoyant potentiellement plusieurs ligne, je parcours le résultat de ma fonction split dans un second cursor paramétré pour insérer une a une les ligne retournées dans ma table cible.


Voici mon code:

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
DECLARE
 
 
	descr              VARCHAR2(4000);
        res                 VARCHAR2(4000);
        desc_string     VARCHAR2(4000);
 
CURSOR a1 IS     
        SELECT description des
        FROM tableSource;
 
CURSOR b1(desc_string VARCHAR2) IS
        SELECT *  INTO res
        FROM TABLE(split_string_into_list(chr(10),desc_string));
BEGIN
 
   FOR a IN a1 LOOP
    descr:=c.des;
    FOR b IN b1(descr) LOOP
      INSERT INTO Z_NAS_INPUT_SOURCE_STRING VALUES (res);
    END LOOP;
  END LOOP; 
 
END;
/

Si j'enlève le paramétrage de mon cursor C2 pour insérer en dur un string dans le code (type 'aaa,bbb,ccc') cela fonctionne. Mais avec le paramètre, je récupère une erreur ORA-22905: impossible d'accéder aux lignes d'un élément qui n'appartient pas à une table imbriquée.

J'ai l'impression que cela vient de l'utilisation de "Select * FROM TABLE(); ". Malheureusement, toutes les solutions que je trouve sont faites avec des versions d'oracles supérieures à la 9i.

Pouvez vous m'aider?
Cordialement,
Moriceot

[McM]

Fait la découpe directement en PLSQL

Pour mon test, j'ai fait un curseur avec un listagg(fonctionnalité 11g) avec un séparateur ',' mais il suffit que tu remplace le curseur r1 par le tien, et le

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
DECLARE		
	v_chaine varchar2(32000);	
	v_champ varchar2(32000);	
	v_sep VARCHAR2(1) := ','; -- séparateur
BEGIN
	FOR r1 IN (SELECT cluster_name, LISTAGG(table_name, v_sep) WITHIN GROUP (ORDER BY table_name) lst
							FROM dba_tables 
							WHERE OWNER ='SYS' AND cluster_name IS NOT NULL
							GROUP BY cluster_name ORDER BY 1)
	LOOP
		dbms_output.put_line(r1.cluster_name ||':'|| r1.lst);
		v_chaine := RTRIM(r1.lst, v_sep) ||v_sep; -- Il faut toujours 1 séparateur si un seul champ dans lst
		WHILE v_chaine IS NOT NULL
		LOOP
			v_champ := SUBSTR(v_chaine, 1, INSTR(v_chaine, v_sep) -1);
			dbms_output.put_line('-   Insert de '|| v_champ);
			v_chaine := SUBSTR(v_chaine, INSTR(v_chaine,v_sep) +1);
		END LOOP;
	END LOOP;
end;

Résultat

C_COBJ#:CCOL$,CDEF$
- Insert de CCOL$
- Insert de CDEF$
C_FILE#_BLOCK#:SEG$,UET$
- Insert de SEG$
- Insert de UET$
C_MLOG#:MLOG$,SLOG$
- Insert de MLOG$
- Insert de SLOG$
C_OBJ#:ASSEMBLY$,ATTRCOL$,CLU$,COL$,COLTYPE$,ICOL$,ICOLDEP$,IND$,LIBRARY$,LOB$,NTAB$,OPQTYPE$,REFCON$,SUBCOLTYPE$,TAB$,TYPE_MISC$,VIEWTRCOL$
- Insert de ASSEMBLY$
- Insert de ATTRCOL$
- Insert de CLU$
- Insert de COL$
- Insert de COLTYPE$
- Insert de ICOL$
- Insert de ICOLDEP$
- Insert de IND$
- Insert de LIBRARY$
- Insert de LOB$
- Insert de NTAB$
- Insert de OPQTYPE$
- Insert de REFCON$
- Insert de SUBCOLTYPE$
- Insert de TAB$
- Insert de TYPE_MISC$
- Insert de VIEWTRCOL$
C_OBJ#_INTCOL#:HISTGRM$
- Insert de HISTGRM$
C_RG#:RGCHILD$,RGROUP$
- Insert de RGCHILD$
- Insert de RGROUP$
C_TOID_VERSION#:ATTRIBUTE$,COLLECTION$,METHOD$,PARAMETER$,RESULT$,TYPE$
- Insert de ATTRIBUTE$
- Insert de COLLECTION$
- Insert de METHOD$
- Insert de PARAMETER$
- Insert de RESULT$
- Insert de TYPE$
C_TS#:FET$,TS$
- Insert de FET$
- Insert de TS$
C_USER#:TSQ$,USER$
- Insert de TSQ$
- Insert de USER$
SMON_SCN_TO_TIME_AUX:SMON_SCN_TIME
- Insert de SMON_SCN_TIME

[Moriceot]

Merci je vais regarder ça!

[vanagreg]

Bonjour,

Si j'ai bien compris tu es en 9i, pas facile d'utiliser des techniques modernes alors. Je n'ai pas d'accès à une base aussi vieille, mais tu peux essayer cette requête SQL:

Code :

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

1
2
3
4
5
6
7
8
9
select description,
         case when level = 1 then substr(str, 1, instr(description, ',', 1, level)-1) 
                else substr(description, instr(description, ',', 1, level-1)+1, decode(instr(description, ',', 1, level), 0, length(description)+1, instr(description, ',', 1, level))-instr(description, ',', 1, level-1)-1)
         end valeur
from (select rownum id, description from tablesource)
)
connect by prior id = id 
                and level <= length(description) - length(replace(description , ','))+1 
               and prior sys_guid() is not null;

Plein de fonctions imbriquées car il n'y a pas les fonctions REGEXP en 9i.
J'ai considéré que le séparateur était la virgule (il faudra remplacer ',' sinon).