Top 60 Oracle Blogs

Recent comments

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 Smile.

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 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
cd /d x:\temp
del /q README.txt
wget --quiet --http-user=MY_CSI_EMAIL_USERNAME --http-password=MY_CSI_PASSWORD --no-check-certificate --output-document=README.txt ""
grep PATCH_VERSION README.txt | awk "{print $4}"

This relatively simple script will

  • connect to the Oracle Support updates page with my CSI details,
  • download the README file ,
  • 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;


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  /


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)