Application Express 5.1 is available since December 2016. As always, developers find a huge amount of new features allowing to build better applications. But APEX 5.1 also contains interesting news for instance administrators. This how to document introduces some of these new functions.

Installation and upgrade

At the first glance, installing a new APEX 5.1 instance, or upgrading an existing APEX instance to 5.1, works the same way as with the releases before: A DBA (e.g. logged in as SYS) runs the apexins.sql installation script; typically as follows:

$ apexins SYSAUX SYSAUX TEMP /i/

The first argument determines the tablespace for the APEX engine itself (which is installed into the APEX_050100 schema); the second one sets the tablespace for the FLOWS_FILES schema (which contains the table for uploaded files). The default temporary tablespace for both schemas is set by the third argument ("TEMP"); and the last argument determines the URL path prefix, which the browser uses to access static image, javascript or CSS files. That one has to match the APEX web server configuration and can be changed using the utilities/reset_image_prefix.sql script, if necessary.

apexins.sql installs APEX completely, that means, the APEX runtime as well as the development environment ("workspace login"). Production systems often want to have only the APEX runtime in order to make the system more secure and robust. For these cases, APEX provides the apxrtins.sql script.

apexins.sql or apxrtins.sql automatically detect existing APEX installations; in these cases the installation will be upgraded to APEX 5.1 automatically. Let's have a detailed look into how such an APEX upgrade works.

Having that in mind, it's easy to understand why APEX allows to revert the upgrade when errors or problems are encountered immediately after the upgrade: the old version is still here - we just have to point the APEX public synonyms back, making it active again. The APEX Installation Guide describes how that works.

