Deploying Application Express on the Command Line

apex exportI love the APEX UI, it makes development so much easier and more convenient – and makes it easy to impress clients when I can quickly fix issues right there and then, using nothing but their computer and their browser, no additional software tools needed.

However, at my main client they have a fairly strict “scripted releases only” policy which is a really excellent policy – deployments must always be provided as a script to run on the command line. This makes for less errors and a little less work for the person who runs the deployment.

In APEX it’s easy to create deployment scripts that will run right in SQL*Plus. You can export a workspace, an application, images, etc. as scripts that will run in SQL*Plus with almost no problem. There’s just a few little things to be aware of, and that’s the subject of this post.

1. Setting the session workspace

Normally if you log into APEX and import an application export script, it will be imported without problem. Also, if you log into SQL*Plus and try to run it, it will work fine as well.

The only difference comes if you want to deploy it into a different workspace ID to the one the application was exported from – e.g. if you want to have two workspaces on one database, one for dev, one for test, when you log into your test schema and try to run it, you’ll see something like this:

SQL> @f118.sql
APPLICATION 118 - My Wonderful App
Set Credentials...
Check Compatibility...
Set Application ID...
begin
*
ERROR at line 1:
ORA-20001: Package variable g_security_group_id must be set.
ORA-06512: at "APEX_040100.WWV_FLOW_API", line 73
ORA-06512: at "APEX_040100.WWV_FLOW_API", line 342
ORA-06512: at line 4

Side note: if you’re using Windows, the SQL*Plus window will disappear too quickly for you to see the error (as the generated apex script sets it to exit on error) – so you should SPOOL to a log file to see the output.

To fix this issue, you need to run a little bit of PL/SQL before you run the export, to override the workspace ID that the script should use:

declare
  v_workspace_id NUMBER;
begin
  select workspace_id into v_workspace_id
  from apex_workspaces where workspace = 'TESTWORKSPACE';
  apex_application_install.set_workspace_id (v_workspace_id);
  apex_util.set_security_group_id
    (p_security_group_id => apex_application_install.get_workspace_id);
  apex_application_install.set_schema('TESTSCHEMA');
  apex_application_install.set_application_id(119);
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias('TESTAPP');
end;
 /

This will tell the APEX installer to use a different workspace – and a different schema, application ID and alias as well, since 118 already exists on this server. If your app doesn’t have an alias you can omit that last step. Since we’re changing the application ID, we need to get all the other IDs (e.g. item and button internal IDs) throughout the application changed as well, so we call generate_offset which makes sure they won’t conflict.

2. Installing Images

This is easy. Same remarks apply as above if you’re installing the image script into a different workspace.

3. Installing CSS Files

If you export your CSS files using the APEX export facility, these will work just as well as the above, and the same considerations apply if you’re installing into a different workspace.

If you created your CSS export file manually using Shared Components -> Cascading Style Sheets and clicking on your stylesheet and clicking “Display Create File Script“, you will find it doesn’t quite work as well as you might expect. It does work, except that the file doesn’t include a COMMIT at the end. Which normally wouldn’t be much of a problem, until you discover late that the person deploying your scripts didn’t know they should issue a commit (which, of course, would have merely meant the file wasn’t imported) – and they didn’t actually close their session straight away either, but just left it open on their desktop while they went to lunch or a meeting or something.

This meant that when I sent the test team onto the system, the application looked a little “strange”, and all the text was black instead of the pretty colours they’d asked for – because the CSS file wasn’t found. And when I tried to fix this by attempting to re-import the CSS, my session hung (should that be “hanged”? or “became hung”?) – because the deployment person’s session was still holding the relevant locks. Eventually they committed their session and closed it, and the autocommit nature of SQL*Plus ended up fixing the issue magically for us anyway. Which made things interesting the next day as I was trying to work out what had gone wrong, when the system was now working fine, as if innocently saying to me, “what problem?”.

4. A little bug with Data Load tables

We’re on APEX 4.1.1  If you have any CSV Import function in your application using APEX’s Data Loading feature, if you export the application from one schema and import into another schema, you’ll find that the Data Load will simply not work, because the export incorrectly hardcodes the owner of the data load table in the call to create_load_table. This bug is described here: http://community.oracle.com/message/10309103?#10307103 and apparently there’s a patch for it.

