As of about 3 weeks ago, we started seeing this error popping up in several places in our team portal: ORA-01428: argument '0' is out of range.
Upon troubleshooting, we narrowed it down to a common SQL query, and we were even able to find out exactly where it is coming from. We have a where clause here:
where ((((a.new_area_id = :P63_SPECIALTY_ID)
or exists(select area_id from AREAS s where s.AREA_ID = a.new_area_id and s.PARENT_AREA = :P63_SPECIALTY_ID)
or exists(select s1.AREA_ID from AREAS s1 inner join AREAS s2 on s1.PARENT_AREA = s2.AREA_ID where s1.AREA_ID = a.new_area_id and s2.PARENT_AREA = :P63_SPECIALTY_ID)) and :P63_SUB_ID is null)
or a.new_area_id in (
select regexp_substr(:P63_SUB_ID, '[^:]+', 1, rownum) v
from dual
connect by rownum <= length(:P63_SUB_ID || ':') - length(replace(:P63_SUB_ID, ':'))
))
And when we quote out the last OR statement, the error goes away. So we know the error is stemming from this statement:
a.new_area_id in (
select regexp_substr(:P63_SUB_ID, '[^:]+', 1, rownum) v
from dual
connect by rownum <= length(:P63_SUB_ID || ':') - length(replace(:P63_SUB_ID, ':'))
For reference, the variable `:P63_SUB_ID` comes from a group of Checkboxes on the page, and when checked, the variable is populated like `31:159:163` for example when 3 boxes are checked.
We have ran this snippet on a separate SQL command it it works fine producing the result:
31
159
163
We also see that `:P63_SUB_ID` is being populated as we expect in the Session.
Again, this error only started showing up ~3 weeks ago, around the time of a recent APEX update. Does anyone know what might be causing this error, or if the update changed some functionality?
Thanks.