Here I am giving an example to how to do search results with highlighted searched text using PL/SQL in Oracle APEX classical report.
Step 1: Create a page.
Step 2: Create a region (Type: PL/SQL Dynamic Content).
Step 3: Create a text item.
Step 4: Create a select list to define no of rows that needs to be displayed in search results. (Optional)
Step 5: Change a UI of text field item as like search item (Optional)
Appearance:
Template: Hidden
Icon: fa-search
Value Placeholder: Enter country to search.... (Your own text)
Advanced:
CSS Classes: t-Form-searchField
Custom Attributes: style="border-radius:20px;"
Source:
Type: Null
Used: Only when current value in session state is null
Step 6: Populate sample data (if required)
Step 7: Just copy and paste below code in region (Ref Step: 2)
DECLARE
c pls_integer := 0;
l_detail VARCHAR2(32767);
i pls_integer;
l pls_integer;
l_max_rows INTEGER;
BEGIN
l_max_rows := nvl(:p14_rows,1000);
FOR c1 IN (SELECT country,
capital,
latitude,
longitude ,
country_flag_flat,
updated_by,
updated_on
FROM fxgn_country_coordinates
WHERE (((instr(upper(country),upper(:p14_search)) > 0)
OR :p14_search IS NULL
) or (instr(upper(capital),upper(:p14_search)) > 0)
OR :p14_search IS NULL
)
ORDER BY country ASC
)
loop
c := c + 1;
IF c = 1 THEN
--sys.htp.p('<ul class="sSearchResultsReport">');
sys.htp.p('<div class="t-SearchResults">');
sys.htp.p('<ul class="t-SearchResults-list">');
END IF;
sys.htp.p('<li class="t-SearchResults-item">');
sys.htp.prn('<h3 class="t-SearchResults-title"><a href="#">');
--
--
--
l_detail := c1.country;
i := instr(upper(l_detail),upper(trim(:p14_search)));
IF i > 0 THEN
l := LENGTH(:p14_search);
l_detail := apex_escape.html(substr(l_detail,1,i-1))||
'<span class="highlight">'||
apex_escape.html(substr(l_detail,i,l))||'</span>'||
apex_escape.html(substr(l_detail,i+l));
ELSE
l_detail := apex_escape.html(l_detail);
END IF;
sys.htp.prn(REPLACE(c1.country_flag_flat,64,32)||' '||l_detail);
sys.htp.prn('</a></h3>');
sys.htp.prn('<div class="t-SearchResults-info">');
sys.htp.prn('<p class="t-SearchResults-desc">');
--
--
--
l_detail := c1.capital;
i := instr(upper(l_detail),upper(trim(:p14_search)));
IF i > 0 THEN
l := LENGTH(:p14_search);
l_detail := apex_escape.html(substr(l_detail,1,i-1))||
'<span class="highlight">'||
apex_escape.html(substr(l_detail,i,l))||'</span>'||
apex_escape.html(substr(l_detail,i+l));
ELSE
l_detail := apex_escape.html(l_detail);
END IF;
sys.htp.prn(l_detail);
sys.htp.prn('</p>');
--
--
--
sys.htp.prn('<span class="t-SearchResults-misc"> Latitude: ');
sys.htp.prn(apex_escape.html(c1.latitude));
sys.htp.prn('</span>');
--
--
--
sys.htp.prn('<span class="t-SearchResults-misc"> Longitude: ');
sys.htp.prn(apex_escape.html(c1.longitude));
sys.htp.prn('</span>');
--
--
--
sys.htp.prn('<span class="t-SearchResults-misc">Last Action: ');
sys.htp.prn(apex_escape.html(to_char(c1.updated_on,'DD-Mon-RRRR HH24:MI:SS'))||' '||lower(apex_escape.html(c1.updated_by)));
sys.htp.prn('</span>');
--
--
--
sys.htp.p('</div>');
sys.htp.p('</li>');
--
--
--
IF c = l_max_rows THEN
exit;
END IF;
END loop;
IF c > 0 THEN
sys.htp.p('</ul>');
sys.htp.p('</div>');
sys.htp.p('<p>'||c||' results found.</p>');
ELSE
sys.htp.p('<p>No search results.</p>');
END IF;
END;
Step 8: Add below CSS code in page inline section, which will highlight the searched text(s)
.highlight {background-color:#ffecb4;}
Happy APEXing!!!...
Comments
Post a Comment