CSV to JSON Array

Oracle은 아쉽게도 CSV 형식의 문자 값을 JSON Array로 변환하는 기능을 제공하지 않습니다.

 

아래와 같이 CSV 형식의 문자 값을 JSON Array 형식으로 변환하는 사용자 정의 함수를 생성하겠습니다.

-- 1
CREATE OR REPLACE FUNCTION fnc_csv_to_jarray (
    i_val IN CLOB
  , i_del IN VARCHAR2 DEFAULT ','
  , i_typ IN VARCHAR2 DEFAULT 'C' -- C, N
)
    RETURN CLOB
 -- RETURN JSON
IS
    PRAGMA UDF;
    TYPE taa_varchar2 IS TABLE OF VARCHAR2(32747) INDEX BY PLS_INTEGER;

    v_buf VARCHAR2(32767);
    v_tmp taa_varchar2;
    v_idx PLS_INTEGER := 1;
    v_pos PLS_INTEGER;
    v_rst JSON_ARRAY_T := JSON_ARRAY_T ();
BEGIN
    LOOP
        v_buf := SUBSTR (i_val, (10000 * (v_idx - 1)) + 1, 10000);
        EXIT WHEN v_buf IS NULL;
        v_tmp (v_idx) := v_buf;
        v_idx := v_idx + 1;
    END LOOP;

    v_tmp (v_tmp.FIRST) := LTRIM (v_tmp (v_tmp.FIRST), '{');
    v_tmp (v_tmp.LAST)  := RTRIM (v_tmp (v_tmp.LAST) , '}') || i_del;

    FOR i IN v_tmp.FIRST .. v_tmp.LAST LOOP
        v_buf := v_buf || v_tmp (i);

        LOOP
            v_pos := INSTR (v_buf, i_del);
            EXIT WHEN NVL (v_pos, 0) = 0;

            IF i_typ = 'C' THEN
                v_rst.APPEND (REPLACE (SUBSTR (v_buf, 1, v_pos - 1), 'NULL'));
            ELSE
                v_rst.APPEND (TO_NUMBER (REPLACE (SUBSTR (v_buf, 1, v_pos - 1), 'NULL')));
            END IF;

            v_buf := SUBSTR (v_buf, v_pos + 1);
        END LOOP;
    END LOOP;

    RETURN v_rst.TO_CLOB;
 -- RETURN v_rst.TO_JSON;
END;
/

 

아래는 함수를 사용한 결과입니다.

-- 2
WITH w1 AS (
SELECT '{A,B,C,D,E}'             AS c1 -- String Array
     , '{NULL,2,NULL,NULL,NULL}' AS c2 -- Number Array
     , 'A,B,C,D,E'               AS c3 -- String CSV
     , ',2,,,'                   AS c4 -- Number CSV
     , 'A|B|C|D|E'               AS c5 -- String DSV
     , '|2|||'                   AS c6 -- Number DSV
  FROM DUAL)
SELECT fnc_csv_to_jarray (c1)           AS c1
     , fnc_csv_to_jarray (c2, ',', 'N') AS c2
     , fnc_csv_to_jarray (c3)           AS c3
     , fnc_csv_to_jarray (c4, ',', 'N') AS c4
     , fnc_csv_to_jarray (c5, '|')      AS c5
     , fnc_csv_to_jarray (c6, '|', 'N') AS c6
  FROM w1;

C1                    C2                      C3                    C4                      C5                    C6
--------------------- ----------------------- --------------------- ----------------------- --------------------- -----------------------
["A","B","C","D","E"] [null,2,null,null,null] ["A","B","C","D","E"] [null,2,null,null,null] ["A","B","C","D","E"] [null,2,null,null,null]

1개의 행이 선택되었습니다.