PKG_NKW_DNT_EPPC.SPB
Source Package Body
Dynamic Trigger - Execução de procedimentos em pacotes
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;
|
