Extract Composite Audit Trail from database table as XML

After I had done some reading on the database tables that were used for storing the instance details, I did find some information from Oracle forums to use the composite instance id to extract the audit xml from the database tables. A function had to created in the database under the same user (preferably) i.e. SOAINFRA. In case of 10g, to view the BPEL audit xml, create the function in ORABPEL. The function will take composite instance as input and return the audit as BLOB, through a query.

Function be created:

CREATE OR REPLACE FUNCTION get_audit_trail_log(cikey IN INTEGER) RETURN blob IS
--
CURSOR c_log(l_cikey INTEGER) IS
SELECT *
FROM audit_trail atr
WHERE cikey = l_cikey
ORDER BY count_id;
--
bl BLOB;
BEGIN
dbms_lob.createtemporary (bl, TRUE);
FOR r_log IN c_log(cikey)
LOOP
dbms_lob.append (bl,r_log.log);
END LOOP;
--
RETURN(bl);
END;

Query to fetch the details of an instance (instance id = 12345678):

SELECT UTL_COMPRESS.LZ_UNCOMPRESS(get_audit_trail_log(ci.cikey))
FROM cube_instance ci
WHERE ci.cikey = 12345678

To read more on this, visit the Oracle Forums where this thread is available.