Hello,
Upgrating from Apex 4.1, it was working there:
The filter is:
MY_NUM_COLUMN "matches regular expression" 10|11
In higher APEX versions (I tried 20.1, 24.1), it throws an error. When I change the column in the report to VARCHAR2: (TO_CHAR(MY_NUM_COLUMN ), it works as expected.
I have compared the execution plan for NUMBER and VARCHAR2, there are the same, the filter is added like follow:
…..
and regexp_like("MY_NUM_COLUMN ",:apex$f1)
)a
)where apx$rownum<=:p$_max_rows
…….
The Predicate information from the execution plan:
If the column is NUMBER:
--
..
filter(( REGEXP_LIKE (TO_CHAR("MY_NUM_COLUMN "),:APEX$F1
..
--
If the column is VARCHAR2:
--
..
filter(( REGEXP_LIKE ("MY_NUM_COLUMN ",:APEX$F1
..
--
So I compared the content of the bind variable - with a simple value, e.g. 10 (to avoid the error , otherwise it does not captures the bind value)
select * from v$sql_bind_capture where sql_id=…
For Number, the value is: :APEX$F1 ‘NULL’ (string ‘NULL’)
For Varchar2, the value is: :APEX$F1 ‘10’ (string ‘10’)
It seems like there is a bug for the NUMBER data type.
Any idea how to workaround it (except to change the data type from NUMBER to VARCHAR2)?
Thx, Jan