When I load a page with a tabular form built on a view of a collection in APEX 5, I am getting an ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table error.
I create a view
CREATE OR REPLACE FORCE VIEW test_coll_vw
(
c001,
c002,
c003,
c004,
seq_id
)
AS
SELECT TO_NUMBER (c001),
c002,
TO_NUMBER (c003),
TO_NUMBER (c004),
seq_id
FROM apex_collections
WHERE collection_name = 'TEST'
Then create a tabular form on the view with primary key of SEQ_ID and Primary Key source of Existing Trigger through the wizard.
The debug info shows the statement that is failing is:
select NULL "CHECK$01", NULL "SEQ_ID", NULL "C001", NULL "C002", NULL "C003", NULL "C004", NULL "APEX$ROWID" from sys.dual union all select "CHECK$01", "SEQ_ID", "C001", "C002", "C003", "C004", "APEX$ROWID" from(select NULL "CHECK$01", "SEQ_ID", "C001", "C002", "C003", "C004", "ROWID" "APEX$ROWID" from ( select
"SEQ_ID",
"C001",
"C002",
"C003",
"C004"
from "WPCAMS_APP"."TEST_COLL_VW"
)APEX$RPTSRC
order by 1, 7
).
These tabular forms worked up to 4.2, but they seem to fail in APEX 5 for upgraded pages or newly created one. The generated query is the same between versions, but it looks like the underlying apex_collections view has changed between versions and is causing the problem.
Does anyone have a solution or workaround?
Thanks