create or replace package body pkg_nkw_dnt_eppc 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_eppc is record(insert_flag nkw_dnt_eppc.insert_flag%TYPE, update_flag nkw_dnt_eppc.update_flag%TYPE, delete_flag nkw_dnt_eppc.delete_flag%TYPE, runif nkw_dnt_eppc.runif%TYPE, pkg_procedure nkw_dnt_eppc.pkg_procedure%TYPE, pkg_arguments nkw_dnt_eppc.pkg_arguments%TYPE); type tab_eppc is table of rec_eppc index by binary_integer; gt_eppc tab_eppc; gv_object varchar2(30); gv_stdcur integer; gv_execur integer; gv_stmt pkg_nkw_env.gv_plchar%TYPE; ------------------------------------------------------------------ ----------------------- Private Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- POP_EPPC ------------------------------------------------------------------ procedure pop_eppc is lv_datatype varchar2(30); i binary_integer; lv_inserting pkg_nkw_env.gv_plchar%TYPE; lv_updating pkg_nkw_env.gv_plchar%TYPE; lv_deleting pkg_nkw_env.gv_plchar%TYPE; begin if (upper(nvl(gv_object,'+')) <> upper(nvl(pkg_nkw_dnt.gv_object,'-'))) then gt_eppc.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.pkgid, a.insert_flag, a.update_flag, a.delete_flag, a.pkg_procedure, a.pkg_arguments, a.runif from nkw_dnt_eppc 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.pkgid; gt_eppc(i).insert_flag := get_row.insert_flag; gt_eppc(i).update_flag := get_row.update_flag; gt_eppc(i).delete_flag := get_row.delete_flag; gt_eppc(i).runif := get_row.runif; gt_eppc(i).pkg_procedure := lower(get_row.pkg_procedure); gt_eppc(i).pkg_arguments := get_row.pkg_arguments; 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_eppc.first; << eppc_loop >> while (i is not null) loop if (upper(gt_eppc(i).insert_flag) = 'Y') then lv_inserting := lv_inserting || ' if (' || gt_eppc(i).runif || ') then' || chr(10) || ' ' || gt_eppc(i).pkg_procedure || '(' || gt_eppc(i).pkg_arguments || ');' || chr(10) || ' end if;' || chr(10); lv_inserting := replace(lv_inserting, ',' || chr(10), ',' || chr(10) || rpad(' ',length(gt_eppc(i).pkg_procedure) + 10,' ')); elsif (upper(gt_eppc(i).update_flag) = 'Y') then lv_updating := lv_updating || ' if (' || gt_eppc(i).runif || ') then' || chr(10) || ' ' || gt_eppc(i).pkg_procedure || '(' || gt_eppc(i).pkg_arguments || ');' || chr(10) || ' end if;' || chr(10); lv_updating := replace(lv_updating, ',' || chr(10), ',' || chr(10) || rpad(' ',length(gt_eppc(i).pkg_procedure) + 10,' ')); elsif (upper(gt_eppc(i).delete_flag) = 'Y') then lv_deleting := lv_deleting || ' if (' || gt_eppc(i).runif || ') then' || chr(10) || ' ' || gt_eppc(i).pkg_procedure || '(' || gt_eppc(i).pkg_arguments || ');' || chr(10) || ' end if;' || chr(10); lv_deleting := replace(lv_deleting, ',' || chr(10), ',' || chr(10) || rpad(' ',length(gt_eppc(i).pkg_procedure) + 10,' ')); end if; i := gt_eppc.next(i); end loop eppc_loop; gv_stmt := 'begin' || chr(10) || ' if (upper(pkg_nkw_dnt.gv_action) = ''INSERT'') then' || chr(10) || nvl(lv_inserting, ' null;' || chr(10)) || ' end if;' || chr(10) || ' if (upper(pkg_nkw_dnt.gv_action) = ''UPDATE'') then' || chr(10) || nvl(lv_updating, ' null;' || chr(10)) || ' end if;' || chr(10) || ' if (upper(pkg_nkw_dnt.gv_action) = ''DELETE'') then' || chr(10) || nvl(lv_deleting, ' null;' || chr(10)) || ' end if;' || chr(10) || 'end;' || 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; end pop_eppc; ------------------------------------------------------------------ -- LOG_EPPC ------------------------------------------------------------------ procedure log_eppc(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) || '.' || 'eppc'; 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_eppc; ------------------------------------------------------------------ -- PARSE_EPPC ------------------------------------------------------------------ procedure parse_eppc is i binary_integer; begin if (gv_stmt is not null) then log_eppc(gv_stmt); 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; end parse_eppc; ------------------------------------------------------------------ ------------------------ Public Section -------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ -- PARSE_ACTION ------------------------------------------------------------------ procedure parse_action is begin pop_eppc; parse_eppc; exception when others then raise; end parse_action; end pkg_nkw_dnt_eppc; / show errors