I have a tabular detail form/report region.
The SQL for this region is
Apex 4.2.1 turns this into
select NULL "CHECK$01", NULL "ID", NULL "ID_DISPLAY", :"P22_ID" "CONTRIBUTOR_ID", NULL "CONTRIBUTOR_TYPE_ID" from sys.dual union all select "CHECK$01", "ID", "ID_DISPLAY", "CONTRIBUTOR_ID", "CONTRIBUTOR_TYPE_ID" from (select NULL "CHECK$01", "ID", "ID_DISPLAY", "CONTRIBUTOR_ID", "CONTRIBUTOR_TYPE_ID" from ( select "ID", "ID" ID_DISPLAY, "CONTRIBUTOR_ID", "CONTRIBUTOR_TYPE_ID" from "PARTITUREN"."ROLES_OF_CONTRIBUTOR" where contributor_id=to_number(:p22_id)) APEX$RPTSRC order by 1)
I know that implicit conversion should be avoided at all times, but did you try to see what happens if you remove the call to TO_NUMBER?
I am just wondering if the presence of to_number is causing the parser to complain about the datatype of contributor_id which needs to be matched against the first occurrence of :P22_ID (in the first select statement) whose datatype is presumably a varchar2.
is this a form/report which was created in another version ?
Usually the fastest way to get this solved is create a copy of this page and recreate the report part which you can do in 2 minutes and see if this fixes it. Might have to do something with old settings which where never an issue but might be in 4.2.1.
Another test worth trying is to run the query in SQL*Plus replacing the bind variable with the actual value and see if the error is still there. I don't see any logical errors in the statement but just to verify it would be a good test.
So no solution for now but might be a quick way to remove or find the cause.
The problem surfaced first without the to_number, so this was my initial attempt to work around it.
And I failed.
I pulled out the SQL, replaced the :"P22_ID" by :P22_ID and it worked.
But the :"P22_ID" is generated by Apex.
The form already existed in 4.1 (I recently upgraded to keep up with apex.oracle.com) and likely in 4.0 and maybe already in 3.2.
I removed the form and created it again.
And yes, now it works.
I now have a few new unrelated issues (it is a detail form, and when I submit the detail form, I submit the page, which I don't want, as there was no change. I know the nasty solution for this: make the form independent, and callable from other forms. Also I don't see how I can display a timestamp column in APEX without implicilty converting it to text first. As the column is populated by database trigger, APEX shouldn't validate it), but THANKS.