Creating dynamic breadcrumb chains in Oracle APEX

This article is borrowed from one of the topics of my book (http://www.packtpub.com/oracle-apex-4-2-reporting/book) on Oracle APEX 4.2 reporting. Check the chapter by chapter description and the table of contents to know about the other discussions in my book. I wish to talk about the process to engineering breadcrumbs of varying lengths in this article. Let me explain the motive with an example.


Let us say that we have an employee report with a link on each of the employee ids.(Refer this for an example) Let us say that the link on employee ids drills down to show the subordinates of the employee. Now the user can drill down but the user would also need drill up links in case he wants to return to a higher level. We will have to create breadcrumbs for this use case but the number of breadcrumb links will depend on the designation of an employee. For example, a new joinee might have a number of links that connect him to the CEO of the company while a top tier manager might have very few links connecting him to the CEO. Clearly, the number of breadcrumb links cannot be fixed at the development time.
I have created a report to present this use case at https://apex.oracle.com/pls/apex/f?p=81782:1

The traditional way of creating dynamic breadcrumbs is by using page items as substitution variables in the breadcrumb entry. This type of dynamic breadcrumb works if the number of levels is fixed, and only the page number to which each breadcrumb entry refers has to be dynamic.

Since we do not know the number of breadcrumb links at the time of development, we will have to create breadcrumb entries on the fly when the user clicks on an employee ID. This can be achieved using PL/SQL Dynamic Content region of Oracle APEX.

We must have a look at a regular breadcrumb region of our theme  before coding this PL/SQL Dynamic Content region. This is necessary because the CSS classes differ from theme to theme. I did this and came up with the following PL/SQL code for https://apex.oracle.com/pls/apex/f?p=81782:1 example.

Note that the Display Point and the  Template of this region should be the same as the Display Point and the Template of a traditional breadcrumb region.

Let us now check out the code:

DECLARE
l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
breadcrumbString varchar2(500);
BEGIN
htp.p('<div id="topbar"><div class="breadcrumb-region"><div id="breadcrumb_container"><ul><li class="open"><span></span></li>');
htp.p('<li><a href="f?p=&APP_ID.:1:&APP_SESSION.::NO::P1_MANAGER_ID:">All employees</a></li>');
SELECT *
INTO breadcrumbString
FROM
(SELECT SYS_CONNECT_BY_PATH(last_name||'('||employee_id||')', ':') 
"Path"
FROM oehr_employees
WHERE employee_id = :P1_MANAGER_ID
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
ORDER BY level DESC
)
WHERE rownum = 1;
l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(breadcrumbString);
FOR z IN 2..l_vc_arr2.count LOOP
htp.p('<li class="sep"><span></span></li><li><a href="f?p=&APP_ID.:1:&APP_SESSION.::NO::P1_MANAGER_ID:'||REGEXP_SUBSTR(l_vc_arr2(z),'[[:digit:]]+')||'">'||l_vc_arr2(z)||'</a></li>');
END LOOP;
htp.p('<li class="close"><span></span></li></ul></div>');
Exception
When no_data_found then
htp.p('<li class="close"><span></span></li></ul></div>');
END;

We start with coding the beginning of the <div> tags along with the appropriate classes for the theme. This code has been highlighted in yellow
Creation of the div tags is followed by the creation of the link for 'All Employees'.
My page has a hidden item called P1_MANAGER_ID and I have a link on employee_id of the report (Check: https://apex.oracle.com/pls/apex/f?p=81782:1). Every time the user clicks on one of the employee id links, the page gets refreshed and the employee_id of the link is set in P1_MANAGER_ID item. P1_MANAGER_ID is then used in the where clause of the query to display only the subbordinates of the selected employee.

P1_MANAGER_ID is also used to frame chain of values to be displayed in the dynamic breadcrumb using a hierarchical query. The code that does this is highlighted in blue.

Once our chain is formed, we use APEX_UTIL.STRING_TO_TABLE so that we can add the necessary HTML for each of the values in the chain. We run a loop and add the necessary HTML.
Note that we are using regular expressions to get the empoyee_id from the breadcrumb. The code that uses regular expressions is highlighted in green. The extracted employee_id is used if the user clicks on any of the links in the dynamic breadcrumb

Check https://apex.oracle.com/pls/apex/f?p=81782:1 to see an example of the above code. Check http://obiee-oracledb.blogspot.in/2013/07/my-upcoming-book-oracle-apex-42.html and http://obiee-oracledb.blogspot.in/2013/08/description-of-my-book-on-oracle-apex.html to find  info about my book that has many more such discussions.

Cheers