Convertir une chaîne séparée par des virgules en tableau en PL/SQL

Question

Comment convertir une chaîne séparée par des virgules en un tableau ?

J'ai l'entrée ' 1,2,3' et je dois le convertir en tableau.

Answer 1

Voici une autre option plus facile

select to_number(column_value) as IDs from xmltable('1,2,3,4,5');

Answer 2

Oracle fournit la fonction intégrée DBMS_UTILITY.COMMA_TO_TABLE .

Malheureusement, celle-ci ne fonctionne pas avec les chiffres :

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => l_input
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        l_array(i)
 18      );
 19    end loop;
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 132
ORA-06512: at "SYS.DBMS_UTILITY", line 164
ORA-06512: at "SYS.DBMS_UTILITY", line 218
ORA-06512: at line 6

Mais avec une petite astuce consistant à préfixer les éléments par un "x", cela fonctionne :

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => regexp_replace(l_input,'(^|,)','\1x')
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        substr(l_array(i),2)
 18      );
 19    end loop;
 20  end;
 21  /
3
Element 1 of array contains: 1
Element 2 of array contains: 2
Element 3 of array contains: 3

PL/SQL procedure successfully completed.

Regards, Rob.

Answer 3

On ne peut jamais être à court d'alternatives pour faire la même chose différemment, n'est-ce pas ? J'ai récemment découvert que c'était très pratique :

DECLARE
   BAR   VARCHAR2 (200) := '1,2,3';
BEGIN
   FOR FOO IN (    SELECT REGEXP_SUBSTR (BAR,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             TXT
                     FROM DUAL
               CONNECT BY REGEXP_SUBSTR (BAR,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             IS NOT NULL)
   LOOP
      DBMS_OUTPUT.PUT_LINE (FOO.TXT);
   END LOOP;
END;

Sorties :

1
2
3

Answer 4

Je sais que Stack Overflow désapprouve le collage d'URL sans explications, mais cette page particulière propose quelques très bonnes options :

http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

J'aime particulièrement celle-ci, qui convertit la liste délimitée en un tableau temporaire sur lequel vous pouvez exécuter des requêtes :

/* Create the output TYPE, here using a VARCHAR2(100) nested table type */

SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);
  2  /

Type created.

/* Now, create the function.*/

SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)
  2    RETURN test_type
  3  AS
  4    l_string       VARCHAR2(32767) := p_list || ',';
  5    l_comma_index  PLS_INTEGER;
  6    l_index        PLS_INTEGER := 1;
  7    l_tab          test_type := test_type();
  8  BEGIN
  9    LOOP
 10      l_comma_index := INSTR(l_string, ',', l_index);
 11      EXIT WHEN l_comma_index = 0;
 12      l_tab.EXTEND;
 13      l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
 14      l_index := l_comma_index + 1;
 15    END LOOP;
 16    RETURN l_tab;
 17  END f_convert;
 18  /

Function created.

---

/* Prove it works */

SQL> SELECT * FROM TABLE(f_convert('AAA,BBB,CCC,D'));

COLUMN_VALUE
--------------------------------------------------------------------------------
AAA
BBB
CCC
D

4 rows selected.

Answer 5

Code simple

    create or replace function get\_token(text\_is varchar2, token\_in number, delim\_is varchar2 := ';') return varchar2 is
       text\_ls varchar2(2000);
       spos\_ln number;
       epos    \_ln number;
    begin
       text\_ls := delim\_is || text\_is || rpad(delim\_is, token\_in, delim\_is);
       spos\_ln := instr(text\_ls, delim\_is, 1, token\_in);
       epos\_ln := instr(text\_ls, delim\_is, 1, token\_in+1);
       return substr(text\_ls, spos\_ln+1, epos\_ln-spos\_ln-1);
    end get\_token;