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