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

Question

I need to develop a way to determine if Oracle is 11g or newer, because essentially I need to know if DB supports PIVOT.

Getting the version number if it’s not so difficult, so I got:

select * from v$version 

Unfortunately, this does not give a simple number, but 5 entries, including bites and various 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 has 11 g or higher, or even better if the database supports the PIVOT operator?

Thanks!

Answer 1

As one of the ways to determine the PIVOT version support for your Oracle: there is no need to query V $ RESERVED_WORDS to find out if the reserved word PIVOT exists or not.

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

---

If you are writing code (as you mentioned in the comment on @Ben's answer, specify I'm creating a procedure that generates and executes SQL statements for a datamart ), which will use the features of a particular version that you could (and prefer the approach) use dbms_db_version 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; 

Answer 2

I hope that the next steps will help you.

Check Oracle Version

Oracle Database Software Release Identification

Answer 3

I'm a little confused about why you don't know which version of Oracle you are using already.

However, you can use the PRODUCT_COMPONENT_VERSION system view to get the current version. This is what Oracle recommends checking the current release number .

 select * from PRODUCT_COMPONENT_VERSION 

So you can see the result; It works in SQL Fiddle .