Oracle Apex private interactive reports lost post import of application

Oracle Apex version 4.1 has a nasty bug where the users saved interactive reports are lost after an application release.

Oracle have suggested a workaround listed in doc id 1492091.1

Alternatively you can upgrade to 4.2 or run the following to retrieve the users reports post release.

PROMPT 'This script must be run by SYSTEM'

ACCEPT v_app_id       PROMPT 'Enter the number of the application e.g. 162 > ' 

COLUMN v_tmp NEW_VAL v_file

SELECT 'reset_interactive_reports_' || TO_CHAR(SYSDATE, 'yyyymmdd_hh24mi') || '.txt' v_tmp
FROM dual;

SPOOL &v_file

SET SERVEROUTPUT ON

BEGIN
  FOR ir IN (SELECT *
            FROM   apex_040100.wwv_flow_worksheets
            WHERE  page_id IN (
                              SELECT DISTINCT page_id
                              FROM   apex_040100.wwv_flow_worksheet_rpts
                              WHERE  flow_id = &v_app_id
                              AND    application_user != 'APXWS_DEFAULT')
            AND    flow_id = &v_app_id)
  LOOP
    dbms_output.put_line ('Processing page ' || ir.page_id);

    UPDATE apex_040100.wwv_flow_worksheet_rpts                  
    SET    worksheet_id = ir.id
    WHERE  page_id = ir.page_id
    AND    flow_id = &v_app_id;

    dbms_output.put_line ('Updated ' || SQL%ROWCOUNT);

  END LOOP;
END;
/

PROMPT 'Please issue a COMMIT'

SPOOL OFF;
Advertisements

One thought on “Oracle Apex private interactive reports lost post import of application

  1. Horatiu TAINA January 27, 2015 / 11:04 am

    Hi there!

    I’ve found this same issue for my Application Express 4.2.1.00.08

    We’ve moved it from the current machine to an Exadata one but we’ve lost private and public reports.

    When I run each separate query from the procedure you’ve built it says this: ORA-00942: table or view does not exist

    Example:
    SELECT DISTINCT page_id FROM apex_040100.wwv_flow_worksheet_rpts WHERE flow_id = 12 AND application_user != ‘APXWS_DEFAULT’;

    Probably wwv_flow_worksheet_rpts isn’t there, or I don’t know the proper user/schema. It doesn’t work with apex_040100 or apex_040200. And I don’t have ADMIN access!

    Do you have any other suggestions?

    Thank you,
    Horatiu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s