LOGGING

Package source

Legend: comment string keyword reserved word operator

     1: package logging
     2: is
     3:     procedure clear_log_id;
     4:     procedure set_log_auth(user_id in number);
     5:     function get_log_id return number;
     6:     procedure recreate_trigger(table_name_in in varchar);
     7:     procedure enable_logging(table_name_in in varchar);
     8: end logging;

Package body source

Legend: comment string keyword reserved word operator

     1: package body logging
     2: is
     3:     the_log_id number;
     4:     the_user_id number;
     5:     the_stamp timestamp with local time zone;
     6: 
     7:     procedure clear_log_id
     8:     is
     9:     begin
    10:         the_log_id := null;
    11:         the_user_id := null;
    12:         the_stamp := current_timestamp;
    13:     end clear_log_id;
    14: 
    15:     procedure set_log_auth(user_id in number)
    16:     is
    17:     begin
    18:         if the_stamp is null then
    19:         raise_application_error(-20299, 'Call set_log_auth need to follow clear_log_id.');
    20:         end if;
    21:         the_user_id := user_id;
    22:         the_stamp := current_timestamp;
    23:     end set_log_auth;
    24: 
    25:     function get_log_id return number
    26:     is
    27:     begin
    28:         if the_stamp is null then
    29:         raise_application_error(-20297, 'Call get_log_id need to follow set_log_auth.');
    30:         end if;
    31:         if the_log_id is null then
    32:         insert into log (id, stamp, user_id)
    33:         values (log_seq.nextval, the_stamp, the_user_id)
    34:             returning id into the_log_id;
    35:         end if;
    36:         return the_log_id;
    37:     end get_log_id;
    38: 
    39:     function get_pk_column(table_name_in in varchar) return varchar
    40:     is
    41:         pk_column varchar(512);
    42:     begin
    43: 	select column_name into pk_column
    44:         from user_cons_columns
    45:         where constraint_name = (
    46:             select constraint_name
    47:             from user_constraints
    48:             where table_name = upper(table_name_in)
    49:                 and constraint_type = 'P'
    50:                 and owner = user
    51:             )
    52:             and owner = user ;
    53:         return pk_column;
    54:     end get_pk_column;
    55: 
    56:     function get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) return varchar
    57:     is
    58:         ddl_columns varchar(4000);
    59:     begin
    60:         ddl_columns := '';
    61:         for rec in (
    62:             select column_name
    63:             from user_tab_columns
    64:             where table_name = upper(table_name_in)
    65:                 and column_name not in ( pk_column_in, 'CREATED', 'MODIFIED' )
    66:             order by column_id
    67:         ) loop
    68:             ddl_columns := ddl_columns || ', ' || rec.column_name;
    69:         end loop;
    70:         return ddl_columns;
    71:     end get_ddl_columns;
    72: 
    73:     procedure recreate_trigger(table_name_in in varchar)
    74:     is
    75:         pk_column varchar(512);
    76:         ddl_columns varchar(4000);
    77:         the_insert varchar(4000);
    78:     begin
    79:         pk_column := get_pk_column(table_name_in);
    80:         ddl_columns := get_ddl_columns(table_name_in, pk_column);
    81:         the_insert := 'insert into ' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||')
    82:                     values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', :old.') || ');';
    83:         execute immediate 'create or replace trigger ' || table_name_in || '_log_trig
    84:             after insert or update or delete on ' || table_name_in || '
    85:             for each row
    86:             declare
    87:                 log_id_v number;
    88:                 tg_op char(1);
    89:             begin
    90:                 log_id_v := logging.get_log_id();
    91:                 if updating then
    92:                     if :old.' || pk_column || ' <> :new.' || pk_column || ' then raise_application_error(-20298, ''Cannot update column ' || table_name_in || '.' || pk_column || '.''); end if;
    93:                     tg_op := ''U'';
    94:                 end if;
    95:                 if deleting then
    96:                         tg_op := ''D'';
    97:                     ' || the_insert || '
    98:                 else
    99:                     if inserting then tg_op := ''I''; end if;
   100:                     ' || replace(the_insert, ':old.', ':new.') || '
   101:                 end if;
   102:             end;
   103:             ';
   104:     end recreate_trigger;
   105: 
   106:     procedure enable_logging(table_name_in in varchar)
   107:     is
   108:         pk_column varchar(512);
   109:         ddl_columns varchar(4000);
   110:         already_not_null exception;
   111:         pragma exception_init(already_not_null, -1442);
   112:     begin
   113:         pk_column := get_pk_column(table_name_in);
   114:         ddl_columns := get_ddl_columns(table_name_in, pk_column);
   115: 
   116:         execute immediate 'create table ' || table_name_in || '_log
   117:             as select ' || pk_column || ', logging.get_log_id() as log_id, ''A'' as action' || ddl_columns
   118:             || ' from ' || table_name_in;
   119:         begin
   120:             execute immediate 'alter table ' || table_name_in || '_log modify ' || pk_column || ' not null';
   121:         exception when already_not_null then
   122:             null;
   123:         end;
   124:         execute immediate 'create index ' || table_name_in || '_log_idx on ' || table_name_in || '_log(' || pk_column || ')';
   125:         execute immediate 'alter table ' || table_name_in || '_log modify log_id not null';
   126:         execute immediate 'alter table ' || table_name_in || '_log add foreign key (log_id) references log(id)';
   127:         execute immediate 'alter table ' || table_name_in || '_log modify action not null';
   128:         recreate_trigger(table_name_in);
   129:     end enable_logging;
   130: end logging;