1. Overview
This document talks about how to Display User Rating Icons Dynamically in Oracle APEX Report.
2. Technologies and Tools Used
The following technologies has been used to achieve the expected output.
- Oracle APEX
- PL/SQL
- CSS, HTML
3. Use Case
Assume that there is a requirement to Display User Rating Icons Dynamically in a report. We can do it manually by creating a PL/SQL function. Here I have showcased hoe to do it dynamically using PL/SQL function and HTML.
This document explains how to achieve this requirement.
4. Architecture
Following steps explains in detail,
Step 1: Create a new blank page to display the report with the user rating icons dynamically.
Step 2: Function Call: Create a function get_user_ratings. If you want to render it in multiple places, it would be good practice to put this code in a package and call it in your query.
Sample function in a package called FXGN_GENERAL
Sample Code:
create or replace FUNCTION get_user_ratings(p_value IN NUMBER)
RETURN VARCHAR2
IS
l_return VARCHAR2 (4000);
l_checked VARCHAR2(240) := ‘<span class=”fa fa-star” style=”color:orange;”></span>’;
l_un_checked VARCHAR2(240) := ‘<span class=”fa fa-star” style=”color:grey;”></span>’;
l_max_value NUMBER := 5;
l_remaining NUMBER;
BEGIN
IF p_value BETWEEN 1 AND 5 THEN — Check given values are in the range
l_remaining := l_max_value – p_value;
/* Add checked user ratings */
FOR i IN 1..p_value
loop
l_return := l_return||’ ‘||l_checked;
END loop;
/* Add unchecked user rating if remaining is greater than Zero */
IF l_remaining > 0 THEN
FOR j IN 1..l_remaining
loop
l_return := l_return||’ ‘||l_un_checked;
END loop;
END IF;
ELSE
l_return := NULL;
END IF;
RETURN l_return;
END get_user_ratings;
Step 4: Create a new classical report or interactive report whichever you want, then you have to write your query to display the report.
Sample Code:
SELECT fund_id,
fund_name,
to_char(start_date, ‘DD-Mon-RRRR’) start_date,
get_user_ratings(user_rating) user_rating
FROM fxgn_fund_progress
ORDER BY fund_id ASC;
Step: Go to report attribute user_rating and set Escape special characters = Yes.
5. Screen Shot
Output:
Here I have created a Display User Rating Icons Dynamically in Oracle APEX Report. The below is the output for the test application.
Sample 2:
