Recently (since 4.1 update) I’ve experienced issues when importing an application into an Apex environment.

After a little bit of searching I found the way to do the import on the command-line.

It is documented in the Apex documentation, but as you know I’d like this blog to be an “OPI” (one point of information) 🙂

Please have a look at the script, alter it whenever appropriate.

declare
  -- the name of the workspace in wich to import
  t_workspace    varchar2(30):= 'MERIDA';
  -- an application number of an existing application in the workspace.
  t_existing_app number      := 100;
  -- the "new" application number, an existing number will be dropped first.
  t_new_app      number      := 100;
  -- security group id, you don't have to set this variable
  t_secgrp_id    number;
begin
  -- get the Security Group ID
  select workspace_id
  into   t_secgrp_id
  from   apex_applications
  where  application_id = t_existing_app;

  -- Set the Security Group ID
  wwv_flow_api.set_security_group_id(p_security_group_id => t_secgrp_id);
  -- Set the Application ID to use
  apex_application_install.set_application_id(t_new_app);
  -- This procedure generates the offset value used during application import.
  -- The offset value is used to ensure that the metadata for the Application
  -- Express application definition does not collide with other metadata on
  -- the instance. For a new application installation, it is usually sufficient
  -- to call this procedure to have Application Express generate this offset
  -- value for you.
  apex_application_install.generate_offset;
  -- Set the parsing schema
  apex_application_install.set_schema( t_workspace );
  -- Set the application alias
  apex_application_install.set_application_alias( 'F' || t_new_app );
end;
/
-- Do the actual import
@D:ProjectsStaplesMeridaexportsmerida-srv022-20111222-1157.sql;

commit;

That’s all folks 🙂

6 thoughts on “Import Apex Application at command line

  1. Hi Richard,

    I have read a number of blogs and threads including your solution to how to import an apex application into a Apex RT environment.

    http://joelkallman.blogspot.co.uk/2010/07/apexapplicationinstall.html
    http://www.apexninjas.com/blog/2011/04/import-full-apex-application-to-runtime-environments/

    Some of the other blogs assume that you are importing the application for the first time into the apex rt environment using the command line i.e. sqlplus. Unfortunately in my case, the apex rt environment already has an older version of the application, therefore the workspace exist.

    I have tried your solution as well as the different methods explained in the blogs mention above. I can successfully import the application into the apex rt environment without any errors during the import. However, when I try to access the application as an application user would, I get the following error:

    Expecting p_company or wwv_flow_company cookie to contain security group id of application owner.
    Error ERR-7620 Could not determine workspace for application ().

    On the other hand If I was to use Oracle SQL Developer to complete the import of the save application, the application runs OK. As the server on which the upgrade must be applied is overseas, I need to find the solution to this import issue so it can be achieved via SQLPLUS, so I can embed the script into a executable which makes use of sqlplus and send the executable to the client.

    Any help suggestion would be more than welcome
    Application Express version 4.0.2.00.07
    Db Version is 11g R2 current release version 11.2.0.3.0

    Thank you

    Reply
  2. The code block works for me. In fact I have it embedded in a Perl script that calls SQL*Plus and it runs fine. Using APEX 4.2.2, Oracle 11G on Linux RHEL 6.3.
    One ‘bug’ it does have though — it does not take the new application id. Always uses the application id from export.
    Any ideas?

    Reply

Leave a Reply