wwv_flow_api.create_load_table(
 p_id =>4846012021772170+ wwv_flow_api.g_id_offset,
 p_flow_id => wwv_flow.g_flow_id,
 p_name =>'IMPORT_TABLE',
 p_owner =>'MYSCHEMA',
 p_table_name =>'IMPORT_TABLE',
 p_unique_column_1 =>'ID',
 p_is_uk1_case_sensitive =>'Y',
 p_unique_column_2 =>'',
 p_is_uk2_case_sensitive =>'N',
 p_unique_column_3 =>'',
 p_is_uk3_case_sensitive =>'N',
 p_wizard_page_ids =>'',
 p_comments =>'');

The workaround I’ve been using is, before importing into a different schema, I just edit the application script to fix the p_owner in the calls to wwv_flow_api.create_load_table.

5. Automating the Export

I don’t know if this is improved in later versions, but at the moment you can only export Applications using the provided API – no other objects (such as images or CSS files). Just a sample bit of code (you’ll need to put other bits around this to do what you want with the clob – e.g. my script spits it out to serverout so that SQL*Plus will write it to a sql file):

l_clob := WWV_FLOW_UTILITIES.export_application_to_clob
  (p_application_id => &APP_ID.
  ,p_export_ir_public_reports => 'Y'
  ,p_export_ir_private_reports => 'Y'
  ,p_export_ir_notifications => 'Y'
  );

That’s all my tips for scripting APEX deployments for now. If I encounter any more I’ll add them here.

EDIT:

Related: “What’s the Difference” – comparing exports to find diffs on an APEX application – http://blog.sydoracle.com/2011/11/whats-difference.html

Trivia question: Why does the 2nd Drop Work?
AUSOUG WA Breakfast Meeting: Function Result Cache

Comments

  1. A nice summary. We’ve been using the UI until encounting an issue (potentially with app size) recently and had to use the APIs, which I have no qualms with. While our project uses file system for images/css, I’ll be chasing up your comments regarding images because my last experiments with files loaded in APP_IMAGES didn’t work so well.

  2. Enjoyed your article.
    I used your code above and everything works but I cannot reassign a new app id the call to
    apex_application_install.set_application_id(127043902); The exported application that I am importing has an application id of 127 and it gets imported with the id and not changed to 127043902 like I want. Any ideas?
    Thanks

    • Hi Tony, I don’t know, but maybe 127043902 is too large for an App ID? Have you tried 128 (one that isn’t currently used in your instance)?

  3. 127043902 works when importing an exported APEX file using the APEX Web front-end. I am just trying to recreate programmatically what would normally be done via the APEX Web front-end.

  4. I tried using a new app id — 138 — just to be sure. No luck, still imports as app id 127.

  5. Here’s my entire environment

    Linux RHEL 6.3
    APEX 4.2.2
    Oracle 11gR1

    By the way, I am piping the APEX SQL code through SQL*Plus 11.2.0.2 on my (Windows 7) desktop.

    • That should be fine. Can you post your whole script that you’re running prior to running the application script (i.e. not just the apex_application_install.set_application_id, but the whole thing)?

      • 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;

  6. from the Oracle docs:

    APEX_APPLICATION_INSTALL.SET_APPLICATION_ID (
    p_application_id IN NUMBER);

    p_application_id = This is the application ID. The application ID must be a positive integer, and cannot be in the reserved range of application IDs (3000 – 8999). It must be less than 3000 or greater than or equal to 9000.

  7. Question: Who do I log in as when running this PL/SQL? I am logging in as the owner/schema that contains the DDL for the APEX application. Is this correct?

    • Hi Tony, to run these scripts you need to log in with a schema assigned for the target workspace. This can be checked/managed from the Apex Admin console under Manage Workspaces -> Manage Workspace to Schema Assignments.

  8. Hi Jeffrey,
    your blog help me a lot, I’ve got just one question : is there a way to set the file character set as UTF-8 (because there are many é à è in french) ??
    Thanks,
    Devi

  9. Hi Jeff,

    Thanks for posting this as it solved my problem when importing an application from Apex 5 into a brand new, fresh out-of-the-oven environment (Oracle 12.2 database and Apex 19.2). The application imported successfully through the command line but still crashes the builder. Within 5 seconds of starting the import I get a wwv_flow.accept page with the general browser message “The connection was reset. The connection to the server was reset while the page was loading.” Any tips on what I should check or where to find more information as to the cause?

    Regards,
    Alwyn

    • When we say “command line” we mean in an environment outside of APEX – e.g. in SQL*Plus or SQL Developer. You should not run this code within APEX builder, which manages the session for you.

  10. Thanks for your reply, Jeff. The extra code designed for SQL*Plus was not run within the Apex builder, only a standard import of the .sql file that was exported out of a different Apex instance. But it is crashing the builder in the new environment.

  11. Yes, importing from App Builder -> Import.

Leave a Reply

Your email address will not be published / Required fields are marked *