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.
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
Learning is not a spectator sport 
> 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.