Where Did My Saved Interactive Reports Go?

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:

view plaincopy to clipboardprint?

1. select tab_label, tab_id
2. from APEX_APPLICATION_TABS
3. where application_id = 70000
4.
5. tab_label: Emp
6. tab_id: 1573281607527253166
7.
8.
9. select tab_label, tab_id
10. from APEX_APPLICATION_TABS
11. where application_id = 70001
12.
13. tab_label: Emp
14. tab_id: 3146580610985521585


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:
view plaincopy to clipboardprint?

1. select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70000
2.
3. template_id: 1573270610302252883
4.
5.
6. select template_id from APEX_APPLICATION_TEMP_PAGE where template_name = 'Login' and application_id = 70001
7.
8. template_id: 3146569613760521302


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:

view plaincopy to clipboardprint?

1. begin
2. apex_application_install.set_application_id( p_application_id => 70001 );
3. apex_application_install.set_offset( p_offset => 1573299003458268419 );
4. --
5. -- set the alias so it doesn't collide with the alias from app 70000
6. --
7. apex_application_install.set_application_alias(
8. 'F' || apex_application.get_application_id );
9. end;
10. /
11.
12.
13. @f70000.sql


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.

SOURCE

Recover saved interactive reports after an application import

During a recent update of my live APEX system I noticed that the user's saved interactive reports had been lost. I had made numerous updates of the live system before and never experienced this issue before. I found an interesting post from David Peak detailing how to avoid the loss of the saved reports http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html , but before doing this I had to understand exactly what had happened and hopefully work out how to recover the lost reports.

My normal workflow would be to develop in my development environment and then progress this application through testing and into live, preserving the application id through every progression. In this specific instance I had taken a cut of the application from live and imported it into development using a temporary id. On each subsequent promotion I then changed the id to the original, and correct, id. It seems that this changing of the id causes the issue.

Now that I understood how the issue occurred and, thanks to David Peake, understood how to stop this from happening again, I wanted to recover the reports and hopefully calm down irate users.

The saved reports are saved in the table flows_030100.wwv_flow_worksheet_rpts.



Key columns are as follows
flow_id - the id of the application
page_id - the page number where the interactive report is situated
Worksheet_id - the internal unique id
session_id - being blank indicates that this is the "master" version of the individual users saved report.

So now I know where the data is stored, the next step is understanding the data. From David Peake's site mentioned earlier, I know that the worksheet id is the unique identifier for the interactive report, but I knew little else.

After ordering the data based on the date found in the updated_on column I noticed that there was a distinct change in the workspace ID, around the same time that I had done the upgrade. From there it was straightforward enough to realise that I had to update the previous records to include the new worksheet_id that APEX had automatically assigned to the Interactive Region. I also noticed for each unique user and interactive report there were many rows. Although I do not fully understand the use for these multiple rows, except for perhaps auditing purposes, it is clear(after some tests) that the actual saved report is defined by the row with the blank session_id.

Now that I had a bit of understanding of the data I could attempt the recovery. The following is a brief overview of how I actually did it.

1. Determine the new worksheet_id for the specific interactive report where the saved reports are missing. For this, focus on the flow_id(application_id) and page_id. Ensuring that you select data dated after the application upgrade. If there are no rows present for the specific interactive report, then a quick visit to the relevant page in the application will create a row. Simply visiting the report will create a row, but saving a report will allow it to be found even easier. Do not worry about saving a report in a live system, as this report is linked solely to the user you logged in as.
2. Now that you have the correct worksheet_id, you have to determine the rows that contain the data for the saved reports. To do this follow the above procedure, but only include those rows which fall within the date range of your last version of the application.
3. Now simply update the rows containing the saved reports with the new worksheet_id.

A couple of points about the above

1. Before modifying the data you have to unlock the flows_030100 user (or the flows user for whatever version of APEX you use).
2. There is a unique key present that will stop you from having multiple saved reports with the same name, for the same report and for the same user. To get round this you can alter the name to something like, "report A (old)". I'm sure you will think of something better than this, but you get my drift.
3. Remember that you are messing with core APEX tables, and messing with these tables can cause issues with your APEX installation as a whole. Basically, make a backup before you start.

Hopefully the above is of some use to you if you do experience the same issues as I did.