This discussion is archived
4 Replies Latest reply: Mar 25, 2013 11:51 AM by sybrand_b RSS

Apex 4.2.1 tabular form generated sql results in ora-01790

sybrand_b Guru
Currently Being Moderated
Hi,

I have a tabular detail form/report region.
The SQL for this region is
select
"ID",
"ID" ID_DISPLAY,
"CONTRIBUTOR_ID",
"CONTRIBUTOR_TYPE_ID"
from "#OWNER#"."ROLES_OF_CONTRIBUTOR"
where contributor_id=to_number(:p22_id)


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)

and barfs with ora-01790

I don't see what I can do about this.

Any help?
--
Sybrand Bakker
Senior Oracle DBA
  • 1. Re: Apex 4.2.1 tabular form generated sql results in ora-01790
    flavioc Pro
    Currently Being Moderated
    Hi,
    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.

    Flavio

    ----
    http://oraclequirks.blogspot.com
    http://www.yocoya.com
  • 2. Re: Apex 4.2.1 tabular form generated sql results in ora-01790
    Bas de Klerk Pro
    Currently Being Moderated
    Hi Sybrand,

    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.

    Regards
    Bas
  • 3. Re: Apex 4.2.1 tabular form generated sql results in ora-01790
    sybrand_b Guru
    Currently Being Moderated
    Flavio,

    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.

    Regards,

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: Apex 4.2.1 tabular form generated sql results in ora-01790
    sybrand_b Guru
    Currently Being Moderated
    Hi Bas,

    YIKES!

    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.

    Regards,

    --------------
    Sybrand Bakker
    Senior Oracle DBA

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points