Tuesday, July 20, 2010

Where Did My Saved Interactive Reports Go?

A problem I've seen reported numerous times from customers is that users' saved (or customized) interactive reports are missing after they import a new version of their application. This is a problem we've known about for a while with no adequate remedy. However, given the introduction of the APEX_APPLICATION_INSTALL API in Application Express 4.0, I can offer a solution. Granted, it's not an ideal answer but it's certainly a feasible and supported solution. Firstly, some explanations are in order.

When an APEX application is imported into a workspace, the very first thing that's done is the existing version of the application is completely deleted. All of the meta data associated with the application is deleted - the definition of the pages, the reports on the pages, the templates, the buttons, the branches, the shared components, everything - it's all deleted. Once this is complete, then the application meta data of the APEX application being imported is then inserted. This whole process is atomic - so if an error occurs, the transaction is rolled back and the net effect is no change.

In the case of saved Interactive Reports, it's a little bit different. Imagine you have a production instance running application 645 - you have numerous users who have saved many Customized Interactive Reports. Upon import of a new version of the application, all meta data associated with application 645 is first deleted except the Customized Interactive Reports. In essence, these are left "dangling" until the new application 645 is installed. Once the application import is complete, then the meta data of the Customized Interactive Reports will reference real interactive report definitions again.

But there's a catch. If the application ID changes upon import, then this results in totally new meta data IDs being generated. (This is done in an attempt to prevent collisions of meta data, so you can freely export your application and give to anyone in the world to use on their own APEX instance). A meta data offset number is randomly generated and added to all of the existing IDs. This is done uniformly across all of the application meta data (this is important, and you'll see why shortly). Since the IDs of all of the application meta data have changed, all of your users' customized reports in the previous version of the application are forever left orphaned until they're cleaned up by an internal APEX batch process. Yikes!

Let's look at an example. On apex.oracle.com, I created a simple application with an Interactive Report on the EMP table. I defined this application as application 70000. I then exported this application and imported it back as application 70001.

Using SQL Commands and the APEX Data Dictionary views, I ran the following queries:


select tab_label, tab_id
from APEX_APPLICATION_TABS
where application_id = 70000

tab_label: Emp
tab_id: 1573281607527253166


select tab_label, tab_id
from APEX_APPLICATION_TABS
where application_id = 70001

tab_label: Emp
tab_id: 3146580610985521585

And the difference between the two IDs is 3146580610985521585 - 1573281607527253166 = 1573299003458268419


Let's do this again, but this time, for the APEX data dictionary view for page templates:

select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000

template_id: 1573270610302252883


select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001

template_id: 3146569613760521302


If we once again compute the differences between these two IDs, we get: 3146569613760521302 - 1573270610302252883 = 1573299003458268419

This happens to be exactly the difference between the IDs of all of the application meta data, with the exception of the application and page IDs. All of the meta data is consistently "pushed" or offset to a new value.

How is this relevant to missing saved Interactive Reports? Simple. Since we're able to compute the offset which was used between the two applications, if we had a way to ensure that the same offset is used every time upon application import, then there would be no issue with the old saved Interactive Report IDs matching with the newly imported meta data. They would be married again. And how is this done? In Application Express 4.0, there is a new API named APEX_APPLICATION_INSTALL which enables you to control this offset value.

To ensure that I didn't lose the saved Interactive Reports on subsequent imports of application 70000 to application 70001, I included the computed offset before importing this application via SQL*Plus:


begin
apex_application_install.set_application_id( p_application_id => 70001 );
apex_application_install.set_offset( p_offset => 1573299003458268419 );
--
-- set the alias so it doesn't collide with the alias from app 70000
--
apex_application_install.set_application_alias(
'F' || apex_application.get_application_id );
end;
/


@f70000.sql


That's all there is to it. Note that I didn't have to call apex_application_install.set_workspace_id above, because application 70000 and 70001 are in the same workspace where I performed this test.

As I stated earlier, this isn't the most elegant solution on the planet and most people don't want or need to know about meta data IDs or offsets or any of this complexity. But for those experienced users who are stuck with this problem of losing saved interactive reports when migrating from one application ID to another or across workspaces or instances, this is a supported and feasible solution.




