APEX

Hello everyone,

I'm trying to create a region based on type SQL Query (Pl/Sql Function Body Returning Sql Query) since I need to choose a query dynamically, depending on a checkbox.

So far, so good - it works fine if I don't need a construct with a wildcard (%) - this is necessary to be able to search for a partial field value entered by the user... I tried to use variables to concatenate the % to the bind variables, but it still doesn't work:

Here's a code snippet:

DECLARE
q varchar2(1000);
v_day varchar2(3);
v_year varchar2(2);
v_counter varchar2(3);
v_loc varchar2(50);
v_name varchar2(50);
BEGIN
v_day := :P1_DAY||'%';
v_year := :P1_YEAR||'%';
v_counter := :P1_COUNTER||'%';
v_loc := :P1_LOC||'%';
v_name := :P1_NAME||'%';

IF :P1_CHK IS NOT NULL AND :P1_TMP IS NOT NULL THEN
q := 'select *
from my_view_v t
where not (t.special = :P1_CHK)
and not (t.special = :P1_TMP)
and t.day like v_day
and t.year like v_year
and t.cnt like v_counter
and t.loc like v_loc
and upper(t.name) like upper(v_name);';
ELSE
q := 'select *
from my_view_v;';
END IF;
RETURN q;
END;

When I have the checkboxes checked, then I get the following error back on the page:

failed to parse SQL query:
ORA-00904: "V_NAME": invalid identifier

Do I really have to use a region based on a anonymous PL/SQL Block, using cursors and htp.p to output the query result in a loop to accomplish what I'm trying, or is there a way around this problem? I would love to see this work, since the option above would mean a lot of manual formatting work for the report...

Thanks in advance,

Holger