11 Replies Latest reply on Nov 7, 2013 9:38 PM by tx103108

    Importing APEX app with PL/SQL does not change app id as requested

    tx103108

      Oracle DB XE 11gR1

      APEX 4.2.2

      RHEL 6.3

      ------------------

       

      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?

       

      declare                 .....

                        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
                        ....
                       
                        exception
                        when others
                        .....
                      end;
      /

       

      @APEX_export.sql;

       

      commit;

       

      Thanks.

        • 1. Re: Importing APEX app with PL/SQL does not change app id as requested
          Nick Buytaert

          Hey,

           

          I think you have to call more procedures from the 'apex_application_install' package. What procedures depends on how the export file looks like. Read through the official documentation on this topic. It includes some interesting examples that will help you out.

           

          Best regards,

          Nick

          1 person found this helpful
          • 2. Re: Importing APEX app with PL/SQL does not change app id as requested
            Scott

            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:

             

            BEGIN

            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');

            END;

            /


            @f1100.sql


            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!


            Thanks,

             

             

             

            - Scott -

             

             

             

            http://spendolini.blogspot.com

            http://www.enkitec.com

            1 person found this helpful
            • 3. Re: Importing APEX app with PL/SQL does not change app id as requested
              tx103108

              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

                                apex_application_install.generate_offset;

                                apex_application_install.set_schema('MY_WORKSPACE');

                                apex_application_install.set_application_alias('MWKSP');

               

              My apologies for the code fragment approach.  :-(

              • 4. Re: Importing APEX app with PL/SQL does not change app id as requested
                tx103108

                Hello Nick,

                     Below is from the Oracle documentation.  As you can see from my response to Scott above, I am doing this...

                 

                Import Application with Specified Application ID

                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
                
                • 5. Re: Importing APEX app with PL/SQL does not change app id as requested
                  Scott

                  I've not tried using generate_offset, as the real reason that we're doing this is so that we can preserve IRs.  See if it works when you manually compute the offset as per Joel's blog.

                   

                  Thanks,

                   

                   

                  - Scott -

                   

                   

                  http://spendolini.blogspot.com

                  http://www.enkitec.com

                  • 6. Re: Importing APEX app with PL/SQL does not change app id as requested
                    tx103108

                    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).

                     

                    declare
                                      v_workspace    varchar2(30)    := 'MY_WORKSPACE';
                                      n_new_app      number          := 127034902;
                                      v_app_alias    varchar2(30)    := 'MYWKSP';
                                      v_err_msg      varchar2(200);
                                      n_err_num      number;
                                      n_workspace_id number;
                                    begin
                                     
                                      select workspace_id
                                      into n_workspace_id
                                      from apex_workspaces
                                      where workspace = v_workspace;
                                      
                                      apex_application_install.set_workspace_id (n_workspace_id);   
                                      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.generate_offset;
                                      apex_application_install.set_application_alias(p_application_alias => v_app_alias);
                                     
                                      exception
                                      when others
                                        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);
                                     
                                    end;
                    /

                     

                    @import.sql

                     

                    commit;

                    • 7. Re: Importing APEX app with PL/SQL does not change app id as requested
                      tx103108

                      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?
                      Thanks.

                      • 8. Re: Importing APEX app with PL/SQL does not change app id as requested
                        Scott

                        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.

                         

                        Thanks,

                         

                         

                        - Scott -

                         

                         

                        http://spendolini.blogspot.com

                        http://www.enkitec.com

                        • 9. Re: Importing APEX app with PL/SQL does not change app id as requested
                          tx103108

                          Hello 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?

                          Thank you.

                          • 10. Re: Importing APEX app with PL/SQL does not change app id as requested
                            tx103108

                            Hello Scott,

                                 Any further ideas on this subject?  Could this be a bug with the latest APEX release that I am using (4.2.2)?  Is there a way -- other then what I am doing -- to generate an offset when the target app does not exist?

                                 Thanks.

                            • 11. Re: Importing APEX app with PL/SQL does not change app id as requested
                              tx103108

                              Turns out the code above (in 6.) works fine.  My development environment has an issue with the workspace not being found.  Tried the code in another test environment and it worked.

                              Thanks.