Browse Dataedo Documentation From Within Oracle SQL Developer

Michal Wrobel - Dataedo Team Michal Wrobel 2017-08-22

Table of Contents:


    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.

    0
    There are no comments. Click here to write the first comment.