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.

Apex Interactive Report Tip #1: Aggregating numbers in string columns

I have been working on an Interactive Report based on a collection recently, to work around the fact that the SQL query for Interactive Reports must be static, and my requirement was to run different queries based on user input.

One problem is that since all columns in a collection are varchars (strings), the built-in aggregation feature of Interactive Reports does not work (it only allows you to aggregate on numbers, which is quite sensible, I guess). Normally you could always convert the string column to a number directly in the underlying query, but since my queries were dynamic (the whole point of using collections), that was not an option.

However, after some fiddling around I discovered that you can aggregate based on computed columns.

So, let's say we have this "transaction amount" column in our report, which consists of numbers but which Apex interprets as a string since it is retrieved from a varchar2 column in the apex_collections view:



Use the "Compute" menu item of the Interactive Report to create a new column, using the TRUNC function (for some reason, the TO_NUMBER function is not available from the list of functions, but TRUNC works -- just remember to include the number of decimals as the second parameter if you need decimals) and setting the appropriate numeric format mask:



We can now see that the new column has been added as a numeric column, since it is right-aligned instead of left-aligned (and also with a nice format mask):



The new column is now available under the "Aggregate" menu item of the Interactive Report, so we can create a Sum of the numbers:




After adding a "Control Break" to the report, we can see that the aggregation works:



Did I mention that I really like Interactive Reports? :-)