Updating my APEX 20.2 installation

Posted by

One of (or should I say “another of”!) the very cool features of Application Express (APEX) is that by being a database-centric software installation, patching of the product can be done very efficiently and easily by simply loading fresh versions of the underlying PL/SQL source.

The reason loading PL/SQL source is such a good thing is that when you load PL/SQL source that is unchanged the database can simply treat that as a “no-op” which

  • makes loading the unchanged PL/SQL faster,
  • avoids invalidation and recompilation impacts

We can see that with this simple demo.


SQL> create or replace
  2  procedure MY_BIG_PROC is
  3  begin
  4    dbms_output.put_line(1);
  5    dbms_output.put_line(2);
  6    dbms_output.put_line(3);
  7    dbms_output.put_line(4);
...
...
996    dbms_output.put_line(993);
997    dbms_output.put_line(994);
998    dbms_output.put_line(995);
999    dbms_output.put_line(996);
1000    dbms_output.put_line(997);
1001    dbms_output.put_line(998);
1002    dbms_output.put_line(999);
1003    dbms_output.put_line(1000);
1004  end;
1005  /

Procedure created.

SQL>
SQL> select last_ddl_time
  2  from   user_objects
  3  where  object_name = 'MY_BIG_PROC';

LAST_DDL_TIME
-------------------
05/11/2020 11:11:29

1 row selected.

[wait 30 seconds]

SQL> create or replace
  2  procedure MY_BIG_PROC is
  3  begin
  4    dbms_output.put_line(1);
  5    dbms_output.put_line(2);
  6    dbms_output.put_line(3);
  7    dbms_output.put_line(4);
...
...
996    dbms_output.put_line(993);
997    dbms_output.put_line(994);
998    dbms_output.put_line(995);
999    dbms_output.put_line(996);
1000    dbms_output.put_line(997);
1001    dbms_output.put_line(998);
1002    dbms_output.put_line(999);
1003    dbms_output.put_line(1000);
1004  end;
1005  /

Procedure created.

SQL>
SQL> select last_ddl_time
  2  from   user_objects
  3  where  object_name = 'MY_BIG_PROC';

LAST_DDL_TIME
-------------------
05/11/2020 11:11:29

1 row selected.


I re-loaded the PL/SQL procedure, but since it was not altered, no DDL change was recorded. As a consequence, when it comes to patching APEX, the patches that are distributed can be a cumulative set of fixes but applying those patches to your APEX installation will be just as efficient as if you had cherry-picked just the changes that were applicable to your own instance.

But the real benefit here is not the time it takes to apply a patch but that as bugs are reported and subsequently fixed/tested, the affected PL/SQL components can be almost immediately added to a patch and made available to customers. Thus rather than customers having to either

  • download lots of small patches and keep a record of which patches are applied and which are not, or
  • wait weeks or months for a large bundle of fixes to be consolidated and published,

in the majority of instance you can simply apply a single patch that contains timely fixes to issues, and that patch will be expanded over time to cover any new issues that arise in APEX.

For example, I installed APEX on my own machine last week and applied PSE 32006852 during that installation. A couple of days later, I checked the Known Issues documentation and saw that the PSE had been updated, so I downloaded it and applied the updated version. This is all tracked within my APEX instance.


SQL> select PATCH_NUMBER,PATCH_VERSION
  2  from   APEX_PATCHES;

PATCH_NUMBER PATCH_VERSION
------------ -----------------------------
    32006852 2020.10.23
    32006852 2020.10.28

Thus all I need to do to keep my APEX installation fully patched is keep an eye on the Known Issues page and the PSE from time to time.

If you’re wondering: “How do I know if I need to get a new patch version?”, it is stamped right there at the top of the README for the patch.

image

So it looks like for me, there are some fixes available because the patch version is my database is 2020.10.28. So I picked up the fresh PSE and applied it as per the README.


SQL> conn /@db19_pdb1 as sysdba
Connected.

SQL> @catpatch

. ORACLE
.
. Application Express (APEX) 20.2.0.00.20
. Patch Set Exception 32006852
........................................

APEX_VERSION
------------------------------
APEX_SCHEMA
--------------------------------------------------------------------------------------------------------------------------------
20.2.0.00.20
APEX_200200



PL/SQL procedure successfully completed.


Session altered.


