How do I ADD a link to display in Classic Report via SQL

 Adding links to Classic Reports in Oracle APEX via SQL is a powerful way to enhance user navigation and interactivity within your applications. By embedding HTML anchor tags directly in your SQL queries, you can create dynamic, clickable links that direct users to related pages, external websites, or specific actions based on the data in your report. This method offers precise control over the link’s appearance and destination, making your reports more functional and user-friendly without relying on additional page components or plugins.

In Oracle APEX, adding a link directly in a Classic Report via SQL allows you to provide interactive navigation inside your report rows. This is done by including HTML anchor (<a>) tags in the SQL query's SELECT statement, which generate clickable links in the report output. These links can redirect users to other application pages, external URLs, or trigger specific actions based on the row data.

To add a link in a Classic Report using SQL, follow these detailed steps:

1. Open Your Application Builder
   Log in to Oracle APEX and open the application where you want to add the Classic Report.

2. Create or Edit a Classic Report
   Navigate to the page with the Classic Report or create a new Classic Report region.

3. Modify the SQL Query
   Edit the SQL query for your Classic Report to include a column with an HTML link. For example:

   SELECT
     EMPLOYEE_ID,
     FIRST_NAME,
     LAST_NAME,
     '<a href="' || apex_util.prepare_url('f?p=&APP_ID.:10:&APP_SESSION.:NO::P10_EMPLOYEE_ID:' || EMPLOYEE_ID) || '">View Details</a>' AS EMPLOYEE_LINK
   FROM EMPLOYEES
   

   Explanation of the code:

   • The '<a href="...' builds an HTML anchor tag.

   • apex_util.prepare_url ensures the URL is correctly encoded and safe.

   • Replace 10 with the target page number where you want to send users.

   • The URL passes the EMPLOYEE_ID as a request or page item parameter (P10_EMPLOYEE_ID).

   • The alias EMPLOYEE_LINK becomes a clickable column in the report.

4. Set the Column to Display as HTML
   After saving the query, go to the Classic Report attributes, find the column that contains the link (EMPLOYEE_LINK in the example), and set its Escape Special Characters property to No. This allows the HTML code to render as a link rather than display as plain text.

5. Save and Run the Page
   Save your changes and run the page to see clickable links in your Classic Report rows. Clicking the link will redirect the user to the specified page with the passed parameter.

6. Customize Further
   You can customize link text, pass multiple parameters, or use conditional logic within your SQL to generate different links per row.

Additional Tips:

• Use apex_util.prepare_url to handle URL encoding and session management properly.

• Always test the links to ensure they direct to the correct page and carry the right parameters.

• Avoid SQL injection by properly handling input values if you build dynamic queries.

• You can combine this with Dynamic Actions or other APEX features to create rich interactive reports.

By embedding links in your Classic Report SQL, you give users intuitive navigation options directly within their data views, improving user experience and application flow.

Example

Adding a Link to Display in a Classic Report via SQL in Oracle APEX

In Oracle APEX, Classic Reports provide a flexible way to display data, and you can enhance them by adding clickable links. These links allow users to navigate to different pages, open modal dialogs, or trigger specific actions. This tutorial will explain how to add links to a Classic Report using SQL, different methods to format them, and best practices for usability.

Why Add Links in Classic Reports?

Adding links to a Classic Report enhances interactivity by allowing users to:

• Navigate to another APEX page while passing parameters.

• Open a modal dialog with detailed information.

• Execute JavaScript or PL/SQL processes.

• Provide external references related to the displayed data.

Method 1: Using APEX_UTIL.PREPARE_URL

The apex_util.prepare_url function ensures that session information is included in the URL for security and proper navigation.

Example of adding an Edit link that navigates to another page:

SELECT 

    empno, 

    ename, 

    job,

    '<a href="' || apex_util.prepare_url('f?p=&APP_ID.:10:&SESSION.::NO::P10_EMPNO:' || empno) || 

    '" class="edit-link">Edit</a>' AS edit_link

FROM emp

In this query:

• The URL navigates to Page 10 of the application.

• The empno value is passed as a parameter to P10_EMPNO.

• The apex_util.prepare_url function ensures proper session handling.

• The class="edit-link" allows custom styling via CSS.

Method 2: Using APEX_LINK for Cleaner Code

The apex_link function simplifies link generation without manually writing HTML.

SELECT 

    empno, 

    ename, 

    job,

    apex_link('f?p=&APP_ID.:10:&SESSION.::NO::P10_EMPNO:' || empno, 'Edit') AS edit_link

FROM emp

This method:

• Generates a properly formatted APEX link.

• Reduces the need for HTML concatenation in SQL.

