Export Audit Trail from Database SOA 10g and 11g

For a long time I thought that was impossible to query the SOA composite instance audit trail directly from database. Several references on internet said that only SOA Management APIs could understand the format in which this information is stored into SOA Dehydration Store.

Based on this misinformation I wrote a post and build a tool based on these APIs (which will be reviewed soon):
http://gibaholms.wordpress.com/2013/07/18/soa-11g-audit-trail-exporter-tool/

However, I finally found a way to query the audit trail directly from Database. The audit trail extraction is very fast and simple, and can be done by anyone with access to the SOAINFRA tables.

This does not invalidate the usefulness of the SOA 11g Audit Trail Exporter Tool mentioned above due to simple features provided like easy of use, remote export, no need database access to export (only an EM user), file based output, etc.

To show the extraction directly from database, I wrote a simple PL-SQL function that does the Audit Trail trick. I encapsulated in a package called SOA_UTIL that can be compiled into a controlled area:

create or replace
PACKAGE SOA_UTIL AS
   
   -------------------------------------------------------------------------------------------
   -- Written by   : Gilberto Holms (http://gibaholms.wordpress.com/)
   -- Last update  : 03/02/2014
   -- Version      : 1.0
   -- Description  : Utility code to handle Oracle SOA Suite tables
   -------------------------------------------------------------------------------------------
   
  FUNCTION GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_CIKEY NUMBER) RETURN CLOB;
   
END SOA_UTIL;
 
/
 
create or replace
PACKAGE BODY SOA_UTIL AS
   
   -------------------------------------------------------------------------------------------
   -- Written by   : Gilberto Holms (http://gibaholms.wordpress.com/)
   -- Last update  : 03/02/2014
   -- Version      : 1.0
   -- Description  : Utility code to handle Oracle SOA Suite tables
   -------------------------------------------------------------------------------------------
   
  FUNCTION GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_CIKEY NUMBER) RETURN CLOB
  AS
    V_AUDIT_BLOB BLOB;
    V_AUDIT_CLOB CLOB;
   
    V_CUR_AUDIT SYS_REFCURSOR;
    TYPE TP_AUDIT_ARRAY IS TABLE OF BLOB;
    V_AUDIT_ARRAY TP_AUDIT_ARRAY;
   
    V_BUFFER_LENGTH PLS_INTEGER := 32767;
    V_BUFFER VARCHAR2(32767);
    V_READ_START PLS_INTEGER := 1;
  BEGIN
 
    DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
    DBMS_LOB.CREATETEMPORARY(V_AUDIT_CLOB, TRUE);
     
    OPEN V_CUR_AUDIT FOR 'SELECT LOG FROM ' || P_SCHEMA_NAME || '.AUDIT_TRAIL WHERE CIKEY = :cikey ORDER BY COUNT_ID' USING P_CIKEY;
    FETCH V_CUR_AUDIT BULK COLLECT INTO V_AUDIT_ARRAY;
    CLOSE V_CUR_AUDIT;
     
    FOR j IN 1..V_AUDIT_ARRAY.COUNT LOOP
      DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j));
    END LOOP;
    V_AUDIT_ARRAY.DELETE;
     
    V_AUDIT_BLOB := UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB);
     
    FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(V_AUDIT_BLOB) / V_BUFFER_LENGTH) LOOP
      V_BUFFER := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(V_AUDIT_BLOB, V_BUFFER_LENGTH, V_READ_START));
      DBMS_LOB.WRITEAPPEND(V_AUDIT_CLOB, LENGTH(V_BUFFER), V_BUFFER);
      V_READ_START := V_READ_START + V_BUFFER_LENGTH;
    END LOOP;
     
    RETURN V_AUDIT_CLOB;
     
  END;
   
END SOA_UTIL;
 
/

Then simply use this function inside a main query that filter the desired instances. The best is the fact that this function works for both SOA 10g and 11g.

Usage example for SOA Suite 11g:

SELECT
  CI.COMPOSITE_NAME AS COMPOSITE_NAME,
  CI.CMPST_ID AS INSTANCE_ID,
  to_char(CI.CREATION_DATE, 'dd/mm/yyyy hh24:mi:ss') AS CREATION_DATE,
  CI.STATUS AS STEP,
  SOA_UTIL.GET_AUDIT_TRAIL('FMW11116_SOAINFRA', CI.CIKEY) AS AUDIT_TRAIL
FROM
  FMW11116_SOAINFRA.CUBE_INSTANCE CI
WHERE
  CI.COMPOSITE_NAME = 'MyComposite'
  AND CI.CREATION_DATE BETWEEN (sysdate - 1) AND (sysdate)
  ORDER BY CI.CREATION_DATE DESC;  

Usage example for SOA Suite 10g:

SELECT
  CI.PROCESS_ID AS PROCESS_ID,
  CI.CIKEY AS INSTANCE_ID,
  to_char(CI.CREATION_DATE, 'dd/mm/yyyy hh24:mi:ss') AS CREATION_DATE,
  CI.STATUS AS STEP,
  SOA_UTIL.GET_AUDIT_TRAIL('ORABPEL', CI.CIKEY) AS AUDIT_TRAIL
FROM
  ORABPEL.CUBE_INSTANCE CI
WHERE
  CI.PROCESS_ID = 'MyBPEL'
  AND CI.CREATION_DATE BETWEEN (sysdate - 1) AND (sysdate)
  ORDER BY CI.CREATION_DATE DESC; 

Enjoy!