Oracle에서 구분 된 문자열을 행으로 분할

dbforums.com의 @LKBrwn_DBA가 이에 대한 해결책을 마련했습니다.

CREATE OR REPLACE TYPE Csvparserreturn AS TABLE OF VARCHAR2 ( 4000 );
/

CREATE OR REPLACE FUNCTION Csvparser ( P_Dat IN CLOB, P_Hdr IN VARCHAR2 )
   RETURN Csvparserreturn
AS
   L_Debug        CHAR ( 1 ) := 'F';
   L_Hdr          VARCHAR2 ( 4000 );
   L_Dat          CLOB;
   L_Wrk          CLOB;
   L_Recsep       CHAR ( 1 ) := CHR ( 9 );

   O_Hdr          VARCHAR2 ( 4000 );
   O_Dat          CLOB;
   O_Data_Tab     Csvparserreturn := Csvparserreturn ( );

   TYPE Text_Array IS TABLE OF VARCHAR2 ( 4000 )
      INDEX BY PLS_INTEGER;

   L_Hdr_Array    Text_Array;
   L_Dat_Array    Text_Array;
   L_Fld_Array    Text_Array;
   L_Elm_Array    Text_Array;
   L_Val_Array    Text_Array;
   L_Hdr_Count    PLS_INTEGER;
   L_Dat_Count    PLS_INTEGER;
   L_Fld_Count    PLS_INTEGER;
   I              PLS_INTEGER;
   J              PLS_INTEGER;
   K              PLS_INTEGER;
   L              PLS_INTEGER;
   N              PLS_INTEGER;

   PROCEDURE Print_Line ( P_Text VARCHAR2 )
   IS
   BEGIN
      IF L_Debug = 'T'
      THEN
         DBMS_OUTPUT.Put_Line ( P_Text );
      END IF;
   END;

   FUNCTION Parse_Csv ( P_Text CLOB, P_Delim VARCHAR2 DEFAULT ',' )
      RETURN Text_Array
   IS
      Wk_Array       Text_Array;
   BEGIN
      N           := 1;
      L           := 1;
      K           := 1;

      WHILE 1 = 1
      LOOP
         L           := INSTR ( SUBSTR ( P_Text || P_Delim, K + 1 )
                              , P_Delim );
         EXIT WHEN K >= LENGTH ( P_Text );
         Wk_Array ( N ) := SUBSTR ( P_Text, K, L );
         N           := N + 1;
         K           := K + L + 1;
      END LOOP;

      RETURN Wk_Array;
   END;
BEGIN
   L_Hdr       := P_Hdr;
   L_Dat       := P_Dat;
   L_Hdr_Array := Parse_Csv ( L_Hdr );
   L_Hdr_Count := L_Hdr_Array.COUNT;
   Print_Line ( 'Hdr#' || L_Hdr_Count );
   O_Hdr       := '';

   FOR I IN 1 .. L_Hdr_Count
   LOOP
      O_Hdr       := O_Hdr || L_Hdr_Array ( I ) || L_Recsep;
      Print_Line ( 'Elm#' || I || ': ' || L_Hdr_Array ( I ) );
   END LOOP;

   L_Wrk       := SUBSTR ( L_Dat, 2, LENGTH ( L_Dat ) - 2 );

   SELECT REPLACE ( L_Wrk, '),(', L_Recsep ) || L_Recsep INTO L_Wrk FROM DUAL;

   Print_Line ( 'Dat: ' || L_Wrk );

   L_Dat_Array := Parse_Csv ( L_Wrk, L_Recsep );
   L_Dat_Count := L_Dat_Array.COUNT;


   O_Data_Tab.EXTEND;
   O_Data_Tab ( 1 ) := O_Hdr;

   FOR I IN 1 .. L_Dat_Count
   LOOP
      L_Fld_Array := Parse_Csv ( L_Dat_Array ( I ) );
      L_Fld_Count := L_Fld_Array.COUNT;
      Print_Line ( 'Rec#' || I || ': ' || L_Dat_Array ( I ) || ' Flds:' || L_Fld_Count );

      O_Dat       := '';

      FOR J IN 1 .. L_Hdr_Count
      LOOP
         K           := 0;

         FOR N IN 1 .. L_Fld_Count
         LOOP
            IF L_Hdr_Array ( J ) =
                  SUBSTR ( L_Fld_Array ( N )
                         , 1, LENGTH ( L_Hdr_Array ( J ) ) )
            THEN
               K           := N;
               CONTINUE;
            END IF;
         END LOOP;

         IF K > 0
         THEN
            L           := INSTR ( L_Fld_Array ( K ), '=' );
            L_Elm_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), 1, L - 1 );
            L_Val_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), L + 1 );
         ELSE
            L_Elm_Array ( J ) := '#N/A';
            L_Val_Array ( J ) := '#N/A';
         END IF;

         Print_Line ( 'Element ' || TO_CHAR ( J ) || '.' || L_Hdr_Array ( J ) || '=' || L_Val_Array ( J ) );

         O_Dat       := O_Dat || L_Val_Array ( J ) || L_Recsep;
      END LOOP;

      Print_Line ( 'Out#' || TO_CHAR ( I ) || '=' || O_Dat );

      O_Data_Tab.EXTEND;
      O_Data_Tab ( I + 1 ) := O_Dat;
   END LOOP;

   RETURN O_Data_Tab;
END;
/

사용법은 다음과 같습니다.

DECLARE
   Out_Data  Csvparserreturn := Csvparserreturn ( );
   X_Hdr     VARCHAR2 ( 4000 ) := 'Directory,ID,Location,UserName,Password,Selector';
   X_Dat     CLOB := '(Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1),(Directory=Voice Active Directory B,ID=VT-AD2,Location=https://beta-proxy.voice.com/VTadp/Proxy/[/url],UserName=admin,Password=passw0rd,Selector=AD2),(Directory=Voice Active Directory C,ID=VT-AD3,Location=https://final-proxy.voice.com/VTadp/Proxy/,UserName=admin,Password=passw0rd)';
BEGIN
   Out_Data    := Csvparser ( X_Dat, X_Hdr );

   FOR I IN Out_Data.FIRST .. Out_Data.LAST
   LOOP
      DBMS_OUTPUT.Put_Line ( 'Rec# ' || TO_CHAR ( I, 'FM000.' ) || Out_Data ( I ) );
   END LOOP;
END;
/