Previous APEX releases required the APEX instance to be down for the whole installation and upgrade process. Before starting the apexins.sql script, the web server had to be taken down until the script finished and the upgrade was completed. Upgrade duration basically depends on the amount of existing workspaces and applications. Dedicated APEX instances with only a few workspaces and applications are rather quick and finish in a few minutes; large instances (like Oracle's apex.oracle.com with thousands of applications) require several hours for an upgrade to complete. Considering the fact, that many projects and business departments are dependent on a multi-project APEX instance, such a downtime can become a problem.

APEX 5.1 allows to perform the upgrade in four phases. Each phase has different downtime requirements.

1. Create the APEX_050100 schema and install the APEX engine
2. Migrate application meta data to the new APEX version
3. Migrate runtime meta data, like interactive report settings, to the new APEX version
4. Migrate archive data, like the APEX activity log, to the new APEX version

The first phase does not impact the existing APEX version at all; so the APEX instance (old version) is up and running during that time: Developers and end users can work as usual. In the second phase, application meta data will be migrated, which means that developers cannot change applications during that phase. Workspace login does work, developers can also browse through the applications, but changes are not possible. End users are not impacted at all.

Only the third phase requires a complete downtime. The web server must be taken down for that phase. But since the work in that phase is only a small part of the whole process, downtime will be significantly reduced. After that phase has been completed, the new APEX version is fully available to end users and developers; the final phase of migrating archived data will run in background.

To perform a minimum downtime upgrade, the apexins1.sql to apexins3.sql scripts must be used instead of apexins.sql. For a runtime only installation, there are the apxrtins1.sql to apxrtins3.sql scripts instead of apxrtins.sql.

• First, apexins1.sql / apxrtins1.sql is started. The APEX web server remains up and running; so APEX is still available.
• After that, apexins2.sql / apxrtins2.sql initiate the second phase. The web server still remains up and running. Developers can log into their workspaces and browse their applications. However, an attempt to make a change leads to an error message similar to the following.
  

  Applications cannot be changed during upgrade phase 2

• The third phase, initiated with the apexins3.sql or apxrtins3.sql script, requires a downtime. as described above. So, before that script is started, the web server must be taken down in order to prevent access to Application Express. After the script finished, the static files of the new APEX version must be provided on the web server, so that they are accessible under the URL prefix used in the apexins?.sql scripts (normally /i/). Then the web server can be started again making the new APEX version available to all developers and end users.
• The fourth and final phase will be initiated from the apexins3.sql / apxrtins3.sql script as a DBMS_SCHEDULER job automatically. Developers and end users are not impacted by this phase.

REST services to retrieve Application Express statistics

APEX 5.1, introduces a REST-based administation interface. This first version contains REST services to provide access statistics and metrics. That allows to fetch statistics over REST and to collect metrics from multiple applications, workspaces and even instances into a central location. To use these REST services, Oracle REST Data Services (ORDS) must be used as the APEX web server and it has to be at least the 3.0.5 version.

Initially, the administrative REST interface is disabled. To enable it, log into the INTERNAL workspace and then to the Manage Instance section.

Workspace INTERNAL - Manage Instance

Click on REST Administration Interface. A dialog window opens showing a message, that the REST interface is disabled.

The REST Administration Interface is disabled

Click the Enable Services button. That will enable the REST interface.

REST Administration Interface is now enabled

Authentication to the REST interface is done usingh the OAuth2 Client Credentials flow. More information on this is contained in the Oracle REST Data Services documentation. So, an OAuth Client must be registered in order to generate a client ID and a client secret for authentication. Click the Create OAuth Client button to do this.

Register a new OAuth client

Enter a descriptive name and a contact email address. In APEX 5.1 only the privilege to access statistic data View Usage Statistics is available. Then click Create OAuth Client, to actually register the client.

The new OAuth client has been created

Click the name of the new client in order to see its details; we are now interested in the generated Client ID and Client Secret.

Client ID and Client Secret of the new OAuth client

The OAuth Client Credentials flow states, that Client ID and Client Secret are being used to obtain an Access Token from the server. For the next one hour, that access token can be used to authenticate with the actual REST interface. The following code examples show how the REST interface is being used with the curl command line utility. The first request obtains the access token:

$ curl -u eQWP0rf84osHamhhLeFfUQ..:ZqRZMJ2BuUo7i8rad2GKzQ.. 
       --data "grant_type=client_credentials"  
       http://localhost:18080/ords/apex_instance_admin_user/oauth/token

{"access_token":"eT-I3nA9peooabMa0F1Ikw..","token_type":"bearer","expires_in":3600}

The server responds, as almost all REST services, in JSON format. The access_token attribute contains the actual token value. With that, we can execute the second request fetching statistics from the APEX instance.

curl -H"Authorization: Bearer eT-I3nA9peooabMa0F1Ikw.." 
     http://localhost:18080/ords/apex_instance_admin_user/stats/latest/instance

Again, the metrics are delivered in JSON format.

{
  "items": [
    {
       "log_day": "2016-09-15T00:00:00Z",
       "workspace_id": 1809074264671554,
       "workspace_name": "SOMEWORKSPACE",
       "workspace_link": {
          "$ref": "http://application-express-host:port/ords/apex_instance_admin_user/stats/latest/workspace/someworkspace"
       },
       "application_id": 4750,
       "application_name": "Oracle APEX Packaged Applications",
       "application_link": {
       :

The available REST services are documented in the Application Express Administrators Guide; metrics can be obtained on instance, workspace or application level. Filtering for specific time frames or specific metric values is also possible. The architecture based on open standards like JSON, REST and OAuth makes it easy to integrate with 3rd party software.

• APEX_UTIL.SET_APPLICATION_STATUS:
  This procedure allows to set the status of an APEX application programmatically with PL/SQL. So an application can be made available or unavailable without logging into the workspace as a developer. The following code makes application 101 in the TESTIT workspace unavailable.

  begin
      apex_util.set_workspace( 'TESTIT' );
      apex_util.set_application_status( 
          p_application_id     => 101, 
          p_application_status => 'UNAVAILABLE', 
          p_unavailable_value  => 'This Application is not available!' );
  end;
  /
  
  commit;
  

  Accessing that application with the browser using f?p=101 will now just lead to an This Application is not available message.
  
  
• New APEX_SESSION PL/SQL package:
  Using this package, an administrator (or developer) can remotely activate debug mode for a specific APEX session. The end user does not have to click on Debug in the developer toolbar and this even works when debugging for the application is not enabled. All the administrator needs, is the session ID. The following example shows how it works - debug level 9 (the highest) is being enabled for one very APEX session.

  begin
    apex_session.set_debug( 2364850177865, 9 );
  end;
  /
  
  commit;
  

  All subsequent events in this sessions will be logged with debug level 9. We can query the APEX_DEBUG_MESSAGES view in order to see what happens.

    select APPLICATION_ID, PAGE_ID, ELAPSED_TIME, MESSAGE_TIMESTAMP, substr(message, 1,40)  
      from apex_debug_messages 
     where session_id = 2364850177865
  order by MESSAGE_TIMESTAMP;
  
  APPLICATION_ID    PAGE_ID ELAPSED_TIME MESSAGE_TIMESTAMP                  SUBSTR(MESSAGE,1,60)                                        
  -------------- ---------- ------------ ---------------------------------- ------------------------------------------------------------
            4050          3 ,041524      18.01.17 04:43:36,715794000 -08:00 DEPRECATED: public_check_authorization                      
             101            ,003383      18.01.17 05:50:34,536311000 -08:00 Reset NLS settings                                          
             101            ,004057      18.01.17 05:50:34,536965000 -08:00 alter session set  NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AM
             101            ,004553      18.01.17 05:50:34,537481000 -08:00 ...NLS: Set Decimal separator="."                           
             101            ,00469       18.01.17 05:50:34,537621000 -08:00 ...NLS: Set NLS Group separator=","                         
             101            ,004817      18.01.17 05:50:34,537751000 -08:00 ...NLS: Set g_nls_date_format="DD-MON-RR"                   
             101            ,004968      18.01.17 05:50:34,537899000 -08:00 ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM
             101            ,005101      18.01.17 05:50:34,538033000 -08:00 ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF
             101            ,005308      18.01.17 05:50:34,538242000 -08:00 no characterset conversion needed             
               :                  :      :                                  :
  

  The SET_TRACE procedure activates SQL trace remotely; it's the equivalent of appending p_trace=yes to the URL. As always, the trace file is written to the user-dump-destination directory on the database server.

As already announced with the APEX 5.0 release, APEX_PLSQL_JOB (which relied on the old DBMS_JOB package) has been removed. APEX_PLSQL_JOB was used to run PL/SQL logic in the background. Since Oracle10g (that means, since 2004) this is better done using the DBMS_SCHEDULER PL/SQL package; with superior configuration options and job monitoring. Note that your applications' parsing schema needs the CREATE JOB privilege in order to use DBMS_SCHEDULER.

Here is an example for some code to run PL/SQL logic in background with APEX_PLSQL_JOB:

DECLARE
    l_sql  VARCHAR2(4000);
    l_job  NUMBER;
BEGIN
    l_sql := 'begin plsql_to_run_as_job( :P1_ITEM ); end;';
    l_job := APEX_PLSQL_JOB.SUBMIT_PROCESS(
        p_sql    => l_sql,
        p_status => 'Background process submitted' );

    :P1_JOB_ID := l_job;
END;

The following code does the same; but uses DBMS_SCHEDULER.

DECLARE
    l_sql varchar2(4000);
    l_job varchar2(4000);
BEGIN
    l_sql := 'begin plsql_to_run_as_job( ' || 
              dbms_assert.enquote_literal( :P1_ITEM ) || 
             ' ); end';
    l_job := dbms_scheduler.generate_job_name ( 
        prefix => 'MY_APEX_JOB' );

    dbms_scheduler.create_job(
        job_name =>   l_job,
        job_type =>   'PLSQL_BLOCK',
        job_action => l_sql,
        comments =>   'Background process submitted',
        enabled =>    true );
END;

Applications or PL/SQL code using APEX_PLSQL_JOB should therefore be migrated to use DBMS_SCHEDULER. That should be done even when still on APEX 5.0 or 4.2. Having this done, applications will run without issues after the upgrade to 5.1.

Zurück zur Community-Seite