create or replace package body pkg_nkw_cmm
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 --------------------------
   ------------------------------------------------------------------
   ------------------------------------------------------------------
   -- SET_COMMENTS
   ------------------------------------------------------------------
   procedure set_comments(ft_tabs in gt_tabs%TYPE)
   is
      i  binary_integer;
   begin
      begin
         i := ft_tabs.first;
         << tabs_loop >>
         while (i is not null) loop
            begin
               pkg_nkw_rpl.run_code('lock table ' || ft_tabs(i).object_name || ' in exclusive mode nowait');
               pkg_nkw_rpl.run_code('comment on table ' || ft_tabs(i).object_name ||
                                    ' is ''' || replace(ft_tabs(i).comments,'''','''''') || '''');
               exception
                  when pkg_nkw_exc.obj_locked then
                     raise_application_error(-20000,'Object is currently locked.|');
                  when pkg_nkw_exc.no_object then
                     raise_application_error(-20000,'Object ' || ft_tabs(i).object_name || ' does not exists.|');
                  when others then
                     raise_application_error(-20000,'Where''s Nulaya ?' || chr(10) ||
                                                    'PKG_NKW_CMM.SET_COMMENTS' || chr(10) || sqlerrm);
            end;
            i := ft_tabs.next(i);
         end loop tabs_loop;
      end;
      exception
         when others then raise;
   end set_comments;
   ------------------------------------------------------------------
   procedure set_comments(ft_cols in gt_cols%TYPE)
   is
      i  binary_integer;
   begin
      begin
         i := ft_cols.first;
         << cols_loop >>
         while (i is not null) loop
            begin
               pkg_nkw_rpl.run_code('lock table ' || ft_cols(i).object_name || ' in exclusive mode nowait');
               pkg_nkw_rpl.run_code('comment on column ' || ft_cols(i).object_name || '.' || ft_cols(i).column_name ||
                                    ' is ''' || replace(ft_cols(i).comments,'''','''''') || '''');
               exception
                  when pkg_nkw_exc.obj_locked then
                     raise_application_error(-20000,'Object is currently locked.|');
                  when pkg_nkw_exc.no_object then
                     raise_application_error(-20000,'Object ' || ft_cols(i).object_name || ' does not exists.|');
                  when pkg_nkw_exc.no_column then
                     raise_application_error(-20000,'Invalid column name.|');
                  when others then
                     raise_application_error(-20000,'Where''s Nulaya ?' || chr(10) ||
                                                    'PKG_NKW_CMM.SET_COMMENTS' || chr(10) || sqlerrm);
            end;
            i := ft_cols.next(i);
         end loop cols_loop;
      end;
      exception
         when others then raise;
   end set_comments;
   ------------------------------------------------------------------
   procedure set_comments(fv_object   in pkg_nkw_env.gv_char%TYPE,
                          fv_comments in pkg_nkw_env.gv_char%TYPE)
   is
   begin
      if (fv_object is not null and fv_comments is not null) then
         begin
            pkg_nkw_rpl.run_code('lock table ' || fv_object || ' in exclusive mode nowait');
            pkg_nkw_rpl.run_code('comment on table ' || fv_object || ' is ''' || replace(fv_comments,'''','''''') || '''');
            exception
               when pkg_nkw_exc.obj_locked then
                  raise_application_error(-20000,'Object is currently locked.|');
               when pkg_nkw_exc.no_object then
                  raise_application_error(-20000,'Object ' || upper(fv_object) || ' does not exists.|');
               when others then
                  raise_application_error(-20000,'Where''s Nulaya ?' || chr(10) ||
                                                 'PKG_NKW_CMM.SET_COMMENTS' || chr(10) || sqlerrm);
         end;
      end if;
      exception
         when others then raise;
   end set_comments;
   --------------------------------------------------------------------
   procedure set_comments(fv_object   in pkg_nkw_env.gv_char%TYPE,
                          fv_column   in pkg_nkw_env.gv_char%TYPE,
                          fv_comments in pkg_nkw_env.gv_char%TYPE)
   is
   begin
      if (fv_object is not null and fv_column is not null and fv_comments is not null) then
         begin
            pkg_nkw_rpl.run_code('lock table ' || fv_object || ' in exclusive mode nowait');
            pkg_nkw_rpl.run_code('comment on column ' || fv_object || '.' || fv_column ||
                                 ' is ''' || replace(fv_comments,'''','''''') || '''');
            exception
               when pkg_nkw_exc.obj_locked then
                  raise_application_error(-20000,'Object is currently locked.|');
               when pkg_nkw_exc.no_object then
                  raise_application_error(-20000,'Object ' || upper(fv_object) || ' does not exists.|');
               when pkg_nkw_exc.no_column then
                  raise_application_error(-20000,'Invalid column name.|');
               when others then
                  raise_application_error(-20000,'Where''s Nulaya ?' || chr(10) ||
                                                 'PKG_NKW_CMM.SET_COMMENTS' || chr(10) || sqlerrm);
         end;
      end if;
      exception
         when others then raise;
   end set_comments;
   ------------------------------------------------------------------
   -- GET_TAB_CMM
   ------------------------------------------------------------------
   procedure get_tab_cmm
   is
   begin
      begin
         declare cursor get_cur is
                 select a.name object_name, b.comment$
                   from sys.com$ b, sys.obj$ a
                  where (a.owner# = pkg_nkw_sys.get_nkid)
                    and (a.type# in (2,4))
                    and (a.obj# = b.obj#)
                    and (b.col# is null);
                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);
               begin
                  begin
                     update nkw_tab_cmm
                        set comments = get_row.comment$
                      where (object_name = get_row.object_name);
                     exception
                        when others then raise;
                  end;
                  if (SQL%ROWCOUNT = 0) then
                     begin
                        insert into nkw_tab_cmm(object_name, comments)
                        values(get_row.object_name, get_row.comment$);
                        exception
                           when others then raise;
                     end;
                  end if;
               end;
            end loop;
            commit;
            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;
      exception
         when others then raise;
   end get_tab_cmm;
   ------------------------------------------------------------------
   -- GET_COL_CMM
   ------------------------------------------------------------------
   procedure get_col_cmm
   is
   begin
      begin
         declare cursor get_cur is
                 select a.name object_name, b.name column_name, c.comment$
                   from sys.com$ c, sys.col$ b, sys.obj$ a
                  where (a.owner# = pkg_nkw_sys.get_nkid)
                    and (a.type# in (2,4))
                    and (a.obj# = b.obj#)
                    and (a.obj# = c.obj#)
                    and (b.col# = c.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);
               begin
                  begin
                     update nkw_col_cmm
                        set comments = get_row.comment$
                      where (object_name = get_row.object_name)
                        and (column_name = get_row.column_name);
                     exception
                        when others then raise;
                  end;
                  if (SQL%ROWCOUNT = 0) then
                     begin
                        insert into nkw_col_cmm(object_name, column_name, comments)
                        values(get_row.object_name, get_row.column_name, get_row.comment$);
                        exception
                           when others then raise;
                     end;
                  end if;
               end;
            end loop;
            commit;
            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;
      exception
         when others then raise;
   end get_col_cmm;
   --------------------------------------------------------------------
   -- POP_COMMENTS
   --------------------------------------------------------------------
   procedure pop_comments(fv_object in pkg_nkw_env.gv_char%TYPE := null)
   is
      lv_tabok   integer := 0;
      lv_tabfail integer := 0;
      lv_colok   integer := 0;
      lv_colfail integer := 0;
   begin
      begin
         declare cursor get_cur is
                 select a.object_name, a.comments
                   from nkw_tab_cmm a
                  where (a.object_name like nvl(upper(fv_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;
               begin
                  lv_tabok := nvl(lv_tabok,0) + 1;
                  pkg_nkw_rpl.run_code('lock table ' || get_row.object_name || ' in exclusive mode nowait');
                  pkg_nkw_rpl.run_code('comment on table ' || get_row.object_name || ' is ''' ||
                                       replace(get_row.comments,'''','''''') || '''');
                  exception
                     when others then
                        lv_tabfail := nvl(lv_tabfail,0) + 1;
               end;
            end loop get_loop;
            if (get_cur%ISOPEN) then
               close get_cur;
            end if;
         end;
      end;
      begin
         declare cursor get_cur is
                 select a.object_name, a.column_name, a.comments
                   from nkw_col_cmm a
                  where (a.object_name like nvl(upper(fv_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;
               begin
                  lv_colok := nvl(lv_colok,0) + 1;
                  pkg_nkw_rpl.run_code('lock table ' || get_row.object_name || ' in exclusive mode nowait');
                  pkg_nkw_rpl.run_code('comment on column ' || get_row.object_name || '.' || get_row.column_name ||
                                       ' is ''' || replace(get_row.comments,'''','''''') || '''');
                  exception
                     when others then
                        lv_colfail := nvl(lv_colfail,0) + 1;
               end;
            end loop get_loop;
            if (get_cur%ISOPEN) then
               close get_cur;
            end if;
         end;
      end;
      if (nvl(lv_tabfail,0) > 0 or nvl(lv_colfail,0) > 0) then
         raise_application_error(-20000, 'Terminate with errors' || chr(10) ||
                                         'Comments on Objects: ' || nvl(lv_tabok,0) ||
                                         ' Errors: ' || nvl(lv_tabfail,0) || chr(10) ||
                                         'Comments on Columns: ' || nvl(lv_colok,0) ||
                                         ' Errors: ' || nvl(lv_colfail,0));
      end if;
      exception
         when others then raise;
   end pop_comments;
end pkg_nkw_cmm;
/
show errors