OraFAQ Forum: SQL & PL/SQL » Question about v$version

Home » SQL & PL/SQL » SQL & PL/SQL » Question about v$version (Oracle 10,11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Question about v$version [message #624845]

Fri, 26 September 2014 09:55

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

In one of my apps, I have a procedure that wants to know what version of the database is the application executing. Using sql like

select * from v$version;

I get something like:

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

but if I run this on a different DB

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

I can tell that the first execution is a 10g db while the second is an 11g db.

All I really want to know in my procedure is am I running 10g or 11g

I wrote something like this to extract the info

select SUBSTR(banner, INSTR(banner, '.', 1, 1) - 2, 2) db_version
  from (select banner from v$version where rownum = 1) data;

DB_VERSION
11

Is there a better way to extract out what DB version i'm running??

Report message to a moderator

Re: Question about v$version [message #624846 is a reply to message #624845]

Fri, 26 September 2014 10:10

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

and to be clear. I need a number returned such as 10, 11, 12, etc.

Report message to a moderator

Re: Question about v$version [message #624847 is a reply to message #624845]

Fri, 26 September 2014 10:10

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

select regexp_replace(version,'\..*') from v$instance

Report message to a moderator

Re: Question about v$version [message #624849 is a reply to message #624845]

Fri, 26 September 2014 10:14

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

DBMS_DB_VERSION is accessible to public so you can remove access to V$VERSION.
This package contains (among other things) 2 constants: VERSION and RELEASE, these are what you want:

SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

1 row selected.

SQL> exec dbms_output.put_line(dbms_db_version.version||'.'||dbms_db_version.release);
10.2

PL/SQL procedure successfully completed.

SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

1 row selected.

SQL> exec dbms_output.put_line(dbms_db_version.version||'.'||dbms_db_version.release);
11.2

PL/SQL procedure successfully completed.

[Updated on: Fri, 26 September 2014 11:00]

Report message to a moderator

Re: Question about v$version [message #624852 is a reply to message #624849]

Fri, 26 September 2014 10:51

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Roachcoach, thanks for that tip. It seems like I don't have access to v$instance. I was down talking to my DBA to grant me access BUT now maybe I don't need to.

Thanks Michel for your tip, I just added it to my procedure. That seems like a nice easy way to get the info I need.

Thanks again!!

Report message to a moderator