APEX 21.1.1 – the first round of improvements

Posted by

I posted late last year about how to query APEX patch updates directly from the database. Now that APEX 21 has come out, there is a new patch number and a new version naming system. Here are amended scripts to query where your APEX system is up to date with patches.

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=32598392"
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 """21.1."""$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_pse21 (
  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_pse21;


Now I can easily run a query each day, week, month etc to check on my APEX installation

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 )

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.