set echo on

drop table audit_dml
/
create table audit_dml
(   timestamp           date,
    who                 varchar2(30),
    op                  varchar2(6),
    seq                 number(6),
    tname               varchar2(30),
    cname               varchar2(30),
    old                 varchar2(2000),
    new                 varchar2(2000),
    rid_old             rowid,
    rid_new             rowid,
    data_type           char(1) 
)
/

drop sequence x#_seq;

create sequence x#_seq;

create or replace package audit_pkg
as
    procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in varchar2, 
        l_old in varchar2,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number
                        );

    procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in date, 
        l_old in date,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number
                        );

    procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in number, 
        l_old in number,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number
                        );
end;
/


create or replace package body audit_pkg
as
procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in varchar2, 
        l_old in varchar2,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_dml values
        ( sysdate, sys_context('USERENV','OS_USER'), l_op, l_seq, upper(l_tname), upper(l_cname),
                             l_old, l_new, l_rid_old, l_rid_new, 'C' );
    end if;
end;

procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in date, 
        l_old in date,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_dml values
        ( sysdate, sys_context('USERENV','OS_USER'), l_op,l_seq,  upper(l_tname), upper(l_cname),
          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
          to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ), l_rid_old, l_rid_new , 'D' );
    end if;
end;

procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in number, 
        l_old in number,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_dml values
        ( sysdate, sys_context('USERENV','OS_USER'), l_op, l_seq, upper(l_tname), upper(l_cname),
                                 l_old, l_new, l_rid_old, l_rid_new , 'N' );
    end if;
end;

end audit_pkg;
/
set echo off