Re: Data Dictionary pretty-printer

From: FC <flavio_at_tin.it>
Date: Wed, 18 Jun 2003 16:51:53 GMT
Message-ID: <JM0Ia.201594$g92.4097940@news2.tin.it>

Here are a couple of scripts.
The first one is a plain sql plus script outputting a long table containing everything. There is little room for html manipulations because almost everything is managed by sqlplus directly.

The second one is an anonymous plsql block creating a more structured html document containing a table of contents and links. In the latter case, if you know how to use CSS stylesheets, you could add a CSS reference in the head section and classes to the various elements and then define a custom CSS to change the look and feel of the tables in terms of fonts, colors, text alignments and so on. In order to run it, you'll have to change the existing directory path and/or filename and specify a location where you can write files to. This requires the parameter UTL_FILE_DIR to be set to the chosen directory in your init.ora file, assuming you haven't specified "*". I hope you're familiar with these details, if not either consult the manuals or ask your DBA.
Unfortunately not all column comments are present in the dictionary, refer to the Oracle Reference Manual for more information. Of course the result varies with the user depending on his/her privileges.

Bye,
Flavio

---

set feedback off

set echo off

set termout off

set markup html on spool on head "<title>Data dictionary views</title>"

spool dictionary.html

column description format a80

column comments format a80

clear breaks

break on table_name noduplicates on description noduplicates

column table_name heading "Table Name"

column description heading "Table Description"

column column_name heading "Column Name"

column comments heading "Column Comments"

select a.table_name, a.comments as description, b.column_name, b.comments from dict_columns b, dictionary a

where a.table_name = b.table_name

order by 1, 3;

spool off

set markup html off

exit

• cut here ----------------------------------

declare

   file_handle utl_file.file_type;
begin

   file_handle := utl_file.fopen('E:\export', 'dictionary.html', 'w');

   utl_file.put_line(file_handle, '<TABLE WIDTH="50%" BORDER="1" TITLE="Table of Contents"><TR><TH WIDTH="20%" ALIGN="left">Table Name</TH><TH WIDTH="80%" ALIGN="left">Description</TH></TR>');

   for each_table in (select table_name, comments from dictionary order by 1)

   loop

      utl_file.put_line(file_handle, '<TR><TD><A HREF="#' || each_table.table_name || '">' || each_table.table_name || '</A></TD>');

      utl_file.put_line(file_handle, '<TD>' || each_table.comments ||
'</TD></TR>');

   end loop;
   utl_file.put_line(file_handle, '</TABLE><BR><BR>');

   for each_table in (select table_name, comments from dictionary order by 1)

   loop

      utl_file.put_line(file_handle, '<H2><A NAME="' || each_table.table_name || '">' || each_table.table_name || '</A>');

      utl_file.put_line(file_handle, '<H3>' || each_table.comments ||
'</H3>');

      utl_file.put_line(file_handle, '<TABLE WIDTH="100%" BORDER="1"><TR><TH WIDTH="20%" ALIGN="left">Column Name</TH><TH WIDTH="80%" ALIGN="left">Comments</TH></TR>');

      for each_column in (select column_name, comments from dict_columns where table_name = each_table.table_name)

      loop
         utl_file.put_line(file_handle, '<TR><TD>' ||
each_column.column_name || '</TD>');
         utl_file.put_line(file_handle, '<TD>' || each_column.comments ||


'</TD></TR>');

      end loop;
      utl_file.put_line(file_handle, '</TABLE><BR><BR>');

   end loop;
   utl_file.fclose(file_handle);
end; Received on Wed Jun 18 2003 - 11:51:53 CDT