APEX_SCHEMA
--------------------------------------------------------------------------------------------------------------------------------
APEX_200200

... Disabling Jobs
... disabling ORACLE_APEX_AUTOMATIONS
... disabling ORACLE_APEX_BACKUP
... disabling ORACLE_APEX_DAILY_MAINTENANCE
... disabling ORACLE_APEX_DICTIONARY_CACHE
... disabling ORACLE_APEX_ISSUE_EMAILS
... disabling ORACLE_APEX_MAIL_QUEUE
... disabling ORACLE_APEX_PURGE_SESSIONS
... disabling ORACLE_APEX_REST_SOURCE_SYNC
... disabling ORACLE_APEX_WS_NOTIFICATIONS

PL/SQL procedure successfully completed.


Session altered.

...patch_32063008.sql

Table altered.


Table altered.


Grant succeeded.

...wwv_flow_db_env_detection.plb

Package body created.

No errors.
... wwv_flow_db_version is up to date

PL/SQL procedure successfully completed.

...wwv_flow_asn1.sql

Package created.

No errors.
...wwv_flow_public_key_util.sql

Package created.

No errors.
...wwv_flow_asn1.plb

Package body created.

No errors.
...wwv_flow_public_key_util.plb

Package body created.

No errors.
...wwv_flow_meta_data

Package body created.

No errors.
...wwv_flow_authentication_social.plb

Package body created.

No errors.
...wwv_flow_error

Package body created.

No errors.
...wwv_flow_escape

Package body created.

No errors.
...wwv_flow_exec_web_src_utils.plb

Package body created.

No errors.
...wwv_flow_ir_render

Package body created.

No errors.

Package body created.

No errors.
...wwv_flow_page

Package body created.

No errors.
...wwv_flow_web_src_sync.plb

Package body created.

No errors.
...wwv_flow_data_export.plb

Package body created.

No errors.
...wwv_flow_xlsx.plb

Package body created.

No errors.
...wwv_flow_card_region

Package body created.

No errors.
...patch_32077747.sql

1 row updated.


Commit complete.

...devpatch.sql

Session altered.


Grant succeeded.


Session altered.

...patch_32077573.sql

View created.


Trigger created.

No errors.
...patch_32078187.sql

1 row updated.


1 row updated.


1 row updated.


Commit complete.

...wwv_flow_web_src_dev.plb

Package body created.

No errors.

PL/SQL procedure successfully completed.

...patch_32001315.sql

1 row updated.


Commit complete.


PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.

... Enabling Jobs

PL/SQL procedure successfully completed.

...Validating APEX
...(11:29:34) Starting validate_apex for APEX_200200
...(11:29:34) Checking missing sys privileges
...(11:29:34) Re-generating APEX_200200.wwv_flow_db_version
... wwv_flow_db_version is up to date
...(11:29:34) Checking invalid public synonyms
...(11:29:35) Key object existence check
...(11:29:35) Setting DBMS Registry for APEX to valid
...(11:29:35) Exiting validate_apex

PL/SQL procedure successfully completed.

...Recompiling invalid public synonyms

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch 32006852
Elapsed: 00:00:04.72

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0


Because a good portion of those fixes are probably already contained in the previous PSE update, it only took 5 seconds! And easy as you like, I’m now fully up to date.


SQL> select PATCH_NUMBER,PATCH_VERSION
  2  from   APEX_PATCHES;

PATCH_NUMBER PATCH_VERSION
------------ ---------------------------
    32006852 2020.10.23
    32006852 2020.10.28
    32006852 2020.10.29

2 comments

  1. > The reason loading PL/SQL source is such a good thing is that when you load PL/SQL source that is unchanged the database can simply treat that as a “no-op” which

    That is quite a nice thing and does save a lot of time when some application scripts are deployed.
    The other day I was compiling something in a common user schema as a local user. I was surprised that I got a ‘Procedure created’ message rather than ORA-1031. It turns out that Oracle also has a switch to disable that behavior:

    [oracle@localhost ~]$ oerr ora 10523
    10523, 00000, “force recreate package even if definition is unchanged”
    // *Cause:
    // *Action: Set this event only under the supervision of Oracle development
    // *Comment: Changes behaviour of create or replace package, procedure,
    // function or type to force recreation of the object even if its new
    // definition exactly matches the old definition.
    // No level number required.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.