Tuesday, 6 August 2019

What version is my RAD stack?

A common follow up clarification on forums is regarding the version of the relevant tool.
Questions relating to Oracle APEX could be impacted by the APEX version, the database version, and perhaps the ORDS version - in addition to what browser is being used.

The information on the RAD stack can be resolved in one (concatenated) SQL query.

APEX was easy, there is a simple one row view, which ultimately translates to a function returning a literal.
Same with ORDS, I didn't even bother with a scalar subquery when I put it all together.

The Oracle database version had a few options, each with nuance.
  1. v$instance is not available to everyone
  2. dbms_db_version package variables are not accessible to SQL, unless you use the WITH clause, which is only available in 12c. And I'm not going to write a query based on all_source. It also doesn't provide the version granularity I expected.
  3. v$version returns a few rows in the Express Edition. In Enterprise Edition, it is a different "banner_full" column that contains the dot release you really need - among other data
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.2.0.0.0
  4. I hadn't heard of product_component_version, but it's a sys view that does all the string processing you need on v$version, but has the same XE/EE column differences. It also only returns one row when not in Express Edition.
So I ended up with the following, executed here in Express Edition.
col apex_version format a15
col ords_version format a15
col db_version format a15

select 
  (select version_no from apex_release) as apex_version
 ,ords.installed_version                as ords_version
 ,(select version /* column name 'version_full' in Standard|Enterprise Edition */
   from product_component_version
   where product like 'Oracle%')        as db_version
from dual;

APEX_VERSION    ORDS_VERSION    DB_VERSION    
--------------- --------------- ---------------
19.1.0.00.15    3.0.6.176.08.46 11.2.0.2.0 
But then I decided it would look better as rows, executed here on an Enterprise Edition database.
col tool format a10
col version_no format a20

select 'APEX' tool, version_no from apex_release
union all
select 'ORDS', ords.installed_version from dual
union all
select 'Database', version_full /* column name 'version' in XE */
from product_component_version
where product like 'Oracle%';

TOOL       VERSION_NO          
---------- --------------------
APEX       18.2.0.00.12        
ORDS       18.4.0.r3541002     
Database   18.3.0.0.0    
APEX and ORDS version is also available form within the App Builder, under Help -> About.

APEX Help -> About

I was curious as to how the WITH function would look, though I had trouble executing it.
with function ver return varchar2 is
  begin
    return dbms_db_version.version || '.' || dbms_db_version.release;
  end;
select ver from dual;
And it returns 18.0, when I expected the 'version_full' value of 18.3.

References

Forum question - How to check the database version
Marko - What is my current ORDS version
Martin - What is my current APEX Version

5 comments:

Robert Schäfer said...

Hi Scott,

great blog, always a pleasure to read!

One additional point could be of interest: the displayed APEX version doesn't represent the actual patchlevel (in detail). I.e. for APEX19.1 exists the Patch Bundle No. 29369869. Currently this patch bundle is updated constantly (about every 3-4 weeks) - the patch number stays the same, new bugfixes will be added.
When applying this patch the APEX version also stays the same (19.1.0.00.15). The applied patches could be queried via:

select * from apex_190100.wwv_flow_platform_prefs where name like '%PATCH%';

Best regards
Robert

Anton Scheffer said...

You can use this to get version_full when available
select 'Database' tool
, coalesce( x.version_full, x.version ) version_no
from xmltable( '*' passing xmltype( cursor( select pcv.*
from product_component_version pcv
where pcv.product like 'Oracle%'
)
)
columns version varchar2(100) path '/ROWSET/ROW/VERSION'
, version_full varchar2(100) path '/ROWSET/ROW/VERSION_FULL'
) x

Anton

Scott Wesley said...

Thanks, Robert, it's always fun to write.

That's terribly granular.

Nifty little trick, Anton, but it ora-600'd my 11g XE instance.

Robert Schäfer said...

This kind of granularity can be very helpful when you hit a strange, unknown APEX error and you'll have to know if you're working with version "19.1.0.00.15" or "19.1.0.00.15 with applied patchset".

Well ok, your database has a patch level from 2011 - i understand your point;-)))

Anton Scheffer said...

The XML variant doens't work on my 11g XE neither.
But this works:
select 'Database' tool
, ( select coalesce( version_full, version )
from product_component_version
where product like 'Oracle%'
) version_no
from ( select '' version_full from dual )