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; / show errors