create or replace package body pkg_nkw_drm 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_relations is record(dml_table nkw_drm.dml_table%TYPE, relational_table nkw_drm.relational_table%TYPE, dml_action nkw_drm.dml_action%TYPE, raise_when nkw_drm.raise_when%TYPE, assertion_message nkw_drm.assertion_message%TYPE, text nkw_drm.text%TYPE, stmt pkg_nkw_env.gv_plchar%TYPE); type tab_relations is table of rec_relations index by binary_integer; gt_relations tab_relations; gv_lru_object sys.obj$.name%TYPE; gv_stmt constant pkg_nkw_env.gv_plchar%TYPE := 'begin ' || chr(10) || ' declare cursor drm_cur is ' || chr(10) || ' select 1 ' || chr(10) || ' from #TABLE# ' || chr(10) || ' #TEXT#;' || chr(10) || ' bv_bit number(1); ' || chr(10) || ' bv_found varchar2(10) := ''?''; ' || chr(10) || ' bv_raisew varchar2(10) := ''#RAISE_WHEN#'';' || chr(10) || ' bv_message pkg_nkw_env.gv_char%TYPE := ''#MESSAGE#.|'';' || chr(10) || ' begin ' || chr(10) || ' if (not drm_cur%ISOPEN) then ' || chr(10) || ' open drm_cur; ' || chr(10) || ' end if; ' || chr(10) || ' fetch drm_cur into bv_bit; ' || chr(10) || ' if (drm_cur%FOUND) then ' || chr(10) || ' bv_found := ''FOUND''; ' || chr(10) || ' elsif (drm_cur%NOTFOUND) then ' || chr(10) || ' bv_found := ''NOT FOUND''; ' || chr(10) || ' end if; ' || chr(10) || ' if (drm_cur%ISOPEN) then ' || chr(10) || ' close drm_cur; ' || chr(10) || ' end if; ' || chr(10) || ' if (bv_found = bv_raisew) then ' || chr(10) || ' raise_application_error(-20000, bv_message); ' || chr(10) || ' end if; ' || chr(10) || ' end; ' || chr(10) || ' exception ' || chr(10) || ' when others then raise;' || chr(10) || 'end;'; ------------------------------------------------------------------ ----------------------- Private Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- BIND_STMT ------------------------------------------------------------------ function bind_stmt(fv_text in pkg_nkw_env.gv_plchar%TYPE) return pkg_nkw_env.gv_plchar%TYPE is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DRM.REPLACE_TEXT' || chr(10); retval pkg_nkw_env.gv_plchar%TYPE := fv_text; i binary_integer; begin if (retval is not null) then i := pkg_nkw_drm.gt_data.first; while (i is not null) loop if (upper(pkg_nkw_drm.gt_data(i).data_type) = 'DATE') then retval := replace(retval, ':drm.' || lower(pkg_nkw_drm.gt_data(i).column_name), 'pkg_nkw_drm.gt_data(' || to_char(i) || ').date_value'); elsif (upper(pkg_nkw_drm.gt_data(i).data_type) = 'NUMBER') then retval := replace(retval, ':drm.' || lower(pkg_nkw_drm.gt_data(i).column_name), 'pkg_nkw_drm.gt_data(' || to_char(i) || ').number_value'); elsif (upper(pkg_nkw_drm.gt_data(i).data_type) = 'VARCHAR2') then retval := replace(retval, ':drm.' || lower(pkg_nkw_drm.gt_data(i).column_name), 'pkg_nkw_drm.gt_data(' || to_char(i) || ').varchar2_value'); end if; i := pkg_nkw_drm.gt_data.next(i); end loop; end if; return retval; exception when others then raise_application_error(-20000, lv_scope || sqlerrm); end bind_stmt; ------------------------------------------------------------------ -- POP_COLUMNS ------------------------------------------------------------------ procedure pop_columns is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DRM.POP_COLUMNS' || chr(10); begin gt_data.delete; gt_data := pkg_nkw_obj.get_columns(gv_object); exception when others then raise_application_error(-20000, lv_scope || sqlerrm); end pop_columns; ------------------------------------------------------------------ -- CHECK_DRM ------------------------------------------------------------------ procedure check_drm is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DRM.CHECK_DRM' || chr(10); begin if (nvl(gt_data.count,0) = 0) then raise_application_error(-20000, lv_scope || 'None DRM initialization.|'); end if; end check_drm; ------------------------------------------------------------------ -- BIND_DATATYPE ------------------------------------------------------------------ procedure bind_datatype(pv_column in varchar2, pv_data_type in varchar2, pv_date_value in date := null, pv_number_value in number := null, pv_varchar2_value in varchar2 := null) is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DRM.BIND_DATATYPE' || chr(10); lv_col# sys.col$.col#%TYPE; begin check_drm; if (pv_column is null) then raise_application_error(-20000, lv_scope || 'Missing column name.|'); end if; if (nvl(pkg_nkw_obj.index_of(gt_data, pv_column),0) = 0) then raise_application_error(-20000, lv_scope || 'Column ' || upper(gv_object) || '.' || upper(pv_column) || ' does not exists.|'); end if; begin lv_col# := pkg_nkw_obj.index_of(gt_data, pv_column); if (upper(pv_data_type) = 'DATE') then gt_data(lv_col#).date_value := pv_date_value; elsif (upper(pv_data_type) = 'NUMBER') then gt_data(lv_col#).number_value := pv_number_value; elsif (upper(pv_data_type) = 'VARCHAR2') then gt_data(lv_col#).varchar2_value := pv_varchar2_value; end if; exception when others then raise_application_error(-20000, lv_scope || sqlerrm); end; exception when others then raise; end bind_datatype; ------------------------------------------------------------------ -- LOG_DRM ------------------------------------------------------------------ procedure log_drm(fv_buffer in pkg_nkw_env.gv_plchar%TYPE) is begin pkg_nkw_hfs.fwrite(fv_buffer); exception when others then null; end log_drm; ------------------------------------------------------------------ ------------------------ Public Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- POP_DRMIES ------------------------------------------------------------------ procedure pop_drmies is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DRM.POP_DRMIES' || chr(10); lv_text pkg_nkw_env.gv_plchar%TYPE; lv_stmt pkg_nkw_env.gv_plchar%TYPE; i binary_integer := 1; begin gt_relations.delete; begin declare cursor get_cur is select a.dml_table, a.relational_table, a.relid, a.dml_action, a.raise_when, a.assertion_message, a.text from nkw_drm a where (a.dml_table = upper(gv_object)) and (a.relid > 0) order by a.relid; 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); lv_stmt := gv_stmt; gt_relations(i).dml_table := get_row.dml_table; gt_relations(i).relational_table := get_row.relational_table; gt_relations(i).dml_action := get_row.dml_action; gt_relations(i).raise_when := get_row.raise_when; gt_relations(i).assertion_message := get_row.assertion_message; lv_text := bind_stmt(get_row.text); gt_relations(i).text := lv_text; lv_stmt := replace(lv_stmt, '#TABLE#', lower(get_row.relational_table)); lv_stmt := replace(lv_stmt, '#TEXT#', lv_text); lv_stmt := replace(lv_stmt, '#RAISE_WHEN#', get_row.raise_when); lv_stmt := replace(lv_stmt, '#MESSAGE#', get_row.assertion_message); gt_relations(i).stmt := lv_stmt; i := (i + 1); end loop get_loop; if (get_cur%ISOPEN) then close get_cur; end if; end; end; exception when others then raise_application_error(-20000, lv_scope || sqlerrm); end pop_drmies; ------------------------------------------------------------------ -- INIT_DRM ------------------------------------------------------------------ procedure init_drm(fv_object in varchar2) is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DRM.INIT_DRM' || chr(10); begin if (not pkg_nkw_sys.is_table(fv_object)) then raise_application_error(-20000, lv_scope || 'Table or View ' || upper(fv_object) || ' does not exists.|'); end if; if (upper(nvl(fv_object,'-')) <> nvl(gv_lru_object,'+')) then gv_object := upper(fv_object); pop_columns; pop_drmies; gv_lru_object := upper(fv_object); end if; exception when others then raise; end init_drm; ------------------------------------------------------------------ -- BIND_COLUMN ------------------------------------------------------------------ procedure bind_column(fv_column in varchar2, fv_value in date) is begin bind_datatype(pv_column => fv_column, pv_data_type => 'DATE', pv_date_value => fv_value); exception when others then raise; end bind_column; ------------------------------------------------------------------ procedure bind_column(fv_column in varchar2, fv_value in number) is begin bind_datatype(pv_column => fv_column, pv_data_type => 'NUMBER', pv_number_value => fv_value); exception when others then raise; end bind_column; ------------------------------------------------------------------ procedure bind_column(fv_column in varchar2, fv_value in varchar2) is begin bind_datatype(pv_column => fv_column, pv_data_type => 'VARCHAR2', pv_varchar2_value => fv_value); exception when others then raise; end bind_column; ------------------------------------------------------------------ -- PARSE_DRM ------------------------------------------------------------------ procedure parse_drm(fv_action in varchar2) is i binary_integer; begin i := gt_relations.first; while (i is not null) loop if (upper(fv_action) = 'INSERT') then if (upper(gt_relations(i).dml_action) = 'INSERT') then log_drm(gt_relations(i).stmt); pkg_nkw_rpl.run_code(gt_relations(i).stmt); end if; elsif (upper(fv_action) = 'UPDATE') then if (upper(gt_relations(i).dml_action) = 'UPDATE') then log_drm(gt_relations(i).stmt); pkg_nkw_rpl.run_code(gt_relations(i).stmt); end if; elsif (upper(fv_action) = 'DELETE') then if (upper(gt_relations(i).dml_action) = 'DELETE') then log_drm(gt_relations(i).stmt); pkg_nkw_rpl.run_code(gt_relations(i).stmt); end if; end if; i := gt_relations.next(i); end loop; exception when others then raise; end parse_drm; ------------------------------------------------------------------ ------------------------------------------------------------------ -- GET_DATA ------------------------------------------------------------------ ------------------------------------------------------------------ function get_data return gt_data%TYPE is begin return gt_data; exception when others then raise; end get_data; end pkg_nkw_drm; / show errors