Getting Oracle version – new utility on 18c oraversion

While scripting on an environment with different Oracle versions, I often needed to get the Oracle version for the target database as usually the SQL to be executed is version depended.

I used different methods to grab the database  version some you can see below:

SQL> select 
substr(
substr(
banner, 
instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1, 
instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1
),
1, 
instr(
substr(
banner, 
instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1,
instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1
),
'.'
)-1
) version
from v$version
where rownum = 1
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

VERSION
--------------------------------------------------------------------------------
18

Or like this

SQL> select substr(version,1,instr(version,'.')-1) version from product_component_version where product like 'Oracle%';

VERSION
--------------------------------------------------------------------------------
18

But on Oracle 18c there is a new utility called oraversion which you can get the same result as the above queries.

[oracle@server01 ~]$ oraversion
This program prints release version information.
These are its possible arguments:
-compositeVersion: Print the full version number: a.b.c.d.e.
-baseVersion: Print the base version number: a.0.0.0.0.
-majorVersion: Print the major version number: a.
-buildStamp: Print the date/time associated with the build.
-buildDescription: Print a description of the build.
-help: Print this message.
[oracle@server1 ~]$

[oracle@server01 ~]$ oraversion -majorVersion
18
[oracle@server01 ~]$

This could be somewhat useful but I though it was worth sharing.

Until next time.

Elisson Almeida

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.