The EMP mindmap

By accident, I stumbled upon a Java script library for generating mind maps. But instead of having a static diagram, this Java script enabled one is dynamic, you can move nodes, you can zoom in or out to another level of nodes. A very nice graphical way of presenting hierarchical data. For example, who's managing who in Oracle's EMP table as shown below. Come on, grab your mouse and start moving and clicking, it's alive!

Here's the recipe to build such an application.

• Start with downloading the source of the Google code project js-mindmap. In the sources you will several Java script libraries, and an example file js-mindmap.html.

• In APEX upload the Java script libraries add references from the header section to these libraries.

• Add a PL/SQL region, which generates the hierarchy and will be converted in a diagram:

  <canvas id="cv"></canvas>
  <div id="js-mindmap">
    <a>KING</a>
    <ul>
    <li>BLAKE
    <ul>
     <li>ALLEN</li>
     ...
     <li>JAMES</li>
    </ul>
    ...
   </ul>
   </li> 
  </div>

  For example, the function show_emp_tree returns the html code for the <div> element.

Note that when you replace the EMP table by OEHR_EMPLOYEES table the animation will become very slow due to the increase of nodes and links. Also the js-mindmap project is still in development. But anyway it's fun!

To b:\ or not to B:\

Recently, I ran into a small issue with Java permissions. Starting point is the DirList Java procedure to list the content of an OS directory. Here is the code to get started (executed as SCOTT):

CREATE global TEMPORARY TABLE DIR_LIST ( filename VARCHAR2(255) ) ON
 COMMIT
  DELETE rows
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirList"
AS
 import java.io.*;
 import java.sql.*;
 public class DirList { 
  public static void getList(String directory) throws SQLException { 
    File path = NEW File( directory );
    String[]
    list = path.list();
    String element;
    for(INT i = 0; i < list.length;  i++) { 
      element = list[i];
      #sql { INSERT INTO DIR_LIST (FILENAME) VALUES (:element) };
    } 
  } 
 }
/

CREATE OR REPLACE PROCEDURE get_dir_list ( p_directory IN VARCHAR2 ) 
  AS language java name 'DirList.getList( java.lang.String )'; 
/

Don't grant the role JAVAUSERPRIV but use a more granular option. Grant read permission to SCOTT on the d: drive:

EXECUTE dbms_java.grant_permission( 'SCOTT', 'java.io.FilePermission','d:\','read' );

Everything is in place now, time to run the procedure:

EXECUTE get_dir_list('D:\');
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: 
the Permission (java.io.FilePermission D:\ read) 
has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(SCOTT|PolicyTableProxy(SCOTT))
ORA-06512: at "SCOTT.GET_DIR_LIST", line 0
ORA-06512: at line 2

An error occurred, perhaps the directory did not exists? Executing a dir D:\ at the command prompt on the database server lists the files. The command prompt is not case sensitive, executing a dir d:\ returns the same listing. Perhaps but DBMS_JAVA.GRANT_PERMISSION is case sensitive?

EXECUTE get_dir_list( 'd:\' );
PL/SQL procedure successfully completed

SELECT * FROM DIR_LIST
/
FILENAME
--------------------------------------------------------------------------------
oracle
RECYCLER
System Volume Information

APEX 3.1 On XE

On 29 Februari 2008, Oracle released Application Express 3.1 . The page Oracle Database XE and Application Express 3.1 provides upgrade instructions .