[Oracle] BLOB type 데이터를 XMLTYPE 으로 변환하기

Oracle SOA Suite 의 Repository DB 의 테이블들을 보면 Log Trail 데이터들은 크기가 커서 BLOB type 으로 저장되어 있다. (SYNC_TRAIL, AUDIT_DETAILS, SUITCASE_BIN 등의 BIN 컬럼)

BPEL Console 로 확인이 힘든 (해당 instance를 찾기 힘든) 경우, 직접 Repository DB 에 접속하여 query 를 통해 확인하기도 하는데, 이때 BLOB Type 은 읽어낼 수가 없어 XMLTYPE 으로 변환하는 Functio 을 만들어보았다. (어차피 내용은 xml 이니, XMLTYPE 으로 변환하면 활용도가 크다.)

과정은 간단하지 않다. XMLTYPE 을 만들기 위해선 XMLTYPE type package 의 CREATEXML 을 이용해야 하는데, 지원하는 type 이 BLOB 는 없고, VARCHAR2 등 외에 CLOB 만 지원한다. 따라서 일단 BLOB 를 CLOB 로 변환후, CLOB 를 XMLTYPE 으로 변환하는 과정을 거친다.

1단계 : BLOB 를 CLOB 로 변환하기

DBMS_LOB package 의 CONVERTOCLOB 를 이용한다.

   PROCEDURE convert_to_clob (
      v_clob           IN OUT   CLOB,
      v_blob           IN OUT   BLOB,
      v_amount         IN OUT   NUMBER,
      v_clob_offset    IN OUT   NUMBER,
      v_blob_offset    IN OUT   NUMBER,
      v_blob_csid      IN       NUMBER,
      v_lang_context   IN OUT   NUMBER,
      v_warning        OUT      NUMBER
   )
   AS
   BEGIN
      DBMS_LOB.OPEN (v_blob, DBMS_LOB.lob_readonly);
      DBMS_LOB.OPEN (v_clob, DBMS_LOB.lob_readwrite);
      DBMS_LOB.converttoclob (v_clob,
                              v_blob,
                              v_amount,
                              v_clob_offset,
                              v_blob_offset,
                              v_blob_csid,
                              v_lang_context,
                              v_warning
                             );
      DBMS_LOB.CLOSE (v_blob);
      DBMS_LOB.CLOSE (v_clob);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM);
         DBMS_LOB.CLOSE (v_blob);
         DBMS_LOB.CLOSE (v_clob);
   END;

2단계 : BLOB 를 CLOB 변환후, XMLTYPE 으로 변환하기

알다시피 LOB 는 compress 된 형태이므로, BLOB data 를 uncompress 한 후, 1단계에서 만든 convert_to_clob 를 이용하여 CLOB 로 변환하고 이것을 XMLTYPE.CREATEXML 을 통해 XMLTYPE 으로 변환한다.

   FUNCTION blob_to_xmltype (
      p_blob          IN BLOB,
      p_root_tag_name IN VARCHAR2
   )
      RETURN XMLTYPE
   IS
      v_blob_locator         BLOB;
      v_clob_locator         CLOB        := EMPTY_CLOB;
      v_blob_offset          NUMBER;
      v_clob_offset          NUMBER;
      v_blob_csid            NUMBER      := 873;
      v_lang_context         NUMBER      := DBMS_LOB.default_lang_ctx;
      v_warning              NUMBER;
      v_amount               PLS_INTEGER;
      v_append_string        VARCHAR2(100);
      v_append_string_len    NUMBER      := 0; 
      v_xmltype              XMLTYPE;  
   BEGIN
      v_blob_locator := UTL_COMPRESS.lz_uncompress(p_blob);
      v_amount := DBMS_LOB.getlength (v_blob_locator);
  
      DBMS_LOB.createtemporary (v_clob_locator, TRUE);
      v_append_string := '<' || NVL(p_root_tag_name, 'xmltype-root') || '>';
      v_append_string_len := LENGTH(v_append_string);
  
      DBMS_LOB.writeappend (v_clob_locator, v_append_string_len, v_append_string);
  
      v_clob_offset := v_append_string_len + 1;
      v_blob_offset := 1;
  
      convert_to_clob (v_clob_locator,
                       v_blob_locator,
                       v_amount,
                       v_clob_offset,
                       v_blob_offset,
                       v_blob_csid,
                       v_lang_context,
                       v_warning );
                      
      IF v_warning = DBMS_LOB.warn_inconvertible_char
      THEN
         RAISE_APPLICATION_ERROR(-20000,'Inconvertible Char Error');
      END IF;
     
      v_append_string := '</' || NVL(p_root_tag_name, 'xmltype-root') || '>';
      v_append_string_len := LENGTH(v_append_string);
     
      DBMS_LOB.writeappend (v_clob_locator, v_append_string_len, v_append_string);     
     
      v_xmltype := XMLTYPE.createxml (v_clob_locator);
     
      DBMS_LOB.FREETEMPORARY (v_clob_locator);
      RETURN v_xmltype;     
     
      EXCEPTION WHEN others
      THEN
         v_xmltype := NULL;
         DBMS_OUTPUT.PUT_LINE(SQLERRM);
         --DBMS_LOB.FREETEMPORARY (v_clob_locator);
         RETURN v_xmltype;
     
   END;