create or replace package BCF$UTL_JOURNALLING authid current_user is

/*

Simple utility for generating journal tables and creating triggers on the table being
journalized for logging all changes to the generated journal table.

(c) 2007-2010 Jan Holst Jensen, jan@biochemfusion.com.

2009-05-13: Initial release on the net.
2010-06-06: Fix for empty trigger bodies if lowercase table name passed.

This particular package, BCF$UTL_JOURNALLING is released under a BSD-style
license:

* Copyright (c) 2007-2010, biochemfusion.com
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*     * Redistributions of source code must retain the above copyright
*       notice, this list of conditions and the following disclaimer.
*     * Redistributions in binary form must reproduce the above copyright
*       notice, this list of conditions and the following disclaimer in the
*       documentation and/or other materials provided with the distribution.
*     * Neither the name of biochemfusion.com nor the names of its contributors
*       may be used to endorse or promote products derived from this software
*       without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY biochemfusion.com ``AS IS'' AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL biochemfusion.com BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

*/

  procedure create_journal_table(a_table_name in varchar2);
  
  procedure update_journal_triggers(a_table_name in varchar2);

end BCF$UTL_JOURNALLING;
/
create or replace package body BCF$UTL_JOURNALLING is

  type t_cursor is ref cursor;
  
  LF constant varchar2(1) := chr(10);
  
  function select_column_metadata(a_table_name in varchar2) return t_cursor is
    sanity_count integer;
    result t_cursor;
  begin
    select count(*) into sanity_count
      from all_tab_columns
     where owner = user
       and table_name = upper(a_table_name);

    if sanity_count = 0 then
      raise_application_error(-20000, 'No columns in "' || a_table_name || '" table!');
    end if;

    open result for
      select column_name, data_type, data_length, data_precision, data_scale 
        from all_tab_columns
       where owner = user
         and table_name = upper(a_table_name)
       order by column_id;
       
    return result;
  end;
  
  -- 'execute immediate' does not raise a visible exception on compile errors,
  -- so let's make sure they are clearly visible.
  procedure run_ddl(some_ddl in varchar2) is
  begin
    begin
      execute immediate some_ddl;
    exception
      when others then
        raise_application_error(-20000, 'Error "' || SQLERRM || '" executing ' || LF || some_ddl);
    end;
  end;

  procedure create_journal_table(a_table_name in varchar2) is
    create_statement varchar2(32000);
    --
    column_metadata t_cursor;
    column_name varchar2(40);
    data_type varchar2(120);
    data_length integer;
    data_precision integer;
    data_scale integer;
  begin
    column_metadata := select_column_metadata(a_table_name);
    
    create_statement := 'create table JN$' || a_table_name || 
      ' (jn$timestamp timestamp default sysdate, jn$operation varchar2(3),' ||
      ' jn$oracle_user varchar2(40) default user,' ||
      ' jn$os_user varchar2(40) default sys_context(''USERENV'', ''OS_USER'')';
    
    loop
      fetch column_metadata into 
        column_name, data_type, data_length, data_precision, data_scale;
      exit when not column_metadata%found;
      create_statement := create_statement || ', ' || column_name || ' ' || data_type;
      if (data_type = 'NUMBER') then
        if (data_precision is null and data_scale = 0) then
          create_statement := create_statement || '(*,0)';
        else
          if (data_precision is not null and data_scale is not null) then
            create_statement := create_statement || 
              '(' || data_precision || ',' || data_scale || ')';
          end if;
        end if;
      end if;
      if (data_type = 'VARCHAR2' or data_type = 'RAW') then
        create_statement := create_statement || '(' || data_length || ')';
      end if;
    end loop;

    create_statement := create_statement || ')';
    
    dbms_output.put_line(create_statement);
    run_ddl(create_statement);
  end;
  
  procedure update_journal_triggers(a_table_name in varchar2) is
    insert_trigger varchar2(32000);
    insert_trigger_end varchar2(32000);
    update_trigger varchar2(32000);
    update_trigger_end varchar2(32000);
    delete_trigger varchar2(32000);
    delete_trigger_end varchar2(32000);
    --
    column_metadata t_cursor;
    column_name varchar2(40);
    data_type varchar2(120);
    data_length integer;
    data_precision integer;
    data_scale integer;
  begin
    insert_trigger := 'create or replace trigger JN$AIR_' || a_table_name || LF ||
      '  after insert on ' || a_table_name || LF || '  for each row' || LF ||
      'begin' || LF ||
      '  insert into JN$' || a_table_name || ' (jn$operation';
    insert_trigger_end := '  values (''INS''';

    update_trigger := 'create or replace trigger JN$AUR_' || a_table_name || LF ||
      '  after update on ' || a_table_name || LF || '  for each row' || LF ||
      'begin' || LF ||
      '  insert into JN$' || a_table_name || ' (jn$operation';
    update_trigger_end := '  values (''UPD''';
    
    delete_trigger := 'create or replace trigger JN$BDR_' || a_table_name || LF ||
      '  before delete on ' || a_table_name || LF || '  for each row' || LF ||
      'begin' || LF ||
      '  insert into JN$' || a_table_name || ' (jn$operation';
    delete_trigger_end := '  values (''DEL''';
      
    column_metadata := select_column_metadata(a_table_name);
    
    loop
      fetch column_metadata into 
        column_name, data_type, data_length, data_precision, data_scale;
      exit when not column_metadata%found;
      
      insert_trigger := insert_trigger || ', ' || column_name;
      insert_trigger_end := insert_trigger_end || ', :new.' || column_name;

      update_trigger := update_trigger || ', ' || column_name;
      update_trigger_end := update_trigger_end || ', :new.' || column_name;

      delete_trigger := delete_trigger || ', ' || column_name;
      delete_trigger_end := delete_trigger_end || ', :old.' || column_name;
    end loop;

    insert_trigger := insert_trigger || 
      ')' || LF || insert_trigger_end || ');' || LF || 
      'end JN$AIR_' || a_table_name || ';';
    dbms_output.put_line(insert_trigger);
    dbms_output.put_line('');
    run_ddl(insert_trigger);

    update_trigger := update_trigger || 
      ')' || LF || update_trigger_end || ');' || LF || 
      'end JN$AUR_' || a_table_name || ';';
    dbms_output.put_line(update_trigger);
    dbms_output.put_line('');
    run_ddl(update_trigger);

    delete_trigger := delete_trigger || 
      ')' || LF || delete_trigger_end || ');' || LF || 
      'end JN$BDR_' || a_table_name || ';';
    dbms_output.put_line(delete_trigger);
    run_ddl(delete_trigger);
    
    dbms_output.put_line('');
    dbms_output.put_line('All triggers created or updated successfully.');
  end;

end BCF$UTL_JOURNALLING;