SQL Logging mechanism to debug OIM DB PLSQL Blocks

1. Create below sequence, table and procedure.

  DROP SEQUENCE procinfo_seq
  /
  DROP TABLE proc_info
  /
  DROP PROCEDURE log_progress
  /

  CREATE SEQUENCE PROCINFO_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCACHE;

  create table proc_info(sno NUMBER,progress varchar2(4000));

  CREATE OR REPLACE PROCEDURE log_progress(p_step IN VARCHAR2)
  AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
  INSERT INTO proc_info values(PROCINFO_SEQ.NEXTVAL,p_step);
  COMMIT;
  END;
  / 

2. Use the procedure whereever you want. That procedure inserts the data into that table and later you can query on.
 Here is the sample usuage of that procedure.
 

  log_progress('intRecordcount=>'||intRecordcount);

3. Once the usecase is executed, you can query the table.

  select * from proc_info;