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