Application Express – the PSE update

I published a post a couple of days ago about how due to the architecture of PL/SQL and hence Application Express, we can rapidly deliver and deploy updates to the core APEX product to deliver timely fixes to the APEX community.

Because a single patch may now evolve over time to contain additional fixes, long time friend of the APEX community Peter Raganitsch then made the following observation on Twitter:

I can’t speak for whether this will happen in future, but I decided I could help APEX customers with a little database magic to get them closer to this utopia .

Using the preprocessor feature of external tables, it is easy to get the output from any OS command to be available inside the database as if it were a standard table. Armed with that knowledge, and assuming that you have a Oracle Customer Support Identifier (CSI) which is a given if you are interested in downloading and applying patches, we can craft an interrogation of the APEX patch README file on support.oracle.com that is associated with the patch.

Here is a my preprocessor script. I’ve done this for Windows but of course can easily be ported to a Unix equivalent.

@echo off
set SystemRoot=C:\WINDOWS
set PATH=x:\bin;x:\bin\usr\local\wbin;%PATH%
cd /d x:\temp
del /q README.txt > nul
del /q patch.xml  > nul
del /q patch.aru  > nul
wget --quiet --http-user=MY_CSI_USER --http-password=MY_CSI_PASS --no-check-certificate --output-document=patch.xml "https://updates.oracle.com/Orion/Services/search?bug=32006852"
grep request_id patch.xml | grep request_id patch.xml | awk --field-separator=^> "{print $2}" | awk --field-separator=^< "{print $1}" > patch.aru
for /F "delims=" %%i in (patch.aru) do ( wget --quiet --http-user=MY_CSI_USER --http-password=MY_CSI_PASS --no-check-certificate --output-document=README.txt "https://updates.oracle.com/Orion/Services/download?type=readme&aru=%%i" )
grep "^# PATCH_VERSION" README.txt | awk "{print $4}"

This relatively simple script will

• connect to the Oracle Support updates page with my CSI details,
• find the system generated ARU number for the patch,
• download the README file for that ARU number,
• extracts the PATCH_VERSION line to get the version details.

Once I have a working script, I can wrap that into an external table and I’m good to go

SQL> create table apex_pse (
  2   version varchar2(64)
  3  )
  4  organization external
  5  (  type oracle_loader
  6     default directory temp
  7     access parameters
  8     ( records delimited by newline
  9       preprocessor  bin:'run_os.bat'
 10     )
 11     location ( temp:'dummy.txt' )
 12  ) reject limit unlimited ;
 
 Table created.

SQL> select * from apex_pse;

VERSION
-----------------------------
2020.10.29

Now I can easily run a query each day, week, month etc to check on my APEX installation, or of course I could build a little APEX application around it.

SQL> select
  2    case
  3      when installed = available
  4        then 'UP TO DATE'
  5        else 'NEW VERSION EXISTS'
  6     end status
  7  from
  8  ( select max(patch_version) installed
  9    from   apex_patches
 10    where  patch_number = '32006852'
 11  ),
 12  ( select version available from apex_pse )
 13  /

STATUS
------------------
UP TO DATE

Caveat: Obviously we’re only dealing with PSE 32006852 here, and you should always be checking the official APEX documentation for updates on fixes and patches.

(and if you’re wondering why the picture for this post is a spider…well, its because to get the patch information we are using a …. wait for it …. web crawler)