ORA-01445 in Interactive Report when importing Apex 3.2 app into Apex 5.0

Just a quick note about an error I encountered when importing an old Apex 3.2 (!) app into Apex 5.0.2.

Using Apex 5.0.2, I have imported an application which ran fine under Apex 3.2, that had an interactive report with the following query:

select c001, c002, c003, c004, c005, c006, c007, c008, c009, c010,

  c011, c012, c013, c014, c015, c016, c017, c018, c019, c020,

  c021, c022, c023, c024, c025, c026, c027, c028, c029, c030,

  c031, c032, c033, c034, c035

from apex_collections

where collection_name = 'USER_QUERY'

The collection is populated by PL/SQL code before the query runs.

Now, in Apex 5.0.2, when I run the page, I get the following error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 

Running the page in debug mode shows the following:

...Execute Statement: select 

  apxws_row_pk,

  "C001",

  "C002",

  "C003",

  "C004",

  "C005",

  "C006",

  "C007",

  "C008",

  "C009",

  "C010",

  "C011",

  "C012",

  "C013",

  "C014",

  "C015",

  "C016",

  "C017",

  "C018",

  "C019",

  "C020",

  "C021",

  "C022",

  "C023",

  "C024",

  "C025",

  "C026",

  "C027",

  "C028",

  "C029",

  "C030",

  "C031",

  "C032",

  "C033",

  "C034",

  "C035",

  count(*) over () as apxws_row_cnt

 from (

select * from (select b.ROWID apxws_row_pk, b.* from (select * from (

select c001, c002, c003, c004, c005, c006, c007, c008, c009, c010,

  c011, c012, c013, c014, c015, c016, c017, c018, c019, c020,

  c021, c022, c023, c024, c025, c026, c027, c028, c029, c030,

  c031, c032, c033, c034, c035

from apex_collections

where collection_name = 'USER_QUERY'

) ) b) r

) r where rownum <= to_number(:APX~

Logging exception:

Sqlerrm: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325

ORA-06512: at "SYS.WWV_DBMS_SQL", line 464

ORA-06512: at "SYS.WWV_DBMS_SQL", line 475

ORA-06512: at "APEX_050000.WWV_FLOW_DYNAMIC_EXEC", line 416

Logging exception:

Sqlerrm: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325

ORA-06512: at "SYS.WWV_DBMS_SQL", line 464

ORA-06512: at "APEX_050000.WWV_FLOW_DYNAMIC_EXEC", line 461

ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET_STANDARD", line 471

Logging exception:

Sqlerrm: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Backtrace: ORA-06512: at "SYS.DBMS_SYS_SQL", line 1325

ORA-06512: at "SYS.WWV_DBMS_SQL", line 464

ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET", line 4277

ORA-06512: at "APEX_050000.WWV_FLOW_WORKSHEET", line 11471

And indeed, if I just isolate the inner part of the IR query that Apex generates, and run this in the SQL Workshop in Apex:

select b.ROWID apxws_row_pk, b.* from (select *  from (

select c001, c002, c003, c004, c005, c006, c007, c008, c009, c010,

  c011, c012, c013, c014, c015, c016, c017, c018, c019, c020,

  c021, c022, c023, c024, c025, c026, c027, c028, c029, c030,

  c031, c032, c033, c034, c035

from apex_collections

where collection_name = 'USER_QUERY'

) ) b

I get the same error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

I thought this was a bug, but then I checked the "Link Column" attribute of the Interactive Report. It was set to "Link to Single Row View" and the "Uniquely Identify Rows by" was set to "ROWID". That must be why Apex wraps my query with an outer query that adds the rowid, which then fails because my original query is not "key-preserved". (Funny that the single row view worked in Apex 3, but I guess the internal implementation of the outer query changed between versions.)

In my case I did not really need the single row view, so I just disabled it (set "Link Column" attribute to "Exclude Link Column"). Your case might be different, so you would have to rewrite the query or specify a unique key column instead of rowid.