How to test if my Oracle DB version supports the PIVOT function?

Question

I need to work out a way to determine if Oracle is 11g or newer, because essentially I need to know if the DB I'm running on supports PIVOT.

Getting the version number ifself is not that difficult, so I've got:

select * from v$version 

Unfortunately, this does not give a simple number, but 5 records including stings and various different version numbers:

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

So, is there a way to determine if my Oracle is 11g or higher, or even better, if the DB supports the PIVOT statement?

Thanks!

Answer 1

As one of the methods of determining whether your Oracle version supports PIVOT or not is to query the V$RESERVED_WORDS view to find out if the reserved word PIVOT is there or not.

SQL> select keyword
  2    from v$reserved_words
  3   where keyword = 'PIVOT'
  4  ;

KEYWORD
---------
PIVOT

---

If you are writing a code (as you've mentioned in the comment to the @Ben's answer, quote I'm creating a procedure that generates and executes SQL statements for a datamart) that is going to take advantage of version specific features you could (and it's preferred approach) use dbms_db_version package for conditional compilation. For instance:

 create or replace some_proc
 is
 begin
   $if dbms_db_version.ver_le_10_2 -- for example
   $then
     -- features up to 10g r2 version 
   $else
     -- current release  
   $end
 end;