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