asked on

Oracle APEX - custom search for a classic report

I want to add a search to my classic report so I added a textbox and a button on my page, above the report and I added a process to the page, executing PL/SQL code:

    DECLARE
        v_sql varchar2(4000);
    BEGIN
	IF :P3_SEARCH IS NOT NULL THEN
  		v_sql := q'[select field1, field2, field3
			   FROM Table1
			   WHERE category_id = ]' ||TO_NUMBER(:P3_CATEGORY)||
			   q'[ AND (function(id) LIKE '%]'||:P3_SEARCH||| 
    			   q'[%' OR field2 LIKE '%]' ||:P3_SEARCH|| 
			   q'[%' OR field3 LIKE '%]' ||:P3_SEARCH|| q'[%')]';
  	ELSE
  		v_sql := q'[select field1, field2, field3
			   FROM Table1
			   WHERE category_id = ]' ||TO_NUMBER(:P3_CATEGORY)|| q'[]';
        END IF;
  
      apex_debug.MESSAGE('my query is: ' || v_sql);      

        --   RETURN v_sql;
    END;

Open in new window

I have two issues - my resulting query does not come out as expected - `%` signs disappear and instead of

    select field1, field2, field3
	FROM Table1
	WHERE category_id = 4
	AND (function(id) like '%test%' OR field2 like '%test%' OR field3 like '%test%')

Open in new window

I get

    select field1, field2, field3
	FROM Table1
	WHERE category_id = 4
	AND (function(id) like 'test' OR field2 like 'test' OR field3 like 'test')

Open in new window

And I also cannot figure out how do I switch the source sql for my report region when the search button is clicked. Can anyone help?

Helena Marková

I am not familiar with APEX, so this is only a tip.I would try LIKE '''%]'''
v_sql := q'[select field1, field2, field3
                   FROM Table1
                   WHERE category_id = ]' ||TO_NUMBER(:P3_CATEGORY)||
                   q'[ AND (function(id) LIKE '''%]'''||:P3_SEARCH|||
                   q'[%' OR field2 LIKE'''%]''' ||:P3_SEARCH||
                   q'[%' OR field3 LIKE '''%]''' ||:P3_SEARCH|| q'[%')]';

MarkusId

Hi,

1. How do you check the output? As a %-sign can have a special meaning in HTML, it could be that this is filtered by the APEX_DEBUG-Package (i.e., the query is correct, but the output is wrong).
2. What you are looking for is "Report based on function" (see https://www.oracle.com/technetwork/testcontent/dynamic-report-092026.html). Basically, instead of creating the report query in a page process, you can do it directly in the region code.
3. May I suggest to do this in another way, as creating a query this type is not very database friendly, as you are replacing the binds with fixed values, therefore having to parse a query for each search term. I suppose, you could use something like this instead directly as region query:

select field1, field2, field3
			   FROM Table1
			   WHERE category_id = TO_NUMBER(:P3_CATEGORY)
			      AND (function(id) LIKE '%' || :P3_SEARCH || '%' 
                                  OR field2 LIKE '%' ||:P3_SEARCH|| '%' 
                                  OR field3 LIKE '%' ||:P3_SEARCH|| '%')
                              AND :P3_SEARCH IS NOT NULL
             UNION
             select field1, field2, field3
			   FROM Table1
			   WHERE category_id = TO_NUMBER(:P3_CATEGORY)
                               AND :P3_SEARCH IS NULL

Open in new window

This way, whatever you are entering, the database does not need to reparse the query for each search term. Do not forget to give the items as page items to submit in the region.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.