APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

xmltable in IN clause report error (possibly a bug, at least really strange and suspicious behaviour

Pavel_pMar 16 2016 — edited Mar 18 2016

Hello,

I'm experiencing really unpredictable behaviour of my application (I created a testcase based on EMP+DEPT tables).

I have a multiselect list and a report that uses an underlying select:

select * from emp where deptno in(select to_number(COLUMN_VALUE) from xmltable(:P1_SELECTED_DEPTS))

The multiselect list triggers a DA that sets P1_SELECTED_DEPTS item with selected values from the select list (colons replaced by commas, so the value is for instance 10,20) and on change it refreshes the report (no matter if classic or IR). The really strange thing is that it sometimes works and sometimes not and the report shows the following error:

report error:

ORA-19112: error raised during evaluation: can not get xquery string

I tried to change a classic report to IR (without changing a thing) and suddenly it started to work as expected. Then I logged out and when I logged back in, it again started to show the above error. The next "fix" was even more funny - I replaced an item p1_selected_depts with '10,20' and it started to work as expected again, so I replaced the literal '10,20' with P1_SELECTED_DEPTS and the application continued working.

The most curious "fix" was when it stopped working again and I didn't change a thing, just added a comment above the select statement and suddenly the app worked again.

Please, could anybody from the dev team check it? I created a testcase on apex.oracle.com

workspace: testing

user: supporter

pwd: supporter1234

Application 84167 - multiselect_in

The application is currently producing the error above.

Thanks a lot,

Pavel

edit:

I gave it some more testing and following steps produce (so far) always the same result:

1) replace :P1_SELECTED_DEPTS by '10', so the select is

select * from emp where deptno in(select to_number(COLUMN_VALUE) from xmltable('10'))

2) run the page and it works (employees with deptno=10 are selected)

3) replace '10' with :P1_SELECTED_DEPTS, so we have again the original select

4) run the page and it works like a charm (employees from selected departments are being selected)