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; / show errors