I am trying to pass REGEXP_SUBSTR with match_parameters to a dynamic sql but it doesn't seem to work as expected.
Here's an example
if I run
SELECT INSTR(REGEXP_SUBSTR(&PROMPT, '^(.*\s+)?HLD(\s+.*)?$'),'HLD') FROM DUAL;
The query works perfectly
but when I compile a package to generate a dynamic SQL and I include the match_parameters they don't seem to work.
The dynamic SQL where clause looks like this
create or replace package body MY_PK is
function.....
begin....
execute immediate ..;
l_sqltext := l_sqltext || '
SELECT *
FROM....
WHERE...
OR CASE WHEN ''HLD'' IN ('||CASE WHEN INSTR(REGEXP_SUBSTR(SHELF, '^(.*\s+)?HLD(\s+.*)?$'),'HLD') = 0 THEN 'NULL' ELSE '''HLD''' END ||') THEN abs(hlds) ELSE -1 END > 0
...';
Any advice?
Thanks
Youssef