Method 3: Using HTML Anchor Tags Directly

If session security is not a concern, links can be created manually using basic HTML.

SELECT 

    empno, 

    ename, 

    job,

    '<a href="f?p=100:10:&SESSION.::NO::P10_EMPNO:' || empno || 

    '" target="_blank">View Details</a>' AS details_link

FROM emp

This example:

• Directly creates an <a> tag with the application ID 100.

• Opens the target page in a new tab using target="_blank".

Method 4: Creating Links for External URLs

To link to an external site, concatenate the URL inside the SQL query.

SELECT 

    empno, 

    ename, 

    job,

    '<a href="https://www.example.com/employee/' || empno || 

    '" target="_blank" class="external-link">Profile</a>' AS profile_link

FROM emp

This allows users to access external pages related to the report data.

Enhancing Links with CSS

Applying CSS styles makes links more visually appealing.

.edit-link {

    color: #007bff;

    text-decoration: none;

    font-weight: bold;

}

.edit-link:hover {

    text-decoration: underline;

    color: #0056b3;

}

.external-link {

    color: green;

    font-weight: bold;

}

Triggering JavaScript Actions with Links

Instead of navigating to another page, you can execute JavaScript functions when clicking a link.

Modify the SQL query to include an onclick event:

SELECT 

    empno, 

    ename, 

    job,

    '<a href="#" onclick="showEmployeeDetails(' || empno || ')" class="details-link">View</a>' AS details_link

FROM emp

Define the JavaScript function in the Page JavaScript section:

function showEmployeeDetails(empId) {

    alert("Showing details for Employee ID: " + empId);

}

This method:

• Prevents full-page reloads.

• Allows data retrieval via AJAX without navigating to another page.

Opening a Modal Dialog with a Link

APEX supports modal dialogs for viewing details without leaving the page.

Modify the SQL query to use a modal page:

SELECT 

    empno, 

    ename, 

    job,

    '<a href="f?p=&APP_ID.:20:&SESSION.::NO::P20_EMPNO:' || empno || 

    '" class="open-modal" data-id="' || empno || '">Details</a>' AS modal_link

FROM emp

Ensure Page 20 is set as a modal dialog in APEX settings.

Best Practices for Adding Links in Classic Reports

• Use apex_util.prepare_url for security and proper session handling.

• Always pass primary keys (e.g., empno) when linking to another page.

• Apply CSS classes for consistent styling and hover effects.

• Use JavaScript or modal dialogs for smoother navigation without full page reloads.

• When linking to external sites, use target="_blank" to open them in a new tab.

Adding links to Classic Reports in APEX improves user navigation and interactivity. Whether linking to another page, executing JavaScript, or opening a modal dialog, different methods can be used based on the requirements. Using APEX utilities, HTML links, CSS, and JavaScript together ensures a seamless and visually appealing experience.

COLUMN NAME	IDENTIFICATION TYPE
ID	PLAIN TEXT
LINK_CLASS	PLAIN TEXT
LINK	PLAIN TEXT
ICON_CLASS	PLAIN TEXT
LINK_ATTR	PLAIN TEXT
ICON_COLOR_CLASS	PLAIN TEXT
LIST_CLASS	PLAIN TEXT
LIST_TITLE	PLAIN TEXT
LIST_TEXT	PLAIN TEXT
LIST_BADGE	PLAIN TEXT

select "ID",

    null LINK_CLASS,

    apex_page.get_url(p_items => 'P17_ID', p_values => "ID") LINK,

    null ICON_CLASS,

    null LINK_ATTR,

    null ICON_COLOR_CLASS,

    case when coalesce(:P17_ID,'0') = "ID"

      then 'is-active' 

      else ' '

    end LIST_CLASS,

    (substr("LOCATION_CODE", 1, 50)||( case when length("LOCATION_CODE") > 50 then '...' else '' end )) LIST_TITLE,

    (substr("LOCATION_NAME", 1, 50)||( case when length("LOCATION_NAME") > 50 then '...' else '' end )) LIST_TEXT,

    null LIST_BADGE

from "LOCATION" x

where (:P17_SEARCH is null

        or upper(x."LOCATION_CODE") like '%'||upper(:P17_SEARCH)||'%'

        or upper(x."LOCATION_NAME") like '%'||upper(:P17_SEARCH)||'%'

    )

order by "LOCATION_CODE"

In conclusion, adding links through SQL in Classic Reports is an effective technique to improve user experience by integrating seamless navigation options within your data displays. This approach is flexible, easy to implement, and maintains performance by leveraging SQL’s inherent capabilities. By mastering this method, Oracle APEX developers can create richer, more interactive reports that guide users efficiently through their applications.