This blog post gives a piece of simple SQL that will allow you to automatically copy the comments from one database view to another. In the case of this example we are copying the column comments from a standard oracle view to a materialised view which has the same name except with "_MV" suffix.
The PL/SQL block is;
declare
v_ViewName varchar2(30) := 'XXX';
begin
for v_Comment in (select acc.COLUMN_NAME, REPLACE(REPLACE(acc.COMMENTS, chr(13), ''), chr(10), '') comments
from all_col_comments acc
where acc.OWNER = USER
and acc.TABLE_NAME = v_NoetixViewName) loop
execute immediate
'comment on column ' || v_ViewName || '_MV.' || v_Comment.Column_Name || ' is ''' || v_Comment.Comments || '''';
end loop;
end;
To use this code in your system replace XXX with the name of the view you want to copy from and where is says "... v_ViewName || '_MV ..." you should replace this with the naming convention you are using for the view you want to copy to.
For example if you source view is called SOURCE and your target view is called TARGET then the SQL would become;
declare
v_ViewName varchar2(30) := 'SOURCE';
begin
for v_Comment in (select acc.COLUMN_NAME, REPLACE(REPLACE(acc.COMMENTS, chr(13), ''), chr(10), '') comments
from all_col_comments acc
where acc.OWNER = USER
and acc.TABLE_NAME = v_NoetixViewName) loop
execute immediate
'comment on column TARGET.' || v_Comment.Column_Name || ' is ''' || v_Comment.Comments || '''';
end loop;
end;
I've found this useful when creating new materialised views based on existing NoetixViews to copy across all the comments from the NoetixView to the Materialised view (rather than having to write the comments in myself).
Hope this helps!
The PL/SQL block is;
declare
v_ViewName varchar2(30) := 'XXX';
begin
for v_Comment in (select acc.COLUMN_NAME, REPLACE(REPLACE(acc.COMMENTS, chr(13), ''), chr(10), '') comments
from all_col_comments acc
where acc.OWNER = USER
and acc.TABLE_NAME = v_NoetixViewName) loop
execute immediate
'comment on column ' || v_ViewName || '_MV.' || v_Comment.Column_Name || ' is ''' || v_Comment.Comments || '''';
end loop;
end;
To use this code in your system replace XXX with the name of the view you want to copy from and where is says "... v_ViewName || '_MV ..." you should replace this with the naming convention you are using for the view you want to copy to.
For example if you source view is called SOURCE and your target view is called TARGET then the SQL would become;
declare
v_ViewName varchar2(30) := 'SOURCE';
begin
for v_Comment in (select acc.COLUMN_NAME, REPLACE(REPLACE(acc.COMMENTS, chr(13), ''), chr(10), '') comments
from all_col_comments acc
where acc.OWNER = USER
and acc.TABLE_NAME = v_NoetixViewName) loop
execute immediate
'comment on column TARGET.' || v_Comment.Column_Name || ' is ''' || v_Comment.Comments || '''';
end loop;
end;
I've found this useful when creating new materialised views based on existing NoetixViews to copy across all the comments from the NoetixView to the Materialised view (rather than having to write the comments in myself).
Hope this helps!
