- 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.
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.
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.
No comments:
Post a Comment