PKG_NKW_CMM.SPB
Source Package Body
Comentários em objetos.
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;
|
