ORA-01446 on Interactive query
CoreyTNov 21 2008 — edited Jun 24 2011All,
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>