Skip to main content

Unlocking Dynamic Navigation: Column Links in Oracle APEX Interactive Reports

In this blog, I demonstrate how to leverage the built-in navigation feature of Oracle APEX Interactive Reports (IR). Normally, IR allows you to redirect to other pages by passing column values to target page items with secure, automatically generated checksum values. However, my implementation goes a step further: based on specific column values, I dynamically choose the target page and navigate accordingly.

For this example, I use the default EMP table. The interactive report displays employees grouped by department number, and depending on the department number (column value), the navigation redirects to different pages. Additionally, the relevant values are set in target page items for further processing.

This approach showcases dynamic, multi-page navigation driven by column values in an IR, enhancing user experience by routing them contextually based on the data they interact with.

Create an Interactive Report using the EMP table. In the Attributes section, under the Link settings, use the option to configure redirect behavior

Link Column : Link Custom Target
Target_Type : URL
Target : javascript:redirectPage('#EMPNO#','#ENAME#','#DEPTNO#'); 
Link_Icon : <span class="fa fa-link"></span>

The redirectPage JavaScript function receives column values as parameters.

In page property under function and global variable declaration paste following code

function redirectPage(p_empno, p_ename, p_dept) {
    if (p_dept === null || p_dept === "" || p_dept === undefined) {
        alert("Department number is null");
        return;
    }
    else {

        apex.server.process(
            "GET_REDIRECT_URL",
            {
                x01: p_empno,
                x02: p_ename,
                x03: p_dept
            },
            {
                success: function (pData) {
                    if (pData.success === true) {
                        console.log("Redirect URL:", pData.url);
                        window.open(pData.url, "_blank");
                    } else {
                        console.log("Process returned FALSE");
                    }
                },
                error: function (req, status, error) {
                    console.log("Ajax Error: " + status + " - " + error);
                }
            }
        );
    }
}


The GET_REDIRECT_URL AJAX callback process is used to dynamically generate URLs because it makes the code easier to maintain and modify. Creating the URL in the AJAX callback allows you to build complex queries or logic to determine the target page and the values to be passed.

To implement this, create an AJAX callback process at the page level under the AJAX Callback Processes section.

Note: The AJAX callback name must exactly match the JavaScript function name that calls it.

This approach provides flexibility in constructing URLs and ensures safe, dynamic redirection based on your business logic.

   DECLARE
        l_page_no   NUMBER;
        l_url       VARCHAR2(4000);
    BEGIN
        IF apex_application.g_x03 = 10 THEN
            l_page_no := 10;
        ELSIF apex_application.g_x03 = 20 THEN
            l_page_no := 20;
        ELSE
            l_page_no := 30;
        END IF;

        l_url := 'f?p=' || :APP_ID || ':' || l_page_no || ':' || :APP_SESSION ||
                 '::NO:RP:' ||
                 'P'|| l_page_no || '_EMPNO,'||
                 'P'|| l_page_no || '_DEPARTMENT,'||
                 'P'|| l_page_no || '_EMPNAME:' ||
                 apex_application.g_x01|| ',' ||
                 apex_application.g_x02 || ',' ||
                 apex_application.g_x03;

        apex_json.open_object;
        apex_json.write('success', true);
        apex_json.write('url', apex_util.prepare_url(l_url));
        apex_json.close_object;
    EXCEPTION
        WHEN OTHERS THEN
            apex_json.open_object;
            apex_json.write('success', false);
            apex_json.write('error', sqlerrm);
            apex_json.close_object;
    END;

Instead of hardcoding, we now set the l_page_no value dynamically based on a query. After saving and running the application, it works perfectly.

Comments

Post a Comment

Popular posts from this blog

APEX - Tip: Fix Floating Label Issue

Oracle APEX's Universal Theme provides a modern and clean user experience through features like floating (above) labels for page items.  These floating labels work seamlessly when users manually enter data, automatically moving the label above the field on focus or input.  However, a common UI issue appears when page item values are set Dynamically the label and the value overlap, resulting in a broken and confusing user interface. once the user focuses the affected item even once, the label immediately corrects itself and displays properly. When an issue is reported, several values are populated based on a single user input, causing the UI to appear misaligned and confusing for the end user. Here, I'll share a few tips to fix this issue. For example, employee details are populated based on the Employee name. In this case, the first True Action is used to set the values, and in the second True Action, paste the following code setTimeout(function () {   $("#P29_EMAIL,#P29_...
Post a Comment
Read more

Oracle APEX UI Tip: Display Page Title Next to the APEX Logo

In most Oracle APEX applications, every page has a Page Title displayed at the top. While useful, this title occupies vertical space, especially in apps where screen real estate matters (dashboards, reports, dense forms). So the goal is simple: Show the page title near the APEX logo instead of consuming page content space. This keeps the UI clean, professional, and consistent across all pages. Instead of placing the page title inside the page body:         ✅ Fetch the current page title dynamically         ✅ Display it right after the APEX logo         ✅ Do it globally, so it works for every page All of this is achieved using:         ✅ Global Page (Page 0)         ✅ One Dynamic Action         ✅ PL/SQL + JavaScript Simple, effective, and reusable. 1️⃣ Create a Global Page Item On Page 0 (Global Page), create a hidden item:      P0_PAGE_TITLE This item wi...
Post a Comment
Read more

Building a Custom Debug Package for Oracle APEX Using PL/SQL

While developing Oracle APEX applications, debugging page processes and backend PL/SQL logic can be challenging—especially when values are lost between processes or execution flow is unclear.  Although DBMS_OUTPUT is useful, it doesn’t work well inside APEX runtime. To solve this, I built a custom PL/SQL debug Package that logs execution flow and variable values into a database table.  This approach helps trace exactly where the code reached, what values were passed, and whether a block executed or not - even inside page-level processes and packaged procedures Why a Custom Debug Package? Works seamlessly inside Oracle APEX page processes Persists debug information even after session ends Helps trace execution flow Captures runtime values Can be turned ON/OFF dynamically Does not interrupt business logic The Package consists of:- Debug Table                         -  Stores debug messages Sequence ...
Post a Comment
Read more