create or replace package body pkg_nkw_dnt_ccvies 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_ccvies is record(app_message nkw_dnt_ccvies.app_message%TYPE, pkg_function nkw_dnt_ccvies.pkg_function%TYPE, text nkw_dnt_ccvies.text%TYPE); type tab_ccvies is table of rec_ccvies index by binary_integer; gt_ccvi tab_ccvies; gt_ccvs tab_ccvies; gt_ccvr tab_ccvies; gt_ccvc tab_ccvies; gv_object varchar2(30); gv_stdcur integer; gv_execur integer; gv_stmt pkg_nkw_env.gv_plchar%TYPE; gv_work boolean := (FALSE); ------------------------------------------------------------------ ----------------------- Private Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- POP_CCVIES ------------------------------------------------------------------ procedure pop_ccvies is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DNT_CCVIES.POP_CCVIES' || chr(10); i binary_integer; begin if (upper(nvl(gv_object,'+')) <> upper(nvl(pkg_nkw_dnt.gv_object,'-'))) then gt_ccvi.delete; gt_ccvs.delete; gt_ccvr.delete; gt_ccvc.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; gv_work := (FALSE); begin declare cursor get_cur is select a.column_id, a.dnt, a.app_message, a.pkg_function, a.text from nkw_dnt_ccvies 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); gv_work := (TRUE); i := get_row.column_id; if (upper(get_row.dnt) = 'CCVI') then gt_ccvi(i).app_message := get_row.app_message || '.|'; elsif (upper(get_row.dnt) = 'CCVS') then gt_ccvs(i).app_message := get_row.app_message || '.|'; gt_ccvs(i).pkg_function := get_row.pkg_function; gt_ccvs(i).text := get_row.text; elsif (upper(get_row.dnt) = 'CCVR') then gt_ccvr(i).app_message := get_row.app_message || '.|'; gt_ccvr(i).text := get_row.text; elsif (upper(get_row.dnt) = 'CCVC') then gt_ccvc(i).app_message := get_row.app_message || '.|'; gt_ccvc(i).pkg_function := get_row.pkg_function; gt_ccvc(i).text := get_row.text; end if; 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 gv_stmt := gv_stmt || ' -- Standards assertions' || chr(10); i := gt_ccvs.first; << ccvs_loop >> while (i is not null) loop if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then if (gt_ccvs(i).pkg_function is not null) then gv_stmt := gv_stmt || ' if (not ' || lower(gt_ccvs(i).pkg_function) || '(' || gt_ccvs(i).text || ')) then' || chr(10) || ' raise_application_error(-20000, ''' || gt_ccvs(i).app_message || ''');' || chr(10) || ' end if;' || chr(10); end if; end if; i := gt_ccvs.next(i); end loop ccvc_loop; end; begin gv_stmt := gv_stmt || ' -- Range assertions' || chr(10); i := gt_ccvr.first; << ccvr_loop >> while (i is not null) loop if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then if (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'DATE') then gv_stmt := gv_stmt || ' if (not pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').date_value ' || gt_ccvr(i).text || ') then ' || chr(10) || ' raise_application_error(-20000, ''' || gt_ccvr(i).app_message || ''');' || chr(10) || ' end if;' || chr(10); end if; if (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'NUMBER') then gv_stmt := gv_stmt || ' if (not pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').number_value ' || gt_ccvr(i).text || ') then ' || chr(10) || ' raise_application_error(-20000, ''' || gt_ccvr(i).app_message || ''');' || chr(10) || ' end if;' || chr(10); end if; if (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'VARCHAR2') then gv_stmt := gv_stmt || ' if (not pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').varchar2_value ' || gt_ccvr(i).text || ') then ' || chr(10) || ' raise_application_error(-20000, ''' || gt_ccvr(i).app_message || ''');' || chr(10) || ' end if;' || chr(10); end if; end if; i := gt_ccvr.next(i); end loop ccvr_loop; end; begin gv_stmt := gv_stmt || ' -- Consistency assertions' || chr(10); i := gt_ccvc.first; << ccvc_loop >> while (i is not null) loop if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then if (gt_ccvc(i).pkg_function is not null) then gv_stmt := gv_stmt || ' if (not ' || lower(gt_ccvc(i).pkg_function) || '(' || gt_ccvc(i).text || ')) then' || chr(10) || ' raise_application_error(-20000, ''' || gt_ccvc(i).app_message || ''');' || chr(10) || ' end if;' || chr(10); end if; end if; i := gt_ccvc.next(i); end loop ccvc_loop; end; pkg_nkw_dnt_std.build_stmt(pkg_nkw_dnt.gt_data, gv_stmt); gv_object := upper(pkg_nkw_dnt.gv_object); end if; exception when others then gv_object := null; raise_application_error(-20000, lv_scope || sqlerrm); end pop_ccvies; ------------------------------------------------------------------ -- LOG_CCVIES ------------------------------------------------------------------ procedure log_ccvies is lv_path pkg_nkw_hfs.gv_path%TYPE := '/home/oracle/utlfile'; lv_file pkg_nkw_hfs.gv_file%TYPE := lower(user) || '.' || 'ccvies'; lv_data pkg_nkw_env.gv_plchar%TYPE; begin pkg_nkw_obj.contents_of(pkg_nkw_dnt.gt_new_data, lv_data); pkg_nkw_hfs.fwrite(fv_buffer => lv_data, fv_file => lv_file, fv_path => lv_path); pkg_nkw_hfs.fappend(fv_buffer => 'Action: ' || pkg_nkw_dnt.gv_action, fv_file => lv_file, fv_path => lv_path); pkg_nkw_hfs.fappend(fv_buffer => 'PL/SQL Block: <<<' || chr(10) || 'begin' || chr(10) || gv_stmt || 'end;' || chr(10) || '>>>', fv_file => lv_file, fv_path => lv_path); exception when others then null; end log_ccvies; ------------------------------------------------------------------ ------------------------ Public Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- PARSE_CCVI ------------------------------------------------------------------ procedure parse_ccvi is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DNT_CCVIES.PARSE_CCVI' || chr(10); i binary_integer; begin begin if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then i := gt_ccvi.first; << ccvi_loop >> while (i is not null) loop if (upper(pkg_nkw_dnt.gt_old_data(i).data_type) = 'DATE') then if (pkg_nkw_dnt.gt_old_data(i).date_value <> pkg_nkw_dnt.gt_new_data(i).date_value) then raise_application_error(-20000, gt_ccvi(i).app_message); end if; elsif (upper(pkg_nkw_dnt.gt_old_data(i).data_type) = 'NUMBER') then if (pkg_nkw_dnt.gt_old_data(i).number_value <> pkg_nkw_dnt.gt_new_data(i).number_value) then raise_application_error(-20000, gt_ccvi(i).app_message); end if; elsif (upper(pkg_nkw_dnt.gt_old_data(i).data_type) = 'VARCHAR2') then if (pkg_nkw_dnt.gt_old_data(i).varchar2_value <> pkg_nkw_dnt.gt_new_data(i).varchar2_value) then raise_application_error(-20000, gt_ccvi(i).app_message); end if; end if; i := gt_ccvi.next(i); end loop ccvi_loop; end if; end; exception when others then raise_application_error(-20000, lv_scope || sqlerrm); end parse_ccvi; ------------------------------------------------------------------ -- PARSE_CCVIES ------------------------------------------------------------------ procedure parse_ccvies is i binary_integer; begin if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then if (gv_work) then log_ccvies; 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, 'begin null;' || chr(10) || gv_stmt || ' end;', dbms_sql.native); gv_execur := dbms_sql.execute(gv_stdcur); end if; 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; end parse_ccvies; ------------------------------------------------------------------ -- PARSE_ACTION ------------------------------------------------------------------ procedure parse_action is begin pop_ccvies; parse_ccvi; parse_ccvies; exception when others then raise; end parse_action; end pkg_nkw_dnt_ccvies; / show errors