Is it possible to evaluate SQL given as text? (Oracle APEX 21.1)-CodePudding

I have created a classic report region (REGION: REPORT_FILTER_SHOP_TYPE). That has a SQL below this.

SELECT 
  ID, SHOP_NAME, SHOP_TYPE, OPEN_YEAR, CITY
FROM SHOP_LIST;

I want to apply a filter to this table. The filter criteria will be selected from the list item. And this page have some lists. For example, if there is no filter, the SQL right above one. But if the "SHOP_TYPE" and "OPEN_YEAR" are selected, execute the SQL below.

SELECT * FROM (
  SELECT 
    ID, SHOP_NAME, SHOP_TYPE, OPEN_YEAR, CITY
  FROM SHOP_LIST
  ) S
WHERE S.SHOP_TYPE = 'BOOKSTORE' AND S.OPEN_YEAR <2010;

I can now create the compose SQL text from selected list items.

What do I need to set to display this result in REPORT_FILTER_SHOP_TYPE?

CodePudding user response：

Well, most probably not like that; why using parameters on a page if you hardcode some values into report's query? Use parameters!

Something like this:

SELECT id,
       shop_name,
       shop_type,
       open_year
  FROM shop_list
 WHERE     (   shop_type = :P1_SHOP_TYPE
            OR :P1_SHOP_TYPE IS NULL)
       AND (   open_year < :P1_OPEN_YEAR
            OR :P1_OPEN_YEAR IS NULL);