[BPEL] stale된 instance log 확인하기

Oracle SOA Suite 에서 BPEL 을 재 deploy 하게 되면, 기존의 cube_instance 의 state 가 stale 상태가 된다. (CUBE_INSTANCE Table 의 STATE 컬럼의 값이 9 가 됨)

이 상태에서는 BPEL Console 로 instance log 를 확인할 수가 없으나, 방법은 있다. CUBE_INSTANCE 테이블에서만 state 가 9로 변경되었을 뿐 SYNC_TRAIL, AUDIT_DETAILS 에는 여전히 남아있으므로 이 2개의 테이블을 이용하면 된다.

SYNC_TRAIL, AUDIT_DETAILS 의 BIN 컬럼에 log trail data 가 들어있으나, 이 컬럼의 data type 은 BLOB 이므로 확인하기가 쉽지는 않다.

따라서, 일단 이 BLOB 를 우리가 사용하기 편리한 XMLTYPE 으로 변경하자.

BLOB 데이터를 XMLTYPE 으로 변환하기

확인하고자 하는 instance 의 id (cikey) 를 BPEL Console 을 통해 확인한다. 아래 그림에서 보면 회색으로 표시된 instance 가 stale 상태이며, 여기서 앞의 빨간 네모 안의 34466122 등이 cikey 이다.

cikey 를 확인후, 다음 query 를 실행하면 XMLTYPE 형태의 instance log 를 확인할 수 있다. (blob_to_xmltype 은 이 포스트 를 참고한다.)

 

SELECT blob_to_xmltype (tt.bin, 'audit-trail')
  FROM orabpel.sync_trail tt
 WHERE tt.cikey = 34466122

 

[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;