Oracle Apps Diary: Hierarchical SQL with Tree Diagram output

The greatest value of a picture is when it forces us to notice what we never expected to see
- John Wilder Tukey

Hierarchical SQL output is not easy to read in tabular format. I have seen developers trying to visualize the output in various ways. Provided you have a browser, it is not difficult to generate an output like the below using HTML and JavaScript.

Steps

1. Prepare Hierarchical SQL

I am using the classic EMP table in SCOTT schema.

SELECT e.ename node
,m.ename PARENT
,e.empno || ' ' || e.job tooltip
FROM scott.emp e
,scott.emp mWHERE e.mgr = m.empno(+)
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.job = 'PRESIDENT'

Please make sure that there are 3 columns in your SQL with aliases as below.

Node :- Unique name for the node
Parent :- Name of the parent Node
Tooltip : Tooltip description to be displayed when mouse is moved over the node.

2. Compile the below procedure in database.

CREATE OR REPLACE PROCEDURE draw_tree_diagram(p_sql IN VARCHAR2) IS
  l_html1 VARCHAR2(32767) := '<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load(''current'', {packages:["orgchart"]});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn(''string'', ''Name'');
        data.addColumn(''string'', ''Manager'');
        data.addColumn(''string'', ''ToolTip'');
        
        // For each orgchart box, provide the name, manager, and tooltip to show.
        data.addRows([
          ';

  l_html2 VARCHAR2(32767) := '
   ]);
          // Create the chart.
        var chart = new google.visualization.OrgChart(document.getElementById(''chart_div''));
        // Draw the chart, setting the allowHtml option to true for the tooltips.
        chart.draw(data, {allowHtml:true});
      }
   </script>
    </head>
  <body>
    <div id="chart_div">
</div>
</body>
</html>';

  TYPE empcurtyp IS REF CURSOR;
  v_emp_cursor empcurtyp;
  TYPE emp_record_type IS RECORD(
     node    VARCHAR2(30)
    ,PARENT  VARCHAR2(30)
    ,tooltip VARCHAR2(300));
  rec emp_record_type;

BEGIN
  dbms_output.put_line(l_html1);

  OPEN v_emp_cursor FOR p_sql;

  LOOP
    FETCH v_emp_cursor
      INTO rec;
    EXIT WHEN v_emp_cursor%NOTFOUND;
    IF rec.parent IS NOT NULL
    THEN
      dbms_output.put_line(',');
    END IF;
    dbms_output.put_line('[''' || rec.node || ''', ''' || rec.parent ||
                         ''', ''' || rec.tooltip || ''']');
  
  END LOOP;

  CLOSE v_emp_cursor;

  dbms_output.put_line(l_html2);

END draw_tree_diagram;

Step 3 : Execute the PLSQL procedure

Invoke the above PLSQL procedure with SQL in Step 1 as parameter. The output will be written to DBMS output. Please make sure that the output size is set accordingly.

BEGIN
  draw_tree_diagram('SELECT e.ename node
                    ,m.ename PARENT
                    ,e.empno || '' '' || e.job tooltip
              FROM   scott.emp e
                    ,scott.emp m
              WHERE  e.mgr = m.empno(+)
              CONNECT BY PRIOR e.empno = e.mgr
              START  WITH e.job = ''PRESIDENT''');
END;

Step 4 : Save the output as an htm file.

The output will be written with html tags with JavaScript. Save the output with an htm extension.

Step 5: Open the HTM file in web browser

This will display the hierarchical output as a tree diagram assuming the local machine has internet connection. Internet connection is required since the JavaScript tries to access the library on the Google website.

Oracle Apps Diary

Wednesday, October 12, 2016

Hierarchical SQL with Tree Diagram output

No comments: