Yesterday I came across Graphviz and remembered that a college had already used it to generate some ERDs/Class diagrams. After taking a closer look, I thought to give it a try and use it to generate a page flow diagram for an APEX application as you can also see it for JSF in JDeveloper.
After some try and error figuring out how to use this tool. I created a new package for my ApexLib Framework to do all the work. The diagram output is not optimal yet (I have to ask my college for tips & tricks when he is coming back from vacation), but for a quick overview I think it’s good enough.
So what does my script do?
It generates for each page group (Application/Tasks/Manage Page Groups) a separate diagram, which just contains the pages of this group or referenced pages of other groups (they show gray). Links with a condition are displayed red.
It also generates an overview diagram which contains all the pages of the application.
Pages which do not exist anymore, but which are referenced somewhere are marked red. So you can even use this diagram to find out if you have some dead links.
How to use it?
- Get the newest release of the ApexLib Framework.
- If you just want to use this feature of the framework you only have to install the ApexLib_PageFlowDiagram package. See HowToInstall.html for details. Note: This package needs access to the tables in the FLOWS_XXX schema, because the APEX repository views didn’t contain all the necessary information. Eg there is now view for WWV_FLOW_PAGE_GROUPS. That’s why it will not work if you install the ApexLib Framework into your application schema.
- Download Graphviz and install it.
- Try it the following command works in a command box
dot.exe -h
If it doesn’t work, you can add the Graphviz directory to your PATH or you include the directory in the $dot (eg. $c:\programs\graphviz\dot) call in ApexLib_Generate_PageFlowDiagram.sql
- Open sqlplus and connect to your application schema or to the apexlib schema.
sqlplus apex_demo/apex_demo@mydatabase
- Run the script ApexLib_Generate_PageFlowDiagram.sql
@ApexLib_Generate_PageFlowDiagram.sql
and enter the id of your application.
- Now you should see several PNG files in your directory!
I tried it with the sample application “Software Projects” and the output seems to be ok.
Problems?
If it doesn’t work, check the tmp_export_diagram.sql file and manually execute the steps and look which step fails.
Suggestions?
I’m sure I didn’t capture every property or object which can have a link to a page, so if you notice some missing links. Let me know!
January 11th, 2007 at 01:58
Ah GraphViz, GraphViz and perl.
I used to use both all the time to
produce pretty (or not so pretty)
diagrams of the internal workings of ZygoWare(tm) (see http://www.intechsolutions.co.uk)
Happy days !
January 12th, 2007 at 10:24
WOW!
This is an excellent tool to help document apex applications. Great work!
January 12th, 2007 at 11:35
Thanks Maline!
If you have any ideas how to improve it, let me know!
January 17th, 2007 at 18:37
Is there any way you can use this graph feature just through the browser based app? i cant/dont know how to get to the sever via sqlplus
January 18th, 2007 at 00:23
Hi,
not yet, but I’m looking into that.
Your DBA should be able to help you how to connect to the server. Or are you using Oracle XE on your local PC?
Patrick
January 31st, 2007 at 21:30
To generate the PNG from Apex in XE on Windows, I did this (need to experiment with minimal grants, and try FTP user other than sys). Grant execute on utl_file, dbms_scheduler, create any job, create any directory to user1. Ensure OracleJobSchedulerXE started in Windows services.
From http://daust.blogspot.com/2006/03/where-are-images-of-application.html
SQL> exec dbms_xdb.setftpport(’2100′);
PL/SQL procedure successfully completed.
SQL> alter system register;
System altered.
Write the dot input to file using utl_file then call a DOS script to generate the PNG and get it back into XE.
– DBMS_SCHEDULER.create_job
– (job_name => ‘testjob’,
– job_type => ‘EXECUTABLE’,
– job_action => ‘c:\windows\system32\cmd.exe /c c:\gen_png.bat’,
– enabled => false
– );
DBMS_SCHEDULER.run_job
(job_name => ‘testjob’,
use_current_session =>TRUE);
–commit;
gen_png.bat
———–
cd c:\
type DOT.txt | “C:\Program Files\ATT\Graphviz\bin\dot.exe” -Tpng -o test.png
REM ftp .png back into Oracle…
c:\windows\system32\ftp.exe -s:ftp.scr
http://ftp.scr
——-
open localhost 2100
sys
pass1
bin
cd public
put c:\test.png
bye
Then just reference the image in apex:
<img src=”http://127.0.0.1:8080/public/test.png”>
February 1st, 2007 at 00:11
Nice solution!! Thanks for sharing!
I have created a feature request to be able to specify where the output of generateDiagram should be directed to (to a file handler or dbms_output)
Patrick
February 1st, 2007 at 13:44
To avoid the ftp, you should also be able to read the the bfile from pl/sql directly like this:
http://www.orafaq.com/forum/t/54790/0
One advantage is that the image image isn’t bookmarkable (you can have authentication in the pl/sql retrieving the image)
March 28th, 2007 at 13:35
Very nice and usefull.
We have an apex app that we use for tracking complex jobs and jobdepencies. Would be very nice to generalize the functionality to display graphs on some given view/table structure, we would love to have job trees
May 3rd, 2007 at 09:21
Very useful! I have Oracle XE on Linux so it didn’t complete the process of creating the PNGs. I just did it manually.
Thanks again.
May 3rd, 2007 at 10:47
Hi,
I assume the call in ApexLib_Generate_PageFlowDiagram.sql for the “dot” executable is different on Linux. Just adapt the script to automatically generate it on Linux.
Greetings
Patrick
July 19th, 2007 at 15:00
rather than using ftp like shown above (which needs user/pass), you can do this:
DECLARE
rv BOOLEAN;
BEGIN
BEGIN
DBMS_XDB.deleteresource (’/public/test.svg’, DBMS_XDB.delete_force);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
— unsure if charset is relevant/required but it works
rv := DBMS_XDB.createresource (’/public/test.svg’,BFILENAME (’MY_DIR’, ‘test.svg’), NLS_CHARSET_ID (’AL32UTF8′));
END;