Thursday, June 11, 2009

Quickly Modify APEX Interactive Report Options

If your APEX application has many Interactive Reports (IR) it can be tedious to configure IR features for each report, and their columns, such as filtering, highlighting etc.

Since APEX resides within the database there's a quick way to manage all of your IRs. Please note this is not supported by Oracle so please be aware of this.

First login to the database as SYS or SYSTEM.

Update Interactive Report options: You can modify more options by looking at the table definition for wwv_flow_worksheets

UPDATE apex_030200.wwv_flow_worksheets -- Where apex_030200 is your current APEX instance
SET allow_report_saving = 'Y', -- Configure options as required
show_finder_drop_down = 'N',
show_display_row_count = 'Y',
show_search_bar = 'N',
show_search_textbox = 'Y',
show_actions_menu = 'Y',
show_select_columns = 'N',
show_sort = 'N',
show_filter = 'Y',
show_control_break = 'Y',
show_highlight = 'Y',
show_computation = 'N',
show_aggregate = 'N',
show_chart = 'Y',
show_flashback = 'N',
show_reset = 'Y',
show_download = 'Y',
show_help = 'N'
WHERE flow_id = :app_id
AND page_id = :app_page_id -- Remove this predicate to push changes for all IRs

Update Interactive Report Columns:

UPDATE apex_030200.wwv_flow_worksheet_columns
SET allow_sorting = 'Y',
allow_filtering = 'N',
allow_ctrl_breaks = 'Y',
allow_aggregations = 'N',
allow_computations = 'Y',
allow_charting = 'Y'
WHERE flow_id = :app_id
AND page_id = :app_page_id;


  1. Hi Martin,

    Just to highlight your comment -

    >Please note this is not supported by Oracle

    It goes a bit deeper than just this method not being supported by Oracle.

    If you perform this type of direct DML on the underlying repository tables yourself, you will likely not be supported by Oracle at all. In other words, you have just made your entire instance unsupported (even for issues unrelated to this update).

    I don't work for Oracle, so this is just my interpretation of how it works, however I would urge anyone who wanted to directly update the metadata tables on anything other than a 'playpen' system to seriously think through the consequences of doing so.

    Just my thoughts,


  2. Hi John,

    I agree that this will put the application in an unsupported state. I double checked the code that is run when you update an IR and it seems to be doing the Y/N updates:

    SELECT process_name,
    FROM apex_application_page_proc
    WHERE application_id = 4000
    AND page_id = 601
    AND LOWER (process_point) LIKE '%on submit%'
    ORDER BY execution_sequence

    I've submitted a request for a procedure to be available for developers to do this