PKG_NKW_SYS.SPB
Source Package Body
Acesso direto ao dicionário de dados.
create or replace package body pkg_nkw_sys
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 --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- LOAD_OBJECTS_TYPES
------------------------------------------------------------------
procedure load_objects_types
is
begin
if (gt_type.exists(gt_type.first)) then
return;
end if;
gt_type(1) := 'INDEX';
gt_type(2) := 'TABLE';
gt_type(3) := 'CLUSTER';
gt_type(4) := 'VIEW';
gt_type(5) := 'SYNONYM';
gt_type(6) := 'SEQUENCE';
gt_type(7) := 'PROCEDURE';
gt_type(8) := 'FUNCTION';
gt_type(9) := 'PACKAGE';
gt_type(10) := '';
gt_type(11) := 'PACKAGE BODY';
gt_type(12) := 'TRIGGER';
exception
when others then raise;
end load_objects_types;
------------------------------------------------------------------
-- LOAD_USERS
------------------------------------------------------------------
procedure load_users
is
begin
if (gt_users.exists(gt_users.first)) then
return;
end if;
begin
declare cursor get_cur is
select a.user#, a.name
from sys.user$ a
where (a.user# = 1 or
a.user# > get_nkid)
and (upper(a.name) not like '%NEXT%')
order by a.user#;
get_row get_cur%ROWTYPE;
i binary_integer := 1;
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);
begin
i := get_cur%ROWCOUNT;
gt_users(i).user# := get_row.user#;
gt_users(i).name := get_row.name;
end;
end loop;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
exception
when others then raise;
end load_users;
------------------------------------------------------------------
------------------------ Public Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- GET_NKID
------------------------------------------------------------------
function get_nkid
return sys.user$.user#%TYPE
is
retval sys.user$.user#%TYPE;
begin
retval := get_schemaid('NK');
return retval;
exception
when others then raise;
end get_nkid;
------------------------------------------------------------------
-- GET_USERS
------------------------------------------------------------------
procedure get_users(ft_users in out gt_users%TYPE)
is
begin
load_users;
ft_users := gt_users;
exception
when others then raise;
end get_users;
------------------------------------------------------------------
-- GET_SCHEMAID
------------------------------------------------------------------
function get_schemaid(fv_user in sys.user$.user#%TYPE)
return sys.user$.name%TYPE
is
retval sys.user$.name%TYPE;
begin
begin
declare cursor get_cur is
select a.name
from sys.user$ a
where (a.user# = upper(fv_user));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retval;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end get_schemaid;
------------------------------------------------------------------
function get_schemaid(fv_user in sys.user$.name%TYPE)
return sys.user$.user#%TYPE
is
retval sys.user$.user#%TYPE;
begin
begin
declare cursor get_cur is
select a.user#
from sys.user$ a
where (a.name = upper(fv_user));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retval;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end get_schemaid;
------------------------------------------------------------------
-- GET_OBJECT_ID
------------------------------------------------------------------
function get_object_id(fv_object in sys.obj$.obj#%TYPE)
return sys.obj$.name%TYPE
is
retval sys.obj$.name%TYPE;
begin
begin
declare cursor get_cur is
select a.name
from sys.obj$ a
where (a.obj# = fv_object)
and (a.type# in (2,4,6,7,8,9,11));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retval;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end get_object_id;
------------------------------------------------------------------
function get_object_id(fv_object in sys.obj$.name%TYPE)
return sys.obj$.obj#%TYPE
is
retval sys.obj$.obj#%TYPE;
begin
begin
declare cursor get_cur is
select a.obj#
from sys.obj$ a
where (a.name = upper(fv_object))
and (a.type# in (2,4,6,7,8,9,11));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retval;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end get_object_id;
------------------------------------------------------------------
-- GET_OBJECT_TYPE
------------------------------------------------------------------
function get_object_type(fv_object in sys.obj$.obj#%TYPE)
return sys.obj$.name%TYPE
is
retval sys.obj$.name%TYPE;
begin
load_objects_types;
retval := gt_type(fv_object);
return retval;
exception
when others then raise;
end get_object_type;
------------------------------------------------------------------
function get_object_type(fv_object in sys.obj$.name%TYPE)
return sys.obj$.obj#%TYPE
is
retval sys.obj$.obj#%TYPE;
i binary_integer;
begin
load_objects_types;
i := gt_type.first;
while (i is not null) loop
if (gt_type(i) = upper(fv_object)) then
retval := i;
exit;
end if;
i := gt_type.next(i);
end loop;
return retval;
exception
when others then raise;
end get_object_type;
------------------------------------------------------------------
-- GET_COLUMN_ID
------------------------------------------------------------------
function get_column_id(fv_object in sys.obj$.name%TYPE,
fv_column in sys.col$.col#%TYPE)
return sys.col$.name%TYPE
is
retval sys.col$.name%TYPE;
lv_obj# sys.col$.obj#%TYPE := get_object_id(fv_object);
begin
begin
declare cursor get_cur is
select a.name
from sys.col$ a
where (a.obj# = lv_obj#)
and (a.col# = fv_column);
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retval;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end get_column_id;
------------------------------------------------------------------
function get_column_id(fv_object in sys.obj$.name%TYPE,
fv_column in sys.col$.name%TYPE)
return sys.col$.col#%TYPE
is
retval sys.col$.col#%TYPE;
lv_obj# sys.col$.obj#%TYPE := get_object_id(fv_object);
begin
begin
declare cursor get_cur is
select a.col#
from sys.col$ a
where (a.obj# = lv_obj#)
and (a.name = upper(fv_column));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into retval;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end get_column_id;
------------------------------------------------------------------
-- IS_TABLE
------------------------------------------------------------------
function is_table(fv_object in sys.obj$.name%TYPE)
return boolean
is
retval boolean := (FALSE);
lv_bit number(1);
begin
begin
declare cursor get_cur is
select 1
from sys.obj$ a
where (a.name = upper(fv_object))
and (a.type# in (2,4));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into lv_bit;
if (get_cur%FOUND) then
retval := (TRUE);
end if;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end is_table;
------------------------------------------------------------------
-- IS_COLUMN
------------------------------------------------------------------
function is_column(fv_object in sys.obj$.name%TYPE,
fv_column in sys.col$.name%TYPE)
return boolean
is
retval boolean := (FALSE);
lv_bit number(1);
lv_obj# sys.col$.obj#%TYPE := get_object_id(fv_object);
begin
begin
declare cursor get_cur is
select 1
from sys.col$ a
where (a.obj# = lv_obj#)
and (a.name = upper(fv_column));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into lv_bit;
if (get_cur%FOUND) then
retval := (TRUE);
end if;
if (get_cur%ISOPEN) then
close get_cur;
end if;
end;
end;
return retval;
exception
when others then raise;
end is_column;
end pkg_nkw_sys;
|
