Aufgabe: Ein Liste soll wieder in einzelne Werte zerlegt werden

Um solch einen String zu erzeugen ⇒ siehe auch Ein Listen von Werten in SQL erstellen

Alternativ in Apex die Methode:

DECLARE
   v_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  --
  -- Convert the colon separated string of values into
  -- a PL/SQL array
  v_selected := APEX_UTIL.STRING_TO_TABLE(:P100_PRODUCTLIST);
  --
  -- Loop over array to insert department numbers and sysdate
  --
  FOR i IN 1..v_selected.COUNT
   LOOP
    DBMS_OUTPUT.put_line(v_selected(i));
  END LOOP;
END;

Mit Hilfe von dbms_utility.comma_to_table lässt sich eine Liste in ein Array transformieren.

Beispiel:

DECLARE
 v_values          DBMS_UTILITY.lname_array;
 v_tabLength       BINARY_INTEGER;
 -- not working
 v_list             varchar2(255):=REPLACE('0100:26941:0:0',':',',');
 -- working
 -- v_list          varchar2(255):=replace('a:b:c:d',':',',');
BEGIN
 
   dbms_output.put_line(v_list);
 
   DBMS_UTILITY.COMMA_TO_TABLE( list    => v_list
                              ,  tablen  => v_tabLength
                              ,  tab     => v_values
                              );
 
    IF v_tabLength=4 THEN
        dbms_output.put_line('INST_KEY        :='|| v_values(1));
        dbms_output.put_line('KONTONUMMER     :='|| v_values(2));
        dbms_output.put_line('KONTO_SUBNUMMER :='|| v_values(3));
        dbms_output.put_line('LIMITSEQUENZ    :='|| v_values(4));
    ELSE
      raise_application_error( -20020, 'Konto Key has not enough values ::' || v_list);
    END IF;
 
END;

siehe auch: http://docs.oracle.com/cd/E11882_01/timesten.112/e21645/d_util.htm#i1002468

In meiner 12c Umgebung erhalten ich allerdings den folgenden Fehler wenn der String aus Zahlen besteht:

ERROR at line 1:
ORA-20001: comma-separated list invalid near #
ORA-06512: at "SYS.DBMS_UTILITY", line 236
ORA-06512: at "SYS.DBMS_UTILITY", line 272
ORA-06512: at line 16

Daher meine eigene Routine erstellt, da mir gerade die Apex Methode nicht mehr einfällt …

Seperator kann eine beliebige Zeichenkette sein.

CREATE OR REPLACE PACKAGE GPI_APEX_UTILS AS 
 
  -- =====================
  TYPE splitResultTab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
 
  -- =====================
  -- split a string 
  -- =====================
 
  FUNCTION splitString( p_string    VARCHAR2 
                     ,  p_seperator VARCHAR2)
  RETURN splitResultTab;
 
  -- =====================
  -- test the function 
  --- =====================
  PROCEDURE testsplitString( p_string    VARCHAR2 
                     ,  p_seperator VARCHAR2);
 
 
END GPI_APEX_UTILS;
/
 
CREATE OR REPLACE PACKAGE BODY  GPI_APEX_UTILS
 -- =====================
  -- split a string 
  -- =====================
  FUNCTION splitString ( p_string    VARCHAR2 
                     ,  p_seperator VARCHAR2)
  RETURN splitResultTab
  IS
    v_returnTab splitResultTab;
    v_token_count PLS_INTEGER;
    v_tocken      VARCHAR2(2000);
    v_string      VARCHAR2(32000);
    v_pos1        PLS_INTEGER;
    v_sep_length PLS_INTEGER;
  BEGIN
 
    -- get the count of the tocken (count sperator + one )
    v_token_count:= REGEXP_COUNT(p_string,p_seperator)+1;
 
    v_sep_length:=LENGTH(p_seperator);
 
    IF v_token_count=1 THEN
        raise_application_error( -20020, ' seperator Char not exist in string to split - p_seperator:=' || p_seperator );
    END IF;
 
    -- add at the end on seperator sign as end signal for the loop!
    v_string:=p_string||p_seperator;
 
    -- loop of the result string
    -- in each round cut off the tocken at the begin of the string 
    FOR i IN 1 .. v_token_count
    LOOP
 
       -- find the first delimiter in the string
       v_pos1 :=INSTR(v_string,p_seperator);
 
       -- get the tocken
       v_tocken:=SUBSTR(v_string,1,v_pos1-1);
       v_returnTab(i):=v_tocken;
 
       -- cut the read tocken from the string   
       v_string:=SUBSTR(v_string,v_pos1+v_sep_length,LENGTH(v_string));
    END LOOP;
 
    RETURN v_returnTab;
 
  END splitString;
 
  -- =====================
  -- test the function 
  --- =====================
 
  PROCEDURE testsplitString( p_string    VARCHAR2 
                     ,  p_seperator VARCHAR2)
  IS
    v_resTab splitResultTab;
  BEGIN
    v_resTab:=splitString(p_string=> p_string, p_seperator => p_seperator);
 
    FOR i IN v_resTab.FIRST .. v_resTab.LAST
    LOOP
      -- ausgeben
      IF v_resTab.EXISTS(i) 
       THEN
      DBMS_OUTPUT.put_line('element #' || i || ' = ' || v_resTab(i));
      END IF; 
    END LOOP;    
  END;
 
END GPI_APEX_UTILS;

Testen:

sqlplus gpi/gpi
 
EXEC GPI_APEX_UTILS.testsplitString('0100::26941::0::0','::')
 
element #1 = 0100
element #2 = 26941
element #3 = 0
element #4 = 0

• http://stackoverflow.com/questions/31654411/split-string-by-space-and-character-as-delimiter-in-oracle-with-regexp-substr
• https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Mehr ⇒ PL/SQL - Code Beispiele