REMOTE DBA

In der täglichen Arbeit kommt es immer wieder vor, dass das Ergebnis eines Select-Abfrage in eine “comma separated list” umgewandelt werden soll. Während viele Programmiersprachen wie php hierzu Funktionen wie explode/ implode bereitstellen, fehlt eine solche Funktion in Oracle.

Im folgenden zeigen wir, wie mittels zweier simpler PL/SQL Funktionen ein “Concatinate” und “Split” realisierbar ist.

f_concat

Der Funktion f_concat wird das Select-Statement als REF CURSOR übergeben, ein Trennzeichen (Delimiter) ist optional. Der Rückgabewert ist ein String mit der maximale Länge von 32767 Zeichen.

CREATE OR REPLACE FUNCTION
       f_concat (p_cur sys_refcursor, p_del varchar2:= ',')
   RETURN VARCHAR2 IS
   l_value    VARCHAR2 (32767);
   l_result   VARCHAR2 (32767);
BEGIN
   LOOP
      FETCH p_cur
      INTO l_value;
      EXIT WHEN p_cur%NOTFOUND;
      IF l_result IS NOT NULL THEN
         l_result   := l_result || p_del;
      END IF;
      l_result   := l_result || l_value;
   END LOOP;
   RETURN l_result;
END f_concat;

Somit lassen sich zum Beispiel die Attribute einer Tabelle als CSV-Liste ausgeben:

SELECT f_concat (cursor (SELECT column_name
                           FROM dba_tab_columns
                          WHERE table_name = 'CCOL$'),
                 ', ') cols
  FROM DUAL;

Liefert folgende Ausgabe:

CON#, OBJ#, COL#, POS#, INTCOL#, SPARE1, SPARE2, ...

f_split

Das Gegenstück zur oben gezeigen Funktion ist f_split. Die Funktion extrahiert die einzelnen Tokens in einem String und gibt sie jeweils in einer Zeile zurück. Die Funktion benötigt den Typ “table of varchar”. Die Argumente werden extrahiert und in einer Pipeline zurückgegeben.

create or replace type t_split as table of varchar2(32767);
/
create or replace function f_split(p_list varchar2,
p_del varchar2 := ';')
  return t_split pipelined is
    l_idx     pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value   varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx + length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end f_split;

Durch den type cast “TABLE” kann die Funktion wie eine Tabelle verwendet werden:

select * from table(f_split('eis;zwoi;drü;vier;foif'));

eis
zwoi
drü
vier
foif