Search Results with Highlighted Searched Text using PL/SQL in Oracle APEX Classical Report

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)||' &nbsp;'||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;}

Output: (Search by Country)

Output: (Search by Capital)

The demo is here

That's it.

Happy APEXing!!!...