PKG_NKW_DNT_LOGIES.SPB
Source Package Body
Dynamic Trigger - Log de manipulações
create or replace package body pkg_nkw_dnt_logies
timestamp '2006-07-07:10:10:10'
is
------------------------------------------------------------------
-- 2006 DataPRO Developers - m@urelio
------------------------------------------------------------------
-- Version: 2.0.2
------------------------------------------------------------------
-- Collections, Records, Variables, Constants, Exceptions, Cursors
------------------------------------------------------------------
type rec_logies is record(insert_flag nkw_dnt_logies.insert_flag%TYPE,
update_flag nkw_dnt_logies.update_flag%TYPE,
delete_flag nkw_dnt_logies.delete_flag%TYPE);
type tab_logies is table of rec_logies index by binary_integer;
gt_logies tab_logies;
gv_object varchar2(30);
gv_audid nkw_auditab.audid%TYPE;
gv_action varchar2(30);
gv_stdcur integer;
gv_execur integer;
gv_stmt pkg_nkw_env.gv_plchar%TYPE;
------------------------------------------------------------------
----------------------- Private Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- CHECK_AUDID
------------------------------------------------------------------
procedure check_audid
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT_LOGIES.CHECK_AUDID' || chr(10);
i binary_integer;
begin
if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then
i := pkg_nkw_obj.index_of(pkg_nkw_dnt.gt_new_data, 'audid');
if (pkg_nkw_dnt.gt_new_data.exists(i)) then
gv_audid := pkg_nkw_dnt.gt_new_data(i).varchar2_value;
end if;
elsif (upper(pkg_nkw_dnt.gv_action) = 'DELETE') then
i := pkg_nkw_obj.index_of(pkg_nkw_dnt.gt_old_data, 'audid');
if (pkg_nkw_dnt.gt_old_data.exists(i)) then
gv_audid := pkg_nkw_dnt.gt_old_data(i).varchar2_value;
end if;
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end check_audid;
------------------------------------------------------------------
-- POP_LOGIES
------------------------------------------------------------------
procedure pop_logies
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT_LOGIES.POP_LOGIES' || chr(10);
lv_datatype varchar2(30);
lv_audid# binary_integer;
i binary_integer;
begin
if (upper(nvl(gv_object,'+')) <> upper(nvl(pkg_nkw_dnt.gv_object,'-'))
or nvl(gv_action,'+') <> nvl(pkg_nkw_dnt.gv_action,'-')) then
gt_logies.delete;
gv_stmt := null;
if (dbms_sql.is_open(gv_stdcur)) then
dbms_sql.close_cursor(gv_stdcur);
end if;
gv_stdcur := null;
gv_execur := null;
begin
declare cursor get_cur is
select a.column_id, a.insert_flag, a.update_flag, a.delete_flag
from nkw_dnt_logies a
where (a.table_name = upper(pkg_nkw_dnt.gv_object));
get_row get_cur%ROWTYPE;
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
<< get_loop >>
loop
fetch get_cur into get_row;
exit get_loop when (get_cur%NOTFOUND);
i := get_row.column_id;
gt_logies(i).insert_flag := get_row.insert_flag;
gt_logies(i).update_flag := get_row.update_flag;
gt_logies(i).delete_flag := get_row.delete_flag;
end loop get_loop;
if (get_cur%ISOPEN) then
close get_cur;
end if;
exception
when others then
if (get_cur%ISOPEN) then
close get_cur;
end if;
raise;
end;
end;
begin
i := gt_logies.first;
if (i is not null) then
gv_stmt := 'begin' || chr(10) ||
' declare lv_audid nkw_auditab.audid%TYPE := ''' || gv_audid || ''';' || chr(10) ||
' lv_logtxt pkg_nkw_env.gv_char%TYPE;' || chr(10) ||
' begin' || chr(10);
end if;
<< log_loop >>
while (i is not null) loop
if (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'DATE') then
if (upper(gt_logies(i).insert_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'INSERT') then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_new_data(i).column_name) || '(NEW): '' ||' ||
' to_char(pkg_nkw_dnt.gt_new_data(' || i ||
').date_value,''DD/MM/YYYY HH24:MI:SS'') || chr(10);' || chr(10);
end if;
end if;
if (upper(gt_logies(i).update_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then
if (nvl(pkg_nkw_dnt.gt_old_data(i).date_value, trunc(sysdate)) <>
nvl(pkg_nkw_dnt.gt_new_data(i).date_value, trunc(sysdate))) then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_old_data(i).column_name) || '(OLD): '' ||' ||
' to_char(pkg_nkw_dnt.gt_old_data(' || i ||
').date_value,''DD/MM/YYYY HH24:MI:SS'') || chr(10) || ''' ||
upper(pkg_nkw_dnt.gt_new_data(i).column_name) || '(NEW): '' ||' ||
' to_char(pkg_nkw_dnt.gt_new_data(' || i ||
').date_value,''DD/MM/YYYY HH24:MI:SS'') || chr(10);' || chr(10);
end if;
end if;
end if;
if (upper(gt_logies(i).delete_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'DELETE') then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_old_data(i).column_name) || '(OLD): '' ||' ||
' to_char(pkg_nkw_dnt.gt_old_data(' || i ||
').date_value,''DD/MM/YYYY HH24:MI:SS'') || chr(10);' || chr(10);
end if;
end if;
elsif (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'NUMBER') then
if (upper(gt_logies(i).insert_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'INSERT') then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_new_data(i).column_name) || '(NEW): '' ||' ||
' to_char(pkg_nkw_dnt.gt_new_data(' || i ||
').number_value) || chr(10);' || chr(10);
end if;
end if;
if (upper(gt_logies(i).update_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then
if (nvl(pkg_nkw_dnt.gt_old_data(i).number_value, -1) <>
nvl(pkg_nkw_dnt.gt_new_data(i).number_value, -1)) then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_old_data(i).column_name) || '(OLD): '' ||' ||
' to_char(pkg_nkw_dnt.gt_old_data(' || i ||
').number_value) || chr(10) || ''' ||
upper(pkg_nkw_dnt.gt_new_data(i).column_name) || '(NEW): '' ||' ||
' to_char(pkg_nkw_dnt.gt_new_data(' || i ||
').number_value) || chr(10);' || chr(10);
end if;
end if;
end if;
if (upper(gt_logies(i).delete_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'DELETE') then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_old_data(i).column_name) || '(OLD): '' ||' ||
' to_char(pkg_nkw_dnt.gt_old_data(' || i ||
').number_value) || chr(10);' || chr(10);
end if;
end if;
elsif (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'VARCHAR2') then
if (upper(gt_logies(i).insert_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'INSERT') then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_new_data(i).column_name) || '(NEW): '' ||' ||
' pkg_nkw_dnt.gt_new_data(' || i || ').varchar2_value || chr(10);' || chr(10);
end if;
end if;
if (upper(gt_logies(i).update_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then
if (nvl(pkg_nkw_dnt.gt_old_data(i).varchar2_value, ':)') <>
nvl(pkg_nkw_dnt.gt_new_data(i).varchar2_value, ':)')) then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_old_data(i).column_name) || '(OLD): '' ||' ||
' pkg_nkw_dnt.gt_old_data(' || i ||
').varchar2_value || chr(10) || ''' ||
upper(pkg_nkw_dnt.gt_new_data(i).column_name) || '(NEW): '' ||' ||
' pkg_nkw_dnt.gt_new_data(' || i || ').varchar2_value || chr(10);' || chr(10);
end if;
end if;
end if;
if (upper(gt_logies(i).delete_flag) = 'Y') then
if (upper(pkg_nkw_dnt.gv_action) = 'DELETE') then
gv_stmt := gv_stmt || ' lv_logtxt := lv_logtxt || ''' ||
upper(pkg_nkw_dnt.gt_old_data(i).column_name) || '(OLD): '' ||' ||
' pkg_nkw_dnt.gt_old_data(' || i || ').varchar2_value || chr(10);' || chr(10);
end if;
end if;
end if;
i := gt_logies.next(i);
end loop log_loop;
i := gt_logies.first;
if (i is not null) then
gv_stmt := gv_stmt ||
' if (lv_audid is not null and lv_logtxt is not null) then' || chr(10) ||
' lock table nkw_auditab in row exclusive mode nowait;' || chr(10) ||
' insert into nkw_auditab(timestamp, audid, audsid, object_name,' || chr(10) ||
' dml_action, user_id, username, text)' || chr(10) ||
' values(sysdate, lv_audid, pkg_nkw_dnt.gr_session.audsid,' || chr(10) ||
' upper(pkg_nkw_dnt.gv_object), upper(pkg_nkw_dnt.gv_action),' || chr(10) ||
' pkg_nkw_dnt.gr_session.user#, pkg_nkw_dnt.gr_session.username,' || chr(10) ||
' lv_logtxt);' || chr(10) ||
' end if;' || chr(10) ||
' exception' || chr(10) ||
' when pkg_nkw_exc.obj_locked then' || chr(10) ||
' raise_application_error(-20000, pkg_nkw_env.gv_shout ||' || chr(10) ||
' ''No lock acquired on NKW_AUDITAB.|'');' || chr(10) ||
' when others then raise;' || chr(10) ||
' end;' || chr(10) ||
'end;' || chr(10);
end if;
end;
gv_object := upper(pkg_nkw_dnt.gv_object);
gv_action := pkg_nkw_dnt.gv_action;
end if;
exception
when others then
gv_object := null;
gv_action := null;
raise_application_error(-20000, lv_scope || sqlerrm);
end pop_logies;
------------------------------------------------------------------
-- LOG_LOGIES
------------------------------------------------------------------
procedure log_logies
is
lv_path pkg_nkw_hfs.gv_path%TYPE := '/home/oracle/utlfile';
lv_file pkg_nkw_hfs.gv_file%TYPE := lower(user) || '.' || 'logies';
lv_data pkg_nkw_env.gv_plchar%TYPE;
begin
pkg_nkw_hfs.fwrite(fv_buffer => pkg_nkw_dnt.gv_label || gv_stmt,
fv_path => lv_path,
fv_file => lv_file);
exception
when others then null;
end log_logies;
------------------------------------------------------------------
-- PARSE_LOGIES
------------------------------------------------------------------
procedure parse_logies
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT_LOGIES.PARSE_LOGIES' || chr(10);
i binary_integer;
begin
if (gv_stmt is not null) then
log_logies;
if (dbms_sql.is_open(gv_stdcur)) then
gv_execur := dbms_sql.execute(gv_stdcur);
else
gv_stdcur := dbms_sql.open_cursor;
dbms_sql.parse(gv_stdcur, gv_stmt, dbms_sql.native);
gv_execur := dbms_sql.execute(gv_stdcur);
end if;
end if;
exception
when others then
if (dbms_sql.is_open(gv_stdcur)) then
dbms_sql.close_cursor(gv_stdcur);
end if;
raise_application_error(-20000, lv_scope || sqlerrm);
end parse_logies;
------------------------------------------------------------------
------------------------ Public Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- PARSE_ACTION
------------------------------------------------------------------
procedure parse_action
is
begin
gv_audid := null;
check_audid;
if (gv_audid is null) then
return;
end if;
pop_logies;
log_logies;
parse_logies;
exception
when others then raise;
end parse_action;
end pkg_nkw_dnt_logies;
|
