Get number from Oracle version

Question

I'm interested how I can get only the number from the Oracle version?

select * from v$version where banner like 'Oracle%';

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production  


BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    
         0

Answer 1

There are many ways.

You could check VERSION in V$INSTANCE view:

SQL> SELECT version FROM V$INSTANCE;

VERSION
-----------------
12.1.0.1.0

You could check VERSION in PRODUCT_COMPONENT_VERSION view:

SQL> SELECT VERSION
  2  FROM PRODUCT_COMPONENT_VERSION
  3  WHERE product LIKE '%Oracle Database%';

VERSION
--------------------------------------------
12.1.0.1.0

You could use DBMS_DB_VERSION package:

SQL> set serveroutput on
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);
  3  END;
  4  /
12.1

PL/SQL procedure successfully completed.

---

UPDATE To get both the product and version as separate columns, you could do:

SQL> SELECT product AS edition,
  2         version
  3  FROM PRODUCT_COMPONENT_VERSION
  4  WHERE product LIKE '%Oracle Database%';

EDITION                                  VERSION
---------------------------------------- ----------
Oracle Database 12c Enterprise Edition   12.1.0.1.0

Answer 2

Just for fun, using the query you presented:

select 
   banner, 
   regexp_substr(banner,'[0-9]*\.[0-9]*\.[0-9]*\.[0-9]*') version,
   substr(banner,1, instr(banner,'Release')-2) edition
from v$version 
where banner like 'Oracle%';

PS: I don't use the 5th part of the version in common conversations. And many others do the same.