When it comes to documenting Oracle databases Dataedo is the tool of my choice, but it lacks one important feature. My desktop is always occupied by Oracle SQL Developer and I have to ALT+TAB each time I need to view documentation. Then I scroll tables list which takes another frustrating five seconds to read descriptions.

Necessity is the mother of invention, so I decided to use XML extensions to browse my documentation directly from within SQL Developer.

Foreplay

My plan of action is to extract documentation from Dataedo repository (stored in SQL Server database) using SQL query, replicate it into my Oracle database, and access it using custom XML Extension in SQL Developer. In my example, I will extract only columns descriptions, but it would be easy to extend it to support other objects.

Extract

First, browse documentations tree in Dataedo and check your documentation name.

Now connect to Dataedo repository using Microsoft SQL Management Studio. You will find connection details in Dataedo login window. You can view columns titles and descriptions using this simple query:

SELECT 
    tables.object_type,
    tables.[schema],
    tables.[name] table_name,
    columns.[name] column_name,
    columns.title column_title, 
    columns.[description] column_description
FROM  databases
    INNER JOIN tables ON tables.database_id = databases.database_id
    INNER JOIN columns ON columns.table_id = tables.table_id
WHERE 
    databases.title = 'My test documentation' -- put your documentation name here

Replicate

I decided to replicate data from SQL Server to Oracle using a database link, but you can also generate the script using Management Studio or use Integration Services or other tools.

In my production environment I put replication in database job, so I get documentation updates each day.

First create documentation table in Oracle database:

 CREATE
  TABLE dataedo_documentation
  (
    object_type        VARCHAR2(100),
    schema             VARCHAR2(250),
    table_name         VARCHAR2(250),
    column_name        VARCHAR2(250),
    column_title       VARCHAR2(250),
    column_description VARCHAR2(1000)
  )

Then copy documentation from Dataedo repository to Oracle database using linked server:

insert into openquery(LINKED_ORACLE_SERVER, 'select * from dataedo_documentation')
SELECT 
    tables.object_type,
    tables.[schema],
    tables.[name] table_name,
    columns.[name] column_name,
    columns.title column_title, 
    columns.[description] column_description
FROM databases
    INNER JOIN tables ON tables.database_id = databases.database_id
    INNER JOIN columns ON columns.table_id = tables.table_id
WHERE 
    databases.title = 'My test documentation' -- put your documentation name here

XML extension

Oracle SQL Developer is a great tool, which you can make even greater using custom extension.

Create text file and save it with .xml extension (“dataedo.xml” in my case)

<items>
   <item type="editor" node="TableNode"  vertical="true">
      <title><![CDATA[Dataedo documentation]]></title>
      <query>
         <sql><![CDATA[select column_name Name, column_title Title, column_description Description 
            from dataedo_documentation
            where object_type = 'TABLE' 
                                             and schema = :OBJECT_OWNER 
                                             and table_name = :OBJECT_NAME
            order by column_name]]></sql>
      </query>
   </item>   
</items>

This simple script instructs SQL Developer to add a custom tab to table description window. For each table, it will select column descriptions from replicated documentation.

Next register your extension in Tools -> Preferences -> Database -> User Defined Extension -> Add Row

Choose extension type “EDITOR” and browse for your .xml file.

Save changes using OK button and restart SQL Developer.

WOW!

Now browse your Oracle database and click on the table name. As you can see new tab appeared in table description window. You can browse your columns documentation there!

We are now continuing to explore more options to integrate Dataedo and SQL Developer.

You don't document your Oracle databases with Dataedo yet? Have a try.