Thursday, January 14, 2016

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.


2 comments:

Stew said...

Morten,

Thanks for the tip! This solved my application's problem with this error too.

- Stew

Unknown said...

Thanks Morten, resolved my error.