Navigation Tree with Checkboxes

Oracel APEX (Application Express) seems to be the best tool for PLSQL developer who plan to foray into Web Development. APEX provides an easy to use interface to build a web application in minutes based on existing database design.

In this post, we'll be taking a look at how to develop a tree with checkboxes against each node. This can be useful incases where we need to provide an interface wherein the user has select from a predefined hierarchical input and process further.

This is pretty simple with Oracle APEX API provided. We have a package called APEX_ITEM which can be used to create form elements dynamically at run time. We just need to change the usual SQL for tree and we are ready with a tree with check boxes.

For example, the usual sql for a tree view is as follows

select id,
pid,
name,
link,
a1,
a2
from "#OWNER#"."TABLE"

In order to have a checkbox prefixed to each node, change the code to

select id,
pid,
apex_item.checkbox(p_id,p_value,p_attributes)||name "name",
link,
a1,
a2
from "#OWNER#"."TABLE"

In the above statement
p_id => number to determine the APEX global variable used. A number between 1 to 50.
p_value => the value of the checkbox. This is the value which will be passed to the application when the checkbox is selected
p_attributes => varchar parameter specifying the attributes to control the rendering (HTML) of the checkbox. We can use "CHECKED" to make every checkbox to be checked by default

For example to display the employees(using the standard emp table) in a tree

select empno,
mgr,
apex_item.checkbox(1,empno,'')||ename "name",
link,
a1,
a2
from "#OWNER#"."EMP".

This will render a tree with employee hierarchy (provided you have defined other attributes of tree like root node).

If we want all the checkboxes to be checked by default you can replace the call apex_item.checkbox(1,empno,'') with apex_item.checkbox(1,empno,'CHECKED')