create or replace package body pkg_nkw_dnt_dvies 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_dvla is record(insert_flag nkw_dnt_dvies.insert_flag%TYPE, update_flag nkw_dnt_dvies.update_flag%TYPE, anvl nkw_dnt_dvies.anvl%TYPE, text pkg_nkw_env.gv_char%TYPE); type tab_dvla is table of rec_dvla index by binary_integer; gt_dvla tab_dvla; type rec_dvda is record(insert_flag nkw_dnt_dvies.insert_flag%TYPE, update_flag nkw_dnt_dvies.update_flag%TYPE, anvl nkw_dnt_dvies.anvl%TYPE, pkg_function nkw_dnt_dvies.pkg_function%TYPE, text pkg_nkw_env.gv_plchar%TYPE); type tab_dvda is table of rec_dvda index by binary_integer; gt_dvda tab_dvda; gv_object varchar2(30); gv_stdcur integer; gv_execur integer; gv_stmt pkg_nkw_env.gv_plchar%TYPE; ------------------------------------------------------------------ ----------------------- Private Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- POP_DVIES ------------------------------------------------------------------ procedure pop_dvies is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DNT_DVIES.POP_DVIES' || chr(10); lv_datatype varchar2(30); lv_inserting pkg_nkw_env.gv_plchar%TYPE; lv_updating pkg_nkw_env.gv_plchar%TYPE; i binary_integer; begin if (upper(nvl(gv_object,'+')) <> upper(nvl(pkg_nkw_dnt.gv_object,'-'))) then gt_dvla.delete; gt_dvda.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.dnt, a.insert_flag, a.update_flag, a.anvl, a.pkg_function, a.text from nkw_dnt_dvies 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; if (upper(get_row.dnt) = 'DVLA') then gt_dvla(i).insert_flag := get_row.insert_flag; gt_dvla(i).update_flag := get_row.update_flag; gt_dvla(i).anvl := get_row.anvl; gt_dvla(i).text := get_row.text; elsif (upper(get_row.dnt) = 'DVDA') then gt_dvda(i).insert_flag := get_row.insert_flag; gt_dvda(i).update_flag := get_row.update_flag; gt_dvda(i).anvl := get_row.anvl; gt_dvda(i).pkg_function := get_row.pkg_function; gt_dvda(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 i := gt_dvda.first; << dvda_loop >> while (i is not null) loop if (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'DATE') then lv_datatype := 'date_value'; elsif (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'NUMBER') then lv_datatype := 'number_value'; elsif (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'VARCHAR2') then lv_datatype := 'varchar2_value'; end if; if (upper(gt_dvda(i).insert_flag) = 'Y') then if (upper(gt_dvda(i).anvl) = 'A') then lv_inserting := lv_inserting || ' pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').' || lv_datatype || ' := ' || gt_dvda(i).pkg_function || '(' || gt_dvda(i).text || '); ' || chr(10); else lv_inserting := lv_inserting || ' pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').' || lv_datatype || ' := ' || 'nvl(pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').' || lv_datatype || ',' || gt_dvda(i).pkg_function || '(' || gt_dvda(i).text || ')); ' || chr(10); end if; end if; if (upper(gt_dvda(i).update_flag) = 'Y') then if (upper(gt_dvda(i).anvl) = 'A') then lv_updating := lv_updating || ' pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').' || lv_datatype || ' := ' || gt_dvda(i).pkg_function || '(' || gt_dvda(i).text || '); ' || chr(10); else lv_updating := lv_updating || ' pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').' || lv_datatype || ' := ' || 'nvl(pkg_nkw_dnt.gt_new_data(' || to_char(i) || ').' || lv_datatype || ',' || gt_dvda(i).pkg_function || '(' || gt_dvda(i).text || ')); ' || chr(10); end if; end if; i := gt_dvda.next(i); end loop dvda_loop; gv_stmt := gv_stmt || ' if (upper(pkg_nkw_dnt.gv_action) = ''INSERT'') then ' || chr(10); gv_stmt := gv_stmt || nvl(lv_inserting,' null;' || chr(10)); gv_stmt := gv_stmt || ' end if;' || chr(10); gv_stmt := gv_stmt || ' if (upper(pkg_nkw_dnt.gv_action) = ''UPDATE'') then ' || chr(10); gv_stmt := gv_stmt || nvl(lv_updating,' null;' || chr(10)); gv_stmt := gv_stmt || ' end if;' || chr(10); 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_dvies; ------------------------------------------------------------------ -- PARSE_DVLA ------------------------------------------------------------------ procedure parse_dvla is lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout || 'PKG_NKW_DNT_DVIES.PARSE_DVLA' || chr(10); lv_datatype varchar2(30); i binary_integer; begin begin if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then i := gt_dvla.first; << dvla_loop >> while (i is not null) loop if (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'DATE') then if (upper(gt_dvla(i).insert_flag) = 'Y') then if (upper(pkg_nkw_dnt.gv_action) = 'INSERT') then if (upper(gt_dvla(i).anvl) = 'A') then pkg_nkw_dnt.gt_new_data(i).date_value := to_date(gt_dvla(i).text, 'dd/mm/yyyy hh24:mi:ss'); else pkg_nkw_dnt.gt_new_data(i).date_value := nvl(pkg_nkw_dnt.gt_new_data(i).date_value, to_date(gt_dvla(i).text, 'dd/mm/yyyy hh24:mi:ss')); end if; end if; end if; if (upper(gt_dvla(i).update_flag) = 'Y') then if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then if (upper(gt_dvla(i).anvl) = 'A') then pkg_nkw_dnt.gt_new_data(i).date_value := to_date(gt_dvla(i).text, 'dd/mm/yyyy hh24:mi:ss'); else pkg_nkw_dnt.gt_new_data(i).date_value := nvl(pkg_nkw_dnt.gt_new_data(i).date_value, to_date(gt_dvla(i).text, 'dd/mm/yyyy hh24:mi:ss')); end if; end if; end if; elsif (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'NUMBER') then if (upper(gt_dvla(i).insert_flag) = 'Y') then if (upper(pkg_nkw_dnt.gv_action) = 'INSERT') then if (upper(gt_dvla(i).anvl) = 'A') then pkg_nkw_dnt.gt_new_data(i).number_value := to_number(gt_dvla(i).text); else pkg_nkw_dnt.gt_new_data(i).number_value := nvl(pkg_nkw_dnt.gt_new_data(i).number_value, to_number(gt_dvla(i).text)); end if; end if; end if; if (upper(gt_dvla(i).update_flag) = 'Y') then if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then if (upper(gt_dvla(i).anvl) = 'A') then pkg_nkw_dnt.gt_new_data(i).number_value := to_number(gt_dvla(i).text); else pkg_nkw_dnt.gt_new_data(i).number_value := nvl(pkg_nkw_dnt.gt_new_data(i).number_value, to_number(gt_dvla(i).text)); end if; end if; end if; elsif (upper(pkg_nkw_dnt.gt_new_data(i).data_type) = 'VARCHAR2') then if (upper(gt_dvla(i).insert_flag) = 'Y') then if (upper(pkg_nkw_dnt.gv_action) = 'INSERT') then if (upper(gt_dvla(i).anvl) = 'A') then pkg_nkw_dnt.gt_new_data(i).varchar2_value := gt_dvla(i).text; else pkg_nkw_dnt.gt_new_data(i).varchar2_value := nvl(pkg_nkw_dnt.gt_new_data(i).varchar2_value, gt_dvla(i).text); end if; end if; end if; if (upper(gt_dvla(i).update_flag) = 'Y') then if (upper(pkg_nkw_dnt.gv_action) = 'UPDATE') then if (upper(gt_dvla(i).anvl) = 'A') then pkg_nkw_dnt.gt_new_data(i).varchar2_value := gt_dvla(i).text; else pkg_nkw_dnt.gt_new_data(i).varchar2_value := nvl(pkg_nkw_dnt.gt_new_data(i).varchar2_value, gt_dvla(i).text); end if; end if; end if; end if; i := gt_dvla.next(i); end loop dvla_loop; end if; end; exception when others then raise_application_error(-20000, lv_scope || sqlerrm); end parse_dvla; ------------------------------------------------------------------ -- LOG_DVIES ------------------------------------------------------------------ procedure log_dvies(fv_stmt in gv_stmt%TYPE) is lv_path pkg_nkw_hfs.gv_path%TYPE := '/home/oracle/utlfile'; lv_file pkg_nkw_hfs.gv_file%TYPE := lower(user) || '.' || 'dvies'; lv_data pkg_nkw_env.gv_plchar%TYPE; begin pkg_nkw_hfs.fwrite(fv_buffer => pkg_nkw_dnt.gv_label || fv_stmt, fv_path => lv_path, fv_file => lv_file); exception when others then null; end log_dvies; ------------------------------------------------------------------ -- PARSE_DVDA ------------------------------------------------------------------ procedure parse_dvda is i binary_integer; begin if (upper(pkg_nkw_dnt.gv_action) in ('INSERT','UPDATE')) then if (gv_stmt is not null) then log_dvies('begin' || chr(10) || gv_stmt || 'end;'); 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' || 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_dvda; ------------------------------------------------------------------ ------------------------ Public Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- PARSE_ACTION ------------------------------------------------------------------ procedure parse_action is begin pop_dvies; parse_dvla; parse_dvda; exception when others then raise; end parse_action; end pkg_nkw_dnt_dvies; / show errors