Oracle DB XE 11gR1
The following code segment does successfully import an APEX app that has been exported. However, even when given a 'new' application id, the 'old' application id from within the export is still used.
What could be wrong? That is, how can I ensure that the 'new' application id is used and overrides the 'old' application id that comes with the APEX app export file?
wwv_flow_api.set_security_group_id(p_security_group_id => v_secgrp_id);
apex_application_install.set_application_id(t_new_app); # does not change app id to new app id
You'll need to also pass in a value for the offset. Here's a sample of the script that we use when moving an application from one ID to another:
apex_application_install.set_workspace_id(p_workspace_id => 23423423424234);
apex_application_install.set_application_id( p_application_id => 1000 );
apex_application_install.set_offset( p_offset => 23424234234234 );
apex_application_install.set_schema(p_schema => 'PROD');
You will need to calculate the value for p_offset for this to work, as that will be unique to your two applications in your environment. Also, you'll need to pass in the new schema name, if it is different. This post from Joel Kallman outlines how to compute the offset: (YABAOAE) Yet Another Blog About Oracle Application Express: Where Did My Saved Interactive Reports Go?
Hope this helps!
- Scott -
Thanks Scott. I did not include it in my code example but I generate the offset (after the set_application_id call) by executing apex_application_install.generate_offset. Is this satisfactory?
apex_application_install.set_application_id(123456); -- does not change app id to new app id 123456 but instead keeps app id 999
My apologies for the code fragment approach. :-(
Below is from the Oracle documentation. As you can see from my response to Scott above, I am doing this...
To import this application back into the FRED_DEV workspace on the same development instance, but using application ID 702:
begin apex_application_install.set_application_id( 702); apex_application_install.generate_offset; apex_application_install.set_application_alias( 'F' || apex_application.get_application_id ); end; / @645.sql
I tried the following (from Jeff Kemp's blog at http://jeffkemponoracle.com/2013/05/22/deploying-application-express-on-the-command-line/) -- still no luck.
One note: I am exporting my APEX app from one instance and importing to another new Oracle APEX environment, in the process over-writing the APEX app in the new environment (if it exists).
v_workspace varchar2(30) := 'MY_WORKSPACE';
n_new_app number := 127034902;
v_app_alias varchar2(30) := 'MYWKSP';
where workspace = v_workspace;
apex_util.set_security_group_id (p_security_group_id => apex_application_install.get_workspace_id);
apex_application_install.set_schema(p_schema => v_workspace);
apex_application_install.set_application_id(p_application_id => n_new_app); -- does not change app id to new app id
apex_application_install.set_application_alias(p_application_alias => v_app_alias);
n_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line('Error number = ' || n_err_num);
dbms_output.put_line('Error message = ' || v_err_msg);
I read Joel's blog and found it helpful. But the application I am importing may or may not already exist in the destination APEX instance. That is, I am importing the application for the first time or overwriting the (pre-existing) application and wanting to give it the same app id it already has. The reason the export has an app id I don't want is because it is coming from a development environment. The target environment is different and there can be many different target environments, each needing to have its unique app id. Or, to put it another way, the export will always have app id 150 but the target app id will always be different from 150.
How does the offset come into play here? How does one calculate the offset?
To calculate the offset, you'll need both the source and target app's ID. If the target app does not exist, then you should be OK importing it directly.
You'll probably have to write a little script to do this in real time for you, if you want to truly automate it.
- Scott -
I am importing the APEX app as a new app and the app_id does not exist in the target system. Yet I get the default app_id instead of the app_id I reset to.
I am implementing this thru a scripting language but I have tried the code above in SQL*Plus and in both cases I cannot modify the app_id -- it keeps the app_id that was part of the export. I know you said this isn't necessary when the target app is 'new' but is there way to calculate the offset where the target app does not yet exist?