This discussion is archived
4 Replies Latest reply: Nov 16, 2012 7:45 AM by cbcho RSS

Column in apex_application_page_ir_rpt.report_columns not updated. Defect?

JasperA Newbie
Currently Being Moderated
Hi everyone,

I discovered the following:

I created an IR in my application. First I define the following query for the IR:

SELECT *
FROM apex_application_page_ir_rpt
WHERE application_id = 22333
AND page_id = 2

That works fine.

Then, I change my region source to exclude just one column (the last one: last_updated_by):

SELECT WORKSPACE,WORKSPACE_DISPLAY_NAME,APPLICATION_ID,APPLICATION_NAME,PAGE_ID,INTERACTIVE_REPORT_ID,REPORT_ID,APPLICATION_USER,REPORT_NAME,REPORT_ALIAS,SESSION_ID,BASE_REPORT_ID,REPORT_DESCRIPTION,DISPLAY_SEQUENCE,REPORT_VIEW_MODE,STATUS,CATEGORY_ID,REPORT_TYPE,REPORT_LINK_EXAMPLE,DISPLAY_ROWS,REPORT_COLUMNS,SORT_COLUMN_1,SORT_DIRECTION_1,SORT_COLUMN_2,SORT_DIRECTION_2,SORT_COLUMN_3,SORT_DIRECTION_3,SORT_COLUMN_4,SORT_DIRECTION_4,SORT_COLUMN_5,SORT_DIRECTION_5,SORT_COLUMN_6,SORT_DIRECTION_6,BREAK_ON,BREAK_ENABLED_ON,SUM_COLUMNS_ON_BREAK,AVG_COLUMNS_ON_BREAK,MAX_COLUMNS_ON_BREAK,MIN_COLUMNS_ON_BREAK,MEDIAN_COLUMNS_ON_BREAK,COUNT_COLUMNS_ON_BREAK,COUNT_DISTNT_COL_ON_BREAK,FLASHBACK_MINUTES,FLASHBACK_ENABLED,CHART_TYPE,CHART_LABEL_COLUMN,CHART_LABEL_TITLE,CHART_VALUE_COLUMN,CHART_AGGREGATE,CHART_VALUE_TITLE,CHART_SORT_ORDER,CREATED_ON,CREATED_BY,LAST_UPDATED_ON
FROM apex_application_page_ir_rpt
WHERE application_id = 22333
AND page_id = 2

However, in the apex datadictionary view "apex_application_page_ir_rpt", in the column "report_columns" it still shows the column "last_updated_by".

We run APEX 4.1 on production, but I see on my OTN apex application the same problem occurs. As you can see here:

http://apex.oracle.com/pls/apex/f?p=22333:2

Can anybody tell me if this is a known defect?

The only solution is to completely remove the region and define it again. But this is something we do not want to do, because we end up with all new report id's.

Edit: when I use APEX_IR.GET_REPORT to generate the SQL on 4.2, the column is not selected:

select "WORKSPACE", "WORKSPACE_DISPLAY_NAME", "APPLICATION_ID", "APPLICATION_NAME", "PAGE_ID", "INTERACTIVE_REPORT_ID", "REPORT_ID", "APPLICATION_USER", "REPORT_NAME", "REPORT_ALIAS", "SESSION_ID", "BASE_REPORT_ID", "REPORT_DESCRIPTION", "DISPLAY_SEQUENCE", "REPORT_VIEW_MODE", "STATUS", "CATEGORY_ID", "REPORT_TYPE", "REPORT_LINK_EXAMPLE", "DISPLAY_ROWS", "REPORT_COLUMNS", "SORT_COLUMN_1", "SORT_DIRECTION_1", "SORT_COLUMN_2", "SORT_DIRECTION_2", "SORT_COLUMN_3", "SORT_DIRECTION_3", "SORT_COLUMN_4", "SORT_DIRECTION_4", "SORT_COLUMN_5", "SORT_DIRECTION_5", "SORT_COLUMN_6", "SORT_DIRECTION_6", "BREAK_ON", "BREAK_ENABLED_ON", "SUM_COLUMNS_ON_BREAK", "AVG_COLUMNS_ON_BREAK", "MAX_COLUMNS_ON_BREAK", "MIN_COLUMNS_ON_BREAK", "MEDIAN_COLUMNS_ON_BREAK", "COUNT_COLUMNS_ON_BREAK", "COUNT_DISTNT_COL_ON_BREAK", "FLASHBACK_MINUTES", "FLASHBACK_ENABLED", "CHART_TYPE", "CHART_LABEL_COLUMN", "CHART_LABEL_TITLE", "CHART_VALUE_COLUMN", "CHART_AGGREGATE", "CHART_VALUE_TITLE", "CHART_SORT_ORDER", "CREATED_ON", "CREATED_BY", "LAST_UPDATED_ON" from ( select * from ( SELECT WORKSPACE,WORKSPACE_DISPLAY_NAME,APPLICATION_ID,APPLICATION_NAME,PAGE_ID,INTERACTIVE_REPORT_ID,REPORT_ID,APPLICATION_USER,REPORT_NAME,REPORT_ALIAS,SESSION_ID,BASE_REPORT_ID,REPORT_DESCRIPTION,DISPLAY_SEQUENCE,REPORT_VIEW_MODE,STATUS,CATEGORY_ID,REPORT_TYPE,REPORT_LINK_EXAMPLE,DISPLAY_ROWS,REPORT_COLUMNS,SORT_COLUMN_1,SORT_DIRECTION_1,SORT_COLUMN_2,SORT_DIRECTION_2,SORT_COLUMN_3,SORT_DIRECTION_3,SORT_COLUMN_4,SORT_DIRECTION_4,SORT_COLUMN_5,SORT_DIRECTION_5,SORT_COLUMN_6,SORT_DIRECTION_6,BREAK_ON,BREAK_ENABLED_ON,SUM_COLUMNS_ON_BREAK,AVG_COLUMNS_ON_BREAK,MAX_COLUMNS_ON_BREAK,MIN_COLUMNS_ON_BREAK,MEDIAN_COLUMNS_ON_BREAK,COUNT_COLUMNS_ON_BREAK,COUNT_DISTNT_COL_ON_BREAK,FLASHBACK_MINUTES,FLASHBACK_ENABLED,CHART_TYPE,CHART_LABEL_COLUMN,CHART_LABEL_TITLE,CHART_VALUE_COLUMN,CHART_AGGREGATE,CHART_VALUE_TITLE,CHART_SORT_ORDER,CREATED_ON,CREATED_BY,LAST_UPDATED_ON FROM apex_application_page_ir_rpt WHERE application_id = 22333 AND page_id = 2 ) r ) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

So apperently it's not using the same data dictionary I use.

Edited by: Voxie2 on Nov 14, 2012 11:01 AM
  • 1. Re: Column in apex_application_page_ir_rpt.report_columns not updated. Defect?
    Tom Petrus Expert
    Currently Being Moderated
    I can reproduce this, in 4.1. Put a select in the source, run page, save primary default. Select on the view and see the columns for APXWS_DEFAULT (primary) and also your own session (which will just have copied the primary report settings). Now alter the sql to remove a column which was displayed in the report. Querying the view again shows the columns are still there. However, display is correct.
    I assume this is because in apex_application_page_ir_col the column is actually removed, and thus a column in the _rpt would not be able to retrieve metadata, which probably explains that everything still works alright.
    Saving the report again will alter the columns in the _rpt view only when you went through "Select columns" (just open it, hit apply, then save primary report). If you don't and you would add the column again to the source, the column will also be displayed again in the report, as though it never was gone.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 2. Re: Column in apex_application_page_ir_rpt.report_columns not updated. Defect?
    cbcho Journeyer
    Currently Being Moderated
    Voxie2 / Tom,

    Thank you for reporting. I filed bug 15874222 regarding the issue. The report_columns is used to display columns in the order user defined. When IR runs to come up with runtime query, it validates against real column information in apex_application_page_ir_col. So, this is why you are not seeing any error nor not seeing the column when APEX_IR.GET_REPORT is used. However, having removed column in report_columns value is not good, so we will fix this soon.

    Regards,
    Christina
  • 3. Re: Column in apex_application_page_ir_rpt.report_columns not updated. Defect?
    JasperA Newbie
    Currently Being Moderated
    Hi,

    I can't find this bug in the Oracle support system?
  • 4. Re: Column in apex_application_page_ir_rpt.report_columns not updated. Defect?
    cbcho Journeyer
    Currently Being Moderated
    Hi Jasper,

    The bug is published now. You should be able to find it in the support.

    Regards,
    Christina

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points