DML / SQL when enabling auditing

I’ve enabled auditing many times before.  Everytime has been a nightmare. You need lots of space for big tables, this could be done MUCH better with a rename statement!  Anyway, I need to stop complaining…  So, back to my story, auditing enabled fine in DV, fine in PY – go for PROD – ERROR…  Or more accurately – warning…

For the last statement below, I get:

892/7740 WRK:Starting jdeCallObject            Tue Oct 20 20:36:04.351001    dbperfrq.c462
    OCI0000179 - Error - ORA-24344: success with compilation error
892/7740 WRK:Starting jdeCallObject            Tue Oct 20 20:36:04.366001    Jdb_drvm.c1116
    JDB9900401 - Failed to execute db request

Of course – NO MORE INFO.  SO I have to run all of this manually and try and determine why it’s not working. show err at the command line after the final trigger create statement will show the problems in SQLPLUS.

 

CREATE TABLE PRODDTA.F0011_ADT (ICICUT CHAR(2), ICICU NUMBER, ICIST CHAR(1), ICIAPP CHAR(1), ICAICU NUMBER, ICUSER CHAR(10), ICDICJ NUMBER(6), ICNDO NUMBER, ICBAL CHAR(1), ICBALJ CHAR(1), ICAME NUMBER, ICDOCN NUMBER, ICAUSR CHAR(10), ICPOB CHAR(1), ICIBOI CHAR(1), ICAIPT NUMBER, ICOFFP CHAR(1), ICPID CHAR(10), ICJOBN CHAR(10), ICUPMJ NUMBER(6), ICUPMT NUMBER, ICDRSP CHAR(1), IC52PP CHAR(1), ICCBP CHAR(1)) tablespace proddtat                                           storage ( pctincrease 0 ) ;

GRANT ALL ON PRODDTA.F0011_ADT TO PUBLIC ;

CREATE  UNIQUE INDEX PRODDTA.F0011_ADT_0 ON PRODDTA.F0011_ADT (ICICUT ASC, ICICU ASC) tablespace proddtai                                           storage ( pctincrease 0 ) ;

ALTER TABLE PRODDTA.F0011_ADT ADD CONSTRAINT F0011_ADT_PK PRIMARY KEY (ICICUT, ICICU) ;

INSERT INTO PRODDTA.F0011_ADT (ICICUT, ICICU, ICIST, ICIAPP, ICAICU, ICUSER, ICDICJ, ICNDO, ICBAL, ICBALJ, ICAME, ICDOCN, ICAUSR, ICPOB, ICIBOI, ICAIPT, ICOFFP, ICPID, ICJOBN, ICUPMJ, ICUPMT, ICDRSP, IC52PP, ICCBP) SELECT ICICUT, ICICU, ICIST, ICIAPP, ICAICU, ICUSER, ICDICJ, ICNDO, ICBAL, ICBALJ, ICAME, ICDOCN, ICAUSR, ICPOB, ICIBOI, ICAIPT, ICOFFP, ICPID, ICJOBN, ICUPMJ, ICUPMT, ICDRSP, IC52PP, ICCBP FROM PRODDTA.F0011 ;

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS  WHERE  ( OBJECT_NAME = 'F0011' AND OBJECT_TYPE = 'VIEW' AND OWNER = 'PRODDTA' ) ;

DROP TABLE PRODDTA.F0011 ;

ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRGUID CHAR(36) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRMKEY CHAR(15) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRPID CHAR(10) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRUSER CHAR(10) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICISDELETE CHAR(1) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRSEQN NUMBER DEFAULT 0 ) ;

CREATE VIEW PRODDTA.F0011 AS SELECT ICICUT , ICICU , ICIST , ICIAPP , ICAICU , ICUSER , ICDICJ , ICNDO , ICBAL , ICBALJ , ICAME , ICDOCN , ICAUSR , ICPOB , ICIBOI , ICAIPT , ICOFFP , ICPID , ICJOBN , ICUPMJ , ICUPMT , ICDRSP , IC52PP , ICCBP  FROM PRODDTA.F0011_ADT ;

GRANT ALL ON PRODDTA.F0011 TO PUBLIC ;

DROP PACKAGE PRODDTA.F0011_CFRA_PKG ;

create or replace package PRODDTA.F0011_ADT_CFRA_PKG
as
  type ridArray is table of rowid index by binary_integer;
  ids ridArray;
  empty ridArray;
end ;

DROP TRIGGER PRODDTA.F0011_CFRA_RIA

-- ORACLE INSERT ROW BEFORE

create or replace trigger PRODDTA.F0011_CFRA_RIA
before insert on PRODDTA.F0011_ADT
for each row
declare
  guid char(36);
  user char(10);
  workstation char(15);
  programid varchar2(48);
  audittype char(1) := '1';
begin
  if (:new.ICCFRSEQN = 1) then
    -- JDB insert
    guid := :new.ICCFRGUID;
    if (guid = 'OW Without GUID') then
      audittype := '2';
      user := substr(ora_login_user, 1, 10);
      workstation := substr(sys_context('userenv', 'host'), 1, 15);
      select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
      :new.ICCFRUSER := user;
      :new.ICCFRMKEY := workstation;
      :new.ICCFRPID  := substr(programid, 1, 10);
    else
      audittype := '1';
      user := :new.ICCFRUSER;
      workstation := :new.ICCFRMKEY;
      programid := :new.ICCFRPID;
    end if;
  else
    -- 3rd party insert
    audittype := '3';
    guid := 'Third Party';
    user := substr(ora_login_user, 1, 10);
    workstation := substr(sys_context('userenv', 'host'), 1, 15);
    select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
    :new.ICCFRGUID := guid;
    :new.ICCFRUSER := user;
    :new.ICCFRMKEY := workstation;
    :new.ICCFRPID  := substr(programid, 1, 10);
  end if;

  -- write audit record for insert
  insert into PRODDTA.A0011 (ICCFRGUID, ICICUT ,ICICU , ICB4ORAFTR, ICCFRACTION,
    ICIST, ICUSER, ICDICJ,
    ICAUDITTYPE, ICCFRUSER, ICCFRMKEY, ICDATETIME, ICCFRPID) values
    (guid, :new.ICICUT ,:new.ICICU , 'A', '1',
    :new.ICIST, :new.ICUSER, :new.ICDICJ,
    audittype, user, workstation,
    to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));
end;