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-2011 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. 2011-10-07: Long table names up to 30 characters are now supported. Note, that this fix just uses a truncated version of the base table name to build journalling table names and trigger names. The journalling table names will therefore only use the first 27 characters of the base table name and the triggers only the first 23 characters of the base table name. So if you try to add journalling to two tables whose names only differ after the 23rd character you will get name clashes. This particular package, BCF$UTL_JOURNALLING is released under a BSD-style license: * Copyright (c) 2007-2011, 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; function journal_table_name_suffix(a_table_name varchar2) return varchar2 is begin return substr(a_table_name, 1, 27); end; function journal_trigger_name_suffix(a_table_name varchar2) return varchar2 is begin return substr(a_table_name, 1, 23); 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$' || journal_table_name_suffix(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 base_table_name varchar2(30); jn_table_name_sfx varchar2(30); jn_trig_name_sfx varchar2(30); -- 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 base_table_name := a_table_name; jn_table_name_sfx := journal_table_name_suffix(a_table_name); jn_trig_name_sfx := journal_trigger_name_suffix(a_table_name); insert_trigger := 'create or replace trigger JN$AIR_' || jn_trig_name_sfx || LF || ' after insert on ' || base_table_name || LF || ' for each row' || LF || 'begin' || LF || ' insert into JN$' || jn_table_name_sfx || ' (jn$operation'; insert_trigger_end := ' values (''INS'''; update_trigger := 'create or replace trigger JN$AUR_' || jn_trig_name_sfx || LF || ' after update on ' || base_table_name || LF || ' for each row' || LF || 'begin' || LF || ' insert into JN$' || jn_table_name_sfx || ' (jn$operation'; update_trigger_end := ' values (''UPD'''; delete_trigger := 'create or replace trigger JN$BDR_' || jn_trig_name_sfx || LF || ' before delete on ' || base_table_name || LF || ' for each row' || LF || 'begin' || LF || ' insert into JN$' || jn_table_name_sfx || ' (jn$operation'; delete_trigger_end := ' values (''DEL'''; column_metadata := select_column_metadata(base_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_' || jn_trig_name_sfx || ';'; 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_' || jn_trig_name_sfx || ';'; 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_' || jn_trig_name_sfx || ';'; 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; /