DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • Going Serverless With Oracle Stored Procedures
  • A New Era Has Come, and So Must Your Database Observability
  • Conversational Applications With Large Language Models Understanding the Sequence of User Inputs, Prompts, and Responses
  • Automating Database Operations With Ansible and DbVisualizer

Trending

  • Memory Management in Java: An Introduction
  • Selecting the Right Automated Tests
  • Effective Tips for Debugging Complex Code in Java
  • Unraveling Lombok's Code Design Pitfalls: Exploring Encapsulation Issues
  1. DZone
  2. Data Engineering
  3. Databases
  4. Export Audit Trail from Database SOA 10g and 11g

Export Audit Trail from Database SOA 10g and 11g

Gilberto Holms user avatar by
Gilberto Holms
·
Apr. 14, 14 · Interview
Like (1)
Save
Tweet
Share
8.72K Views

Join the DZone community and get the full member experience.

Join For Free

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!

 

SOA Database

Published at DZone with permission of Gilberto Holms, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Going Serverless With Oracle Stored Procedures
  • A New Era Has Come, and So Must Your Database Observability
  • Conversational Applications With Large Language Models Understanding the Sequence of User Inputs, Prompts, and Responses
  • Automating Database Operations With Ansible and DbVisualizer

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: