PKG_NKW_DNT_DVIES.SPB
Source Package Body
Dynamic Trigger - Assinalação de valores padrões
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;
|
