This discussion is archived
5 Replies Latest reply: Jun 24, 2011 11:57 AM by 871289 RSS

ORA-01446 on Interactive query

CoreyT Newbie
Currently Being Moderated
All,

I am having trouble creating an interactive report using a hierarchical query - which I need. The error reported is ORA-01446 indicating that APEX is wrapping this in another query trying to get a ROWID. It does work as an SQL report but not as an interactive one causing the loss of functionality.

Has anybody experienced this before or know anyway around it.

Thanks,
Corey

-----
To illustrate, try the following from any SQL editor:

+Create a tes

create table scratch (a number, p_a number);



Execute this query - this should run fine in SQL but fails in APEX for ORA-01446*
select * from scratch
connect by prior a = p_a;

Execute the same query wrapped with a ROWID query in SQLPLUS and you get the same error as in APEX interactive report*
select rowid from
(
select * from scratch
connect by prior a = p_a
);


-----
Actual error message:

1 error has occurred <ul><li>The report query needs a unique key to identify each row. The supplied key cannot be used for this query. Please edit the report attributes to define a unique key column. ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.</li>
</ul>
  • 1. Re: ORA-01446 on Interactive query
    60437 Employee ACE
    Currently Being Moderated
    If you don't need the single-row view, uncheck that option and it should work.

    Scott
  • 2. Re: ORA-01446 on Interactive query
    438381 Guru
    Currently Being Moderated
    Corey:

    Does the table that you are using in the query have a primary key/Unique key ?
    If so, you can have APEX use that column as the column instead of 'rowid' to uniquely identify a row in the table

    Varad

    Edited by: varad acharya on Nov 21, 2008 4:18 PM
  • 3. Re: ORA-01446 on Interactive query
    CoreyT Newbie
    Currently Being Moderated
    Thank you both so much for your input. Both worked great. I did the following with success:

    Based on Scott suggestion, initially, I disabled the single-row view and that allowed me to enter the query in interactive mode.

    Then based on Varad suggestion, I took it a little further and added an alias rowid column (from the inner joined table in the from-clause query). I re-enabled the single-row view and specified this aliased rowid and it works well still.

    Thanks,
    Corey
  • 4. Re: ORA-01446 on Interactive query
    804111 Newbie
    Currently Being Moderated
    Corey,

    Thanks for suggesting the solution. This has been giving me fits while developing my first Apex application. I wasn't able to figure out when I would get the error or how to fix it. I do need the single row views as an application "standard". Since it took me a while to figure out how to do what you suggested, for others, here's specifically what I did.

    In an interactive report linking several tables, with subqueries counting child entities with outer joins, I added as the first column in the SELECT statement "acr.rowid MY_ROWID,", where the primary table alias name is "acr".

    I also added "acr.rowid MY_ROWID," to the GROUP BY list.

    Then in the Report Attributes > Link Column, I linked to a single row view, chose Unique Column, and entered MY_ROWID and everything worked fine.

    Tried it in another report that used UNION, and it worked there, too.

    I may have run into trouble using lower case in various places. I'm still getting used to how Apex and Oracle SQL are case sensitive some places.

    Skip
  • 5. Re: ORA-01446 on Interactive query
    871289 Newbie
    Currently Being Moderated
    Skip, thank you for adding the additional description and information. I had been beating my head against a wall on this one. Solution worked perfectly.

    -Sean

    Edited by: user9030474 on Jun 24, 2011 11:56 AM

    Edited by: user9030474 on Jun 24, 2011 11:57 AM

Legend

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