PKG_NKW_OBJ.SPB
Source Package Body
Estrutura de Objetos.
create or replace package body pkg_nkw_obj
timestamp '2006-07-07:10:10:10'
is
------------------------------------------------------------------
-- 2006 DataPRO Developers - m@urelio
------------------------------------------------------------------
-- Version: 2.0.2
------------------------------------------------------------------
-- Collections, Records, Variables, Constants, Exceptions, Cursors
------------------------------------------------------------------
------------------------------------------------------------------
----------------------- Private Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
------------------------ Public Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- GET_SEQUENCE
------------------------------------------------------------------
function get_sequence(fv_name in varchar2)
return integer
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_OBJ.GET_SEQUENCE' || chr(10);
lv_cur integer;
lv_exec integer;
lv_int integer;
begin
lv_cur := dbms_sql.open_cursor;
dbms_sql.parse(lv_cur, 'select ' || fv_name || '.nextval from dual', dbms_sql.native);
lv_exec := dbms_sql.execute(lv_cur);
dbms_sql.define_column(lv_cur, 1, lv_int);
if (dbms_sql.fetch_rows(lv_cur) > 0) then
dbms_sql.column_value(lv_cur, 1, lv_int);
end if;
if (dbms_sql.is_open(lv_cur)) then
dbms_sql.close_cursor(lv_cur);
end if;
return lv_int;
exception
when others then
if (dbms_sql.is_open(lv_cur)) then
dbms_sql.close_cursor(lv_cur);
end if;
raise_application_error(-20000, lv_scope || sqlerrm);
end get_sequence;
------------------------------------------------------------------
-- GET_COLUMNS
------------------------------------------------------------------
function get_columns(fv_table in varchar2)
return gt_data%TYPE
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := 'PKG_NKW_OBJ.GET_COLUMNS';
rettab gt_data%TYPE;
lv_obj# sys.col$.obj#%TYPE;
i binary_integer;
begin
if (fv_table is not null) then
lv_obj# := pkg_nkw_sys.get_object_id(fv_table);
end if;
if (lv_obj# is not null) then
begin
declare cursor get_cur is
select a.col# column_id, a.name column_name, a.type# data_type, a.length
from sys.col$ a
where (a.obj# = lv_obj#)
order by a.col#;
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;
rettab(i).table_name := upper(fv_table);
rettab(i).column_id := i;
rettab(i).column_name := lower(get_row.column_name);
if (get_row.data_type = 12) then
rettab(i).data_type := 'DATE';
elsif (get_row.data_type = 2) then
rettab(i).data_type := 'NUMBER';
elsif (get_row.data_type = 1) then
rettab(i).data_type := 'VARCHAR2';
else
rettab(i).data_type := 'UNDEFINED';
end if;
rettab(i).data_length := lower(get_row.length);
end loop get_loop;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
end if;
return rettab;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end get_columns;
------------------------------------------------------------------
-- INDEX_OF
------------------------------------------------------------------
function index_of(ft_table in gt_data%TYPE,
fv_column in varchar2)
return binary_integer
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_OBJ.INDEX_OF' || chr(10);
retval binary_integer := 0;
i binary_integer;
begin
if (ft_table.count > 0) then
begin
i := ft_table.first;
<< index_loop >>
while (i is not null) loop
if (lower(ft_table(i).column_name) = lower(fv_column)) then
retval := i;
exit index_loop;
end if;
i := ft_table.next(i);
end loop index_loop;
end;
end if;
return retval;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end index_of;
------------------------------------------------------------------
-- CONTENTS_OF
------------------------------------------------------------------
procedure contents_of(ft_data in gt_data%TYPE,
fv_data out varchar2)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_OBJ.CONTENTS_OF' || chr(10);
retval pkg_nkw_env.gv_plchar%TYPE;
i binary_integer;
lv_pad integer := 28;
begin
i := ft_data.first;
if (i is not null) then
retval := 'Object Name: ' || upper(ft_data(i).table_name) || chr(10) ||
'(i) Column Name Type Value' || chr(10) ||
'------------------------------ --------- ---------------------------------------' || chr(10);
end if;
<< index_loop >>
while (i is not null) loop
if (ft_data(i).data_type = 'DATE') then
retval := retval || '(' || to_char(i) || ') ' ||
rpad(upper(ft_data(i).column_name), (lv_pad - length(i))) || 'DATE ' ||
to_char(ft_data(i).date_value,'dd/mm/yyyy hh24:mi:ss') || chr(10);
elsif (ft_data(i).data_type = 'NUMBER') then
retval := retval || '(' || to_char(i) || ') ' ||
rpad(upper(ft_data(i).column_name), (lv_pad - length(i))) || 'NUMBER ' ||
to_char(ft_data(i).number_value) || chr(10);
elsif (ft_data(i).data_type = 'VARCHAR2') then
retval := retval || '(' || to_char(i) || ') ' ||
rpad(upper(ft_data(i).column_name), (lv_pad - length(i))) || 'VARCHAR2 ' ||
replace(ft_data(i).varchar2_value, chr(10), chr(10) || rpad(' ',41,' ')) || chr(10);
else
retval := retval || '(' || to_char(i) || ') ' ||
rpad(upper(ft_data(i).column_name), (lv_pad - length(i))) || 'UNDEFINED ' || chr(10);
end if;
i := ft_data.next(i);
end loop index_loop;
fv_data := retval;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end contents_of;
end pkg_nkw_obj;
|