14 comments:

  1. I want to make sure that I understand this correctly, this only applies if you switch application ID's correct?

    For example, right now my production app is Apex 3.2 app ID 1.

    In development I am running Apex 4.0, app ID 1. When I upgrade production to 4.0 and import app ID 1, will the users saved reports be in place, or should I just the SQL*Plus method you discussed?

    If you use the SQL*Plus method then you'll have to ensure that you use the same offset value every time you import a new version of app ID 1, correct?

    ReplyDelete
  2. Tony,

    Sorry for any confusion this may have caused.

    Upgrade from 3.2 to 4.0 in your production environment will have zero bearing on this. Your users' saved reports will be there before and after the upgrade, assuming in 3.2.1 you could export from dev and import into production without any issues. Nothing has changed, in this respect.


    If you use the SQL*Plus method, either the workspace IDs must already be identical between development and production (a requirement even in 3.2.1) or you must use apex_application_install.set_workspace_id before importing the application. And you would need to do this every time you import a new version of the application. In your specific case, it doesn't sound like your meta data offsets are different, so you wouldn't need to set the offset before importing.

    Joel

    ReplyDelete
  3. Joel,

    Thanks for posting up a solution to the issue. It will definitely help us with our one app that is deployed as different app ids in our environment. We've struggled with going the unsupported route and now there is someplace to turn.

    Thanks,
    Justin

    ReplyDelete
  4. "forever left orphaned until they're cleaned up by an internal APEX batch process"

    Is this one of the scheduler jobs setup during installation? Which one? And what are all the cleanup tasks it performs? Thanks

    ReplyDelete
  5. @Vikas,

    >> "forever left orphaned until they're cleaned up by an internal APEX batch process"

    You know - I stand corrected. These are cleaned up on upgrade and not during any batch process.

    We have to be careful about purging these too quickly, because someone may wish to "marry" them back to the original reports. What would be your recommendation of a time period to wait before forever purging them?

    Thanks.

    Joel

    ReplyDelete
  6. Is it possible to use this approach when the calulated offset difference is a negative #? Also, is there a method to do this by editing the export file and running the import through the APEX import utility?

    ReplyDelete
  7. Hi Joel
    I have recently upgraded my installation to Apex 5.1.
    Is your post still applicable?
    Thanks in advance
    Regards
    Wolfgang

    ReplyDelete
  8. Hi Wolfgang,

    No. These steps aren't necessary for APEX 5.1. Beginning with APEX 4.2, there was a lot of work done internally to preserve these saved interactive reports when importing/exporting. We take care of all of the preservation of these saved reports.

    Joel

    ReplyDelete
  9. Joel,

    I am a newbie with Apex and I am interested in finding out exactly how this is done (saving production user saved IR Reports when re-deploying from DEV (different Application_ID's... since both our Dev and Prod Instances run on the same database)

    We are using Apex Version 5.03xxxxx

    How am I able to just export the production's users saved ir reports and then when I redeploy (import) the development export file to production (keeping the same Application ID?) Is there a way to import from dev, apply the prod IR Reports?

    Thanks in advance,

    Terry

    ReplyDelete
  10. Hi Terry,

    You should not have to do anything special. Everything should be preserved when you overwrite the application in production with the one from development. Just a couple things to keep in mind:

    1) An application export automatically contains default reports (both primary and alternative), those default reports will be overwritten.

    2) If an application export contains the same public or private report as production (same report name saved by the same user), those reports will be overwritten.

    Great question!

    Joel

    ReplyDelete
  11. Hi Joel,
    when we deploy our application from development to production, all saved private reports of an interactive grid are lost on production. On development we have no private reports defined and private reports aren't included in the export file. Am I doing something wrong or is this not possible at the moment?
    Stefanie

    ReplyDelete
  12. Hi Stefanie,

    Reluctantly, I have to admit that this is a known issue, Bug 27024669. It will be fixed in the next release of APEX.

    Joel

    ReplyDelete
  13. Hi Joel,
    I am using apex 5.1 and when I try to replace the production version with development , all the private reports will be overridden with development version. Is there any way to retain the production version private reports? we have two different workspace for development and production. Currently what I am trying is manually copy all private report code from .sql file and paste it to development .sql file and import it to production. I am sure there would be a better approach. Please suggest.

    ReplyDelete
  14. Hi Ashwin,

    You shouldn't have to do anything special. And the steps provided in this blog post are no longer necessary (in your version of APEX and later ones too). The Interactive Reports should all be maintained properly.

    Are you exporting private reports from development? Is there a chance that you're simply overriding them with the private reports (for the same named user) from development?

    Joel

    P.S. Debugging issues like this isn't easy in blog post comments. You may wish to use one of the detailed community resources, which I discuss here: https://blogs.oracle.com/apex/i-need-help-with-oracle-apex-where-can-i-get-it

    ReplyDelete