October 3, 2005: On generating ERDs with desc_table and neato

On September 25, 2005, I presented desc_table which is a package that can be used to extract various information about a table or view, such as column names, their types, primary key and foreign key constraints, as well as comments made on the table and its columns.

In this article, I try to show how this package can be used to create Entity Relationship Diagrams (ERD) along with neato. neato is an open source software that can be used to create graphs (directed or undirected).

In order to drive desc_table, there's another package, schema_to_neato:

create or replace package schema_to_neato
  authid current_user
as 
  -- Don't confuse with desc_table.tables_t
  type tables_t is table of varchar2(61);

  procedure create_neato(tables in tables_t);
end schema_to_neato;
/

And the package's body:

create or replace package body schema_to_neato as

  type descriptions is table of desc_table.description;

  procedure print(l in varchar2) is begin
    dbms_output.put_line(l);
  end print;

  procedure add_relations(descs in descriptions) is 
    description desc_table.description;

    procedure add_relation(parent in desc_table.table_t, child in desc_table.table_t) is begin
      print('  "' || parent.own || '.' || parent.nam || '" -> "' || child.own || '.' || child.nam || '"');
    end add_relation;
  begin
    
    -- iterating over all table descriptions:
    for desc_no in 1 .. descs.count loop
      description := descs(desc_no);

      for child_no in 1 .. description.children.count loop
        for tab_no_i in 1 .. descs.count loop

          if descs(tab_no_i).tab.nam = description.children(child_no).nam and
             descs(tab_no_i).tab.own = description.children(child_no).own then

            add_relation(descs(tab_no_i).tab, descs(desc_no).tab);
          end if;

        end loop;
      end loop;
    end loop;
  end add_relations;

  procedure create_neato(tables in tables_t) is 
    descs descriptions := descriptions();
  begin
    print('digraph ri {'                                      );
    print('  page = "15,10";'                                 ); -- A3
    print('  overlap=false;'                                  );
    print('  splines=true;'                                   );
    print('  node [fontsize=8 fontname=Verdana shape=record];');

    for idx_table in 1 .. tables.count loop
      descs.extend;
      descs(descs.count) := desc_table.describe(tables(idx_table));
    end loop;

    add_relations(descs);
    
    print('}');
  end create_neato;

end schema_to_neato;
/

The following anonymous block calls schema_to_neato.create_neato. I pass it some names of tables found within Oracle sample schemas, each prefixed with its schema.

neato_from_shell.sql

set serveroutput on size 100000 format wrapped
set feedback off
set pagesize 0

spool neato_created.neato

begin
  schema_to_neato.create_neato(
    schema_to_neato.tables_t(
        'HR.JOBS'       , 'OE.ORDERS'     , 'HR.COUNTRIES'  , 'HR.EMPLOYEES'          ,
        'PM.PRINT_MEDIA', 'OE.INVENTORIES', 'SH.SALES'      , 'HR.REGIONS'            ,
        'HR.LOCATIONS'  , 'OE.CUSTOMERS'  , 'HR.JOB_HISTORY', 'PM.ONLINE_MEDIA'       ,
        'SH.COSTS'      , 'SH.PRODUCTS'   , 'SH.CUSTOMERS'  , 'OE.WAREHOUSES'         ,
        'OE.ORDER_ITEMS', 'SH.TIMES'      , 'SH.CHANNELS'   , 'OE.PRODUCT_INFORMATION',
        'SH.COUNTRIES'  , 'SH.PROMOTIONS' , 'HR.DEPARTMENTS', 'OE.PRODUCT_DESCRIPTIONS'
        --'PM.TEXTDOCS_NESTEDTAB', 
      )
  );
end;
/

spool off

exit

This script can be called from the shell (or cmd.exe on Windows) like so (rene is the user to whom the created packages belong):

sqlplus rene/rene @neato_from_shell

Calling and executing the script creates the file neato_created.neato. This file can now be turned into a png by executing neato. The -T flag specifies the output format (here: png), while the -o flag specifies the name of the file to be created. Finally, the last parameter (neato_created.neato) is the input file (which is created using schema_to_neato).

neato -Tpng -oerd.png neato_created.neato

And here's the produced png:

Further links

See also Create an ERD out of SQL create table statements.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger