Documenting the Data Model

[D.]

Hi All,

This is just a general Oracle question. I've been looking at using plsqldoc and the way it generates HTML docs out of table/column comments. Does anyone out there have any other methods they use to document the data model, and how to keep it up-to-date ?

I would like to keep the documentation next to the objects, in the form of comments in the database, but DBAs frown on this due to frequent export/import or drop/create table maintenance exercises. This removes the comments (so they say), and they have to be re-created. Moreover, the plsqldoc generated docs from comments are not done on-the-fly, so they need re-generated when a change is made. One suggestion I was given was to use Oracle Designer repository for documentaion, and generate dynamic HTML pages from the designer database. This keeps the pages up-to-date, and you can run regular reconcilliation reports against the master / production database. However, we dont use Designer and seems a wee bitty expensive for just a documentation tool.

Any other suggestions ? What do you use ?

Sorry, this isn't exactly PLSQL Developer specific, but I would like to know your thoughts on using plsqldoc for this sort of docs.

Regards,
D.

 

[whip]

You don't (shouldn't) lose comments on import/export. If someone drops/recreates tables and forgets about comments, that's a different thing...

What we do is generate HTML pages from the data dictionary (well, almost). This does not require any reconciliation (vs Designer). I use standard xml functions to generate HTML - and try to avoid differences in XML and HTML syntax. This method is quite easy once you get used to it. And PL/SQL Developer can be used to test the generated docs (to make the topic relevant here )

Simple example follows

SELECT
    xmlelement(
        "html",
        xmlconcat(
            xmlelement(
                "head",
                xmlelement(
                    "title",
                    'Table ' || table_name
                )
            ),
            xmlelement(
                "body",
                xmlconcat(
                    xmlelement(
                        "h1",
                        'This is the documentation of ' || table_name
                    ),
                    xmlelement(
                        "p",
                        'Columns:'
                    ),
                    xmlelement(
                        "table",
                        xmlattributes('1' AS "border"),
                        xmlconcat(
                            xmlelement(
                                "tr",
                                xmlforest(
                                    'Name' "th",
                                    'Data type' "th",
                                    'Comments' "th"
                                )
                            ),
                            (
                                SELECT
                                    xmlagg(
                                        xmlelement(
                                            "tr",
                                            xmlforest(
                                                cols.column_name "td",
                                                cols.data_type "td",
                                                comm.comments "td"
                                            )
                                        )
                                        ORDER BY cols.column_id
                                    )
                                FROM
                                    user_tab_columns cols,
                                    user_col_comments comm
                                WHERE
                                    cols.table_name = tab.table_name AND
                                    comm.table_name = cols.table_name AND
                                    comm.column_name = cols.column_name
                            )
                        )
                    )
                )
            )
        )
    ).getclobval()
FROM user_tables tab
WHERE table_name = '&table_name'

 

[D.]

Hi Whip,

This is exactly the kind of thing I was intending to introduce - dynamic HTML generated from the database at runtime. Thanks, for your code example though.

I can assume, then, that you DO use the table/column comments for this documentation purpose? Do you generate your docs from the full production DB, or do you snapshot the comments into another DB and generate from there?

Anyone else got any suggestions, or preferred methods of documentation ?

D.

 

[whip]

Originally posted by David Wilkie:
I can assume, then, that you DO use the table/column comments for this documentation purpose?

Yes.

Originally posted by David Wilkie:
Do you generate your docs from the full production DB, or do you snapshot the comments into another DB and generate from there?

The docs are used mostly by developers and the dev db is the current one so the generator runs there. The data dictionary in the prod environment also contains the comments so we could run the doc generator there if we wanted.
I think whether you should create a snapshot in your situation depends largely on what tools you use for maintaining the data model (and what approach the DBAs take ). Once you change something in the tables you need to take a new snapshot. I like to have everything running automatically.

 

[D.]

Cheers, whip!

Anyone else got any good approaches they use. Even if you agree with this method, please say so!

I want a consensus of opinion on whether this approach is 1) used by others, 2) recommended by others.

Thanks,

D.

 

[Stew Stryker]

This query is pretty cool and I'll definitely put this to use. Minor thing, I was surprised you didn't include the Table comment or the column sizes with their types.

I enhanced it to do those things as well as select from ALL_TABLES here:

SELECT
    xmlelement(
        "html",
        xmlconcat(
            xmlelement(
                "head",
                xmlelement(
                    "title",
                    'Table ' || tab.table_name
                )
            ),
            xmlelement(
                "body",
                xmlconcat(
                    xmlelement(
                        "h1",
                        'This is the documentation of ' || tab.table_name
                    ),
                    xmlelement(
                        "p",
                        'Table comment: ' || nvl(tcomm.comments, 'None.')
                    ),
                    xmlelement(
                        "p",
                        'Columns:'
                    ),
                    xmlelement(
                        "table",
                        xmlattributes('1' AS "border"),
                        xmlconcat(
                            xmlelement(
                                "tr",
                                xmlforest(
                                    'Name' "th",
                                    'Data type' "th",
                                    'Comments' "th"
                                )
                            ),
                            (
                                SELECT
                                    xmlagg(
                                        xmlelement(
                                            "tr",
                                            xmlforest(
                                                lower(cols.column_name) "td",
                                                CASE WHEN
                                                     cols.data_type IN ('VARCHAR2', 'CHAR')
                                                     THEN
                                                         cols.data_type || '(' || cols.data_length || ')'
                                                     WHEN cols.data_type = 'NUMBER'
                                                     THEN
                                                         cols.data_type || '(' || cols.data_precision || ', ' ||
                                                                        cols.data_scale || ')'
                                                     ELSE
                                                         cols.data_type
                                                     END
                                                "td",
                                              --  cols.data_type "td",
                                                comm.comments "td"
                                            )
                                        )
                                        ORDER BY cols.column_id
                                    )
                                FROM
                                    all_tab_columns cols,
                                    all_col_comments comm
                                WHERE
                                    cols.owner = tab.owner AND
                                    cols.table_name = tab.table_name AND
                                    comm.owner = tab.owner AND
                                    comm.table_name = cols.table_name AND
                                    comm.column_name = cols.column_name
                            )
                        )
                    )
                )
            )
        )
    ).getclobval()
FROM all_tables tab
JOIN all_tab_comments tcomm ON tcomm.owner = tab.owner
                      AND tcomm.table_name = tab.table_name
WHERE tab.table_name = '&table_name'
  AND tab.owner = '&owner'

- Stew

 

[whip]

Thanks for the addition. My purpose was just to show our approach with a simplified example.
Cheers

 

[Stew Stryker]

And yet it was very useful too!

Thanks

 

[D.]

Can anyone get the above XML to HTML generation to work ? If so, can you paste an example of the generated HTML ?

All I get is an error with XMLCONCAT. Here's the example from the Oracle 9i manuals which also doesn't work on my DB :-

SQL>
SQL> SELECT XMLCONCAT(XMLELEMENT("First", e.first_name),
  2     XMLELEMENT("Last", e.last_name)) AS "Result"
  3     FROM emp e
  4  /
SELECT XMLCONCAT(XMLELEMENT("First", e.first_name),
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLCONCAT'

Or, if anyone can diagnose what's wrong with my query or DB then please let me know. XML modules have been reloaded, but problem doesn't go away.

Regards,
D.

 

[whip]

Searching for your error message I found:

The patch instructions say the following as part of the pre-install steps for Oracle 9i patch on RDBMS on Windows 2000:
"drop public synonym xmlconcat;"
"drop function xmlconcat;"

Please verify that this applies to your case before doing something irreversible .