create or replace procedure transform_oracle_audit(pStr varchar2, 
pTime varchar2, plogging_host varchar2, pSYSLOGFACILITY number, pSYSLOGSEVERITY number) 
IS
    logging_time date;
    logging_host varchar2(255);
    sessionid varchar2(500);
    entryid varchar2(500);
    statement number;
    USERID VARCHAR2(30); 
    USERHOST VARCHAR2(128); 
    TERMINAL VARCHAR2(255);
    ACTION# NUMBER;
    RETURNCODE NUMBER;    
    OBJ$NAME VARCHAR2(128);
    OBJ$CREATOR VARCHAR2(30);
    COMMENT$TEXT VARCHAR2(4000);
    LOGOFF$LREAD NUMBER; 
    LOGOFF$PREAD NUMBER;
    LOGOFF$LWRITE NUMBER;
    LOGOFF$DEAD NUMBER;
    PRIV$USED NUMBER;
    SESSIONCPU NUMBER;
    AUTH$PRIVILEGES VARCHAR2(16);
    AUTH$GRANTEE VARCHAR2(30);
    NEW$OWNER VARCHAR2(30);
    NEW$NAME VARCHAR2(128);
    SES$ACTIONS VARCHAR2(19);
    SES$TID NUMBER;
    CLIENTID VARCHAR2(64);
    SPARE1 VARCHAR2(255);
    SPARE2 NUMBER;
    OBJ$LABEL varchar2(255); 
    SES$LABEL varchar2(255);
    PROXY$SID NUMBER;
    USER$GUID VARCHAR2(32);
    INSTANCE# NUMBER;
    PROCESS# VARCHAR2(16);
    XID varchar2(8);
    AUDITID VARCHAR2(64);
    SCN NUMBER;
    DBID NUMBER;
    OBJ$EDITION VARCHAR2(30);
 
 
    function get_value(pAttribute varchar2) return varchar2 is
      regexp  varchar2(1000) := replace(upper(pAttribute), '$', '\$') || ':(\[\d+\]+)?\s+\"(.+?)\"';
    begin
      return regexp_replace(regexp_substr(pStr, regexp), regexp, '\2');
    end;


  begin
    logging_time:=to_date(pTime, 'YYYYMMDDHH24MISS');
    sessionid:=get_value('sessionid');
    userid:=get_value('userid');
    entryid:=get_value('entryid');
    statement:=get_value('statement');
    userhost:=get_value('userhost');
    action#:=get_value('action#');
    returncode:=get_value('returncode');
    comment$text:=get_value('comment$text');
    priv$used:=get_value('priv$used'); 
    TERMINAL:=get_value('TERMINAL');
    OBJ$CREATOR:=get_value('OBJ$CREATOR');
    OBJ$NAME:=get_value('OBJ$NAME');
    AUTH$PRIVILEGES:=get_value('AUTH$PRIVILEGES');
    AUTH$GRANTEE:=get_value('AUTH$GRANTEE');
    NEW$OWNER:=get_value('NEW$OWNER');
    NEW$NAME:=get_value('NEW$NAME');
    SES$ACTIONS:=get_value('SES$ACTIONS');
    SES$TID:=get_value('SES$TID');
    LOGOFF$LREAD:=get_value('LOGOFF$LREAD'); 
    LOGOFF$PREAD:=get_value('LOGOFF$PREAD');
    LOGOFF$LWRITE:=get_value('LOGOFF$LWRITE');
    LOGOFF$DEAD:=get_value('LOGOFF$DEAD');
    CLIENTID:=get_value('CLIENTID');
    SPARE1:=get_value('SPARE1');
    SPARE2:=get_value('SPARE2');
    OBJ$LABEL:=get_value('OBJ$LABEL'); 
    SES$LABEL:=get_value('SES$LABEL');
    SESSIONCPU:=get_value('SESSIONCPU');
    PROXY$SID:=get_value('PROXY$SID');
    USER$GUID:=get_value('USER$GUID');
    INSTANCE#:=get_value('INSTANCE#');
    PROCESS#:=get_value('PROCESS#');
    XID:=get_value('XID');
    AUDITID:=get_value('AUDITID');
    SCN:=get_value('DBID');
    DBID:=get_value('DBID');
    OBJ$EDITION:=get_value('OBJ$EDITION');
  
  
    
    insert into oracle_audit_log (logging_time,logging_host,SYSLOGFACILITY,SYSLOGSEVERITY,SESSIONID,ENTRYID,STATEMENT,USERID,USERHOST,TERMINAL,
    ACTION#,RETURNCODE,OBJ$CREATOR,OBJ$NAME,AUTH$PRIVILEGES,AUTH$GRANTEE,NEW$OWNER,NEW$NAME,
    SES$ACTIONS,SES$TID,LOGOFF$LREAD,LOGOFF$PREAD,LOGOFF$LWRITE,LOGOFF$DEAD,COMMENT$TEXT,
    CLIENTID,SPARE1,SPARE2,OBJ$LABEL,SES$LABEL,PRIV$USED,SESSIONCPU,PROXY$SID,USER$GUID,
    INSTANCE#,PROCESS#,XID,AUDITID,SCN,DBID,OBJ$EDITION)
    values
    (logging_time,plogging_host,pSYSLOGFACILITY,pSYSLOGSEVERITY,SESSIONID,ENTRYID,STATEMENT,USERID,USERHOST,TERMINAL,
    ACTION#,RETURNCODE,OBJ$CREATOR,OBJ$NAME,AUTH$PRIVILEGES,AUTH$GRANTEE,NEW$OWNER,NEW$NAME,
    SES$ACTIONS,SES$TID,LOGOFF$LREAD,LOGOFF$PREAD,LOGOFF$LWRITE,LOGOFF$DEAD,COMMENT$TEXT,
    CLIENTID,SPARE1,SPARE2,OBJ$LABEL,SES$LABEL,PRIV$USED,SESSIONCPU,PROXY$SID,USER$GUID,
    INSTANCE#,PROCESS#,XID,AUDITID,SCN,DBID,OBJ$EDITION);
    commit;

  end;