PKG_NKW_DRM.SPB
Source Package Body
Gerenciamento de relacionamentos.
create or replace package body pkg_nkw_drm
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_relations is record(dml_table nkw_drm.dml_table%TYPE,
relational_table nkw_drm.relational_table%TYPE,
dml_action nkw_drm.dml_action%TYPE,
raise_when nkw_drm.raise_when%TYPE,
assertion_message nkw_drm.assertion_message%TYPE,
text nkw_drm.text%TYPE,
stmt pkg_nkw_env.gv_plchar%TYPE);
type tab_relations is table of rec_relations index by binary_integer;
gt_relations tab_relations;
gv_lru_object sys.obj$.name%TYPE;
gv_stmt constant pkg_nkw_env.gv_plchar%TYPE :=
'begin ' || chr(10) ||
' declare cursor drm_cur is ' || chr(10) ||
' select 1 ' || chr(10) ||
' from #TABLE# ' || chr(10) ||
' #TEXT#;' || chr(10) ||
' bv_bit number(1); ' || chr(10) ||
' bv_found varchar2(10) := ''?''; ' || chr(10) ||
' bv_raisew varchar2(10) := ''#RAISE_WHEN#'';' || chr(10) ||
' bv_message pkg_nkw_env.gv_char%TYPE := ''#MESSAGE#.|'';' || chr(10) ||
' begin ' || chr(10) ||
' if (not drm_cur%ISOPEN) then ' || chr(10) ||
' open drm_cur; ' || chr(10) ||
' end if; ' || chr(10) ||
' fetch drm_cur into bv_bit; ' || chr(10) ||
' if (drm_cur%FOUND) then ' || chr(10) ||
' bv_found := ''FOUND''; ' || chr(10) ||
' elsif (drm_cur%NOTFOUND) then ' || chr(10) ||
' bv_found := ''NOT FOUND''; ' || chr(10) ||
' end if; ' || chr(10) ||
' if (drm_cur%ISOPEN) then ' || chr(10) ||
' close drm_cur; ' || chr(10) ||
' end if; ' || chr(10) ||
' if (bv_found = bv_raisew) then ' || chr(10) ||
' raise_application_error(-20000, bv_message); ' || chr(10) ||
' end if; ' || chr(10) ||
' end; ' || chr(10) ||
' exception ' || chr(10) ||
' when others then raise;' || chr(10) ||
'end;';
------------------------------------------------------------------
----------------------- Private Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- BIND_STMT
------------------------------------------------------------------
function bind_stmt(fv_text in pkg_nkw_env.gv_plchar%TYPE)
return pkg_nkw_env.gv_plchar%TYPE
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DRM.REPLACE_TEXT' || chr(10);
retval pkg_nkw_env.gv_plchar%TYPE := fv_text;
i binary_integer;
begin
if (retval is not null) then
i := pkg_nkw_drm.gt_data.first;
while (i is not null) loop
if (upper(pkg_nkw_drm.gt_data(i).data_type) = 'DATE') then
retval := replace(retval, ':drm.' || lower(pkg_nkw_drm.gt_data(i).column_name),
'pkg_nkw_drm.gt_data(' || to_char(i) || ').date_value');
elsif (upper(pkg_nkw_drm.gt_data(i).data_type) = 'NUMBER') then
retval := replace(retval, ':drm.' || lower(pkg_nkw_drm.gt_data(i).column_name),
'pkg_nkw_drm.gt_data(' || to_char(i) || ').number_value');
elsif (upper(pkg_nkw_drm.gt_data(i).data_type) = 'VARCHAR2') then
retval := replace(retval, ':drm.' || lower(pkg_nkw_drm.gt_data(i).column_name),
'pkg_nkw_drm.gt_data(' || to_char(i) || ').varchar2_value');
end if;
i := pkg_nkw_drm.gt_data.next(i);
end loop;
end if;
return retval;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end bind_stmt;
------------------------------------------------------------------
-- POP_COLUMNS
------------------------------------------------------------------
procedure pop_columns
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DRM.POP_COLUMNS' || chr(10);
begin
gt_data.delete;
gt_data := pkg_nkw_obj.get_columns(gv_object);
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end pop_columns;
------------------------------------------------------------------
-- CHECK_DRM
------------------------------------------------------------------
procedure check_drm
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DRM.CHECK_DRM' || chr(10);
begin
if (nvl(gt_data.count,0) = 0) then
raise_application_error(-20000, lv_scope || 'None DRM initialization.|');
end if;
end check_drm;
------------------------------------------------------------------
-- BIND_DATATYPE
------------------------------------------------------------------
procedure bind_datatype(pv_column in varchar2,
pv_data_type in varchar2,
pv_date_value in date := null,
pv_number_value in number := null,
pv_varchar2_value in varchar2 := null)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DRM.BIND_DATATYPE' || chr(10);
lv_col# sys.col$.col#%TYPE;
begin
check_drm;
if (pv_column is null) then
raise_application_error(-20000, lv_scope || 'Missing column name.|');
end if;
if (nvl(pkg_nkw_obj.index_of(gt_data, pv_column),0) = 0) then
raise_application_error(-20000, lv_scope ||
'Column ' || upper(gv_object) || '.' || upper(pv_column) || ' does not exists.|');
end if;
begin
lv_col# := pkg_nkw_obj.index_of(gt_data, pv_column);
if (upper(pv_data_type) = 'DATE') then
gt_data(lv_col#).date_value := pv_date_value;
elsif (upper(pv_data_type) = 'NUMBER') then
gt_data(lv_col#).number_value := pv_number_value;
elsif (upper(pv_data_type) = 'VARCHAR2') then
gt_data(lv_col#).varchar2_value := pv_varchar2_value;
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end;
exception
when others then raise;
end bind_datatype;
------------------------------------------------------------------
-- LOG_DRM
------------------------------------------------------------------
procedure log_drm(fv_buffer in pkg_nkw_env.gv_plchar%TYPE)
is
begin
pkg_nkw_hfs.fwrite(fv_buffer);
exception
when others then null;
end log_drm;
------------------------------------------------------------------
------------------------ Public Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- POP_DRMIES
------------------------------------------------------------------
procedure pop_drmies
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DRM.POP_DRMIES' || chr(10);
lv_text pkg_nkw_env.gv_plchar%TYPE;
lv_stmt pkg_nkw_env.gv_plchar%TYPE;
i binary_integer := 1;
begin
gt_relations.delete;
begin
declare cursor get_cur is
select a.dml_table, a.relational_table, a.relid, a.dml_action,
a.raise_when, a.assertion_message, a.text
from nkw_drm a
where (a.dml_table = upper(gv_object))
and (a.relid > 0)
order by a.relid;
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);
lv_stmt := gv_stmt;
gt_relations(i).dml_table := get_row.dml_table;
gt_relations(i).relational_table := get_row.relational_table;
gt_relations(i).dml_action := get_row.dml_action;
gt_relations(i).raise_when := get_row.raise_when;
gt_relations(i).assertion_message := get_row.assertion_message;
lv_text := bind_stmt(get_row.text);
gt_relations(i).text := lv_text;
lv_stmt := replace(lv_stmt, '#TABLE#', lower(get_row.relational_table));
lv_stmt := replace(lv_stmt, '#TEXT#', lv_text);
lv_stmt := replace(lv_stmt, '#RAISE_WHEN#', get_row.raise_when);
lv_stmt := replace(lv_stmt, '#MESSAGE#', get_row.assertion_message);
gt_relations(i).stmt := lv_stmt;
i := (i + 1);
end loop get_loop;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end pop_drmies;
------------------------------------------------------------------
-- INIT_DRM
------------------------------------------------------------------
procedure init_drm(fv_object in varchar2)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DRM.INIT_DRM' || chr(10);
begin
if (not pkg_nkw_sys.is_table(fv_object)) then
raise_application_error(-20000, lv_scope ||
'Table or View ' || upper(fv_object) || ' does not exists.|');
end if;
if (upper(nvl(fv_object,'-')) <> nvl(gv_lru_object,'+')) then
gv_object := upper(fv_object);
pop_columns;
pop_drmies;
gv_lru_object := upper(fv_object);
end if;
exception
when others then raise;
end init_drm;
------------------------------------------------------------------
-- BIND_COLUMN
------------------------------------------------------------------
procedure bind_column(fv_column in varchar2, fv_value in date)
is
begin
bind_datatype(pv_column => fv_column,
pv_data_type => 'DATE',
pv_date_value => fv_value);
exception
when others then raise;
end bind_column;
------------------------------------------------------------------
procedure bind_column(fv_column in varchar2, fv_value in number)
is
begin
bind_datatype(pv_column => fv_column,
pv_data_type => 'NUMBER',
pv_number_value => fv_value);
exception
when others then raise;
end bind_column;
------------------------------------------------------------------
procedure bind_column(fv_column in varchar2, fv_value in varchar2)
is
begin
bind_datatype(pv_column => fv_column,
pv_data_type => 'VARCHAR2',
pv_varchar2_value => fv_value);
exception
when others then raise;
end bind_column;
------------------------------------------------------------------
-- PARSE_DRM
------------------------------------------------------------------
procedure parse_drm(fv_action in varchar2)
is
i binary_integer;
begin
i := gt_relations.first;
while (i is not null) loop
if (upper(fv_action) = 'INSERT') then
if (upper(gt_relations(i).dml_action) = 'INSERT') then
log_drm(gt_relations(i).stmt);
pkg_nkw_rpl.run_code(gt_relations(i).stmt);
end if;
elsif (upper(fv_action) = 'UPDATE') then
if (upper(gt_relations(i).dml_action) = 'UPDATE') then
log_drm(gt_relations(i).stmt);
pkg_nkw_rpl.run_code(gt_relations(i).stmt);
end if;
elsif (upper(fv_action) = 'DELETE') then
if (upper(gt_relations(i).dml_action) = 'DELETE') then
log_drm(gt_relations(i).stmt);
pkg_nkw_rpl.run_code(gt_relations(i).stmt);
end if;
end if;
i := gt_relations.next(i);
end loop;
exception
when others then raise;
end parse_drm;
------------------------------------------------------------------
------------------------------------------------------------------
-- GET_DATA
------------------------------------------------------------------
------------------------------------------------------------------
function get_data return gt_data%TYPE
is
begin
return gt_data;
exception
when others then raise;
end get_data;
end pkg_nkw_drm;
|
