PKG_NKW_DNT_CCVIES.SPB
Source Package Body
Dynamic Trigger - Checagem de valores de colunas
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;
|
