Vishal Gupta's Blog

Earlier i used to maintain different sql script files for one function for every database version. As some of the older release dont have some of columns available in new release’s view/tables. I started updating my scripts to maintain a single script using Tanel Poder’s  snapper script’s idea of using SQL*Plus variables to automatically comment out the version specific code in the script (if its not applicable for logged in database version), so that it does not generate the syntax error.

So initialize the variables (_IF_ORA_9iR2_OR_HIGHER, _IF_ORA_10gR1_OR_HIGHER etc) with single line comment “–” and if version of logged in database is applicable, then remove the “–” from its value and set relevant variable to empty string (“”). I was doing this via my header.sql script which is called in every script. Later i moved that bit of code to login.sql, as i wanted it execute only during initial database connection instead of during every execution of every script.

For example, now i can use the following SELECT statement even on 9i databases where SQL_ID column does not exists in v$session view.

                          SELECT sid
&_IF_ORA_10gR1_OR_HIGHER       , sql_id
                          from v$session

I used to get the version of logged in database using a query from v$version and parsing the output. I recently noticed that SQL*Plus by default sets given below variables in every sqlplus session at logon time. This also includes the _O_RELEASE which has the database version in it. Now i can make use of this variable, instead of querying from v$version and parsing the output myself.

In 9.2.x.x release

• _CONNECT_IDENTIFIER   – This is set to the value used to connect after @ in the connection string (user/password@connection_identifer). So if you are using TNS_ALIAS, it will be set to the alias. If you are using EZConnect format string, it will be set to that.
• _SQLPLUS_RELEASE – This is the sqlplus version used to connect to the database.
• _EDITOR –
• _O_VERSION – Oracle database version banner
• _O_RELEASE – Oracle Release version.

From 10gR1 onwards (all above and following)

• _DATE – Today’s date
• _USER – User connected as.
• _PRIVILEGE – Privilege being used by current session (e.g SYSDBA etc)

See the output below from various version of SQL*Plus connection to their respective version databases. If sqlplus and database version are different, you will get the different values for _SQPLUS_RELEASE and _O_RELEASE variables, but you get the idea.

################# <strong>9204</strong> ########################
DEFINE _CONNECT_IDENTIFIER = "9204" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000400" (CHAR)

################# 9206 ########################
DEFINE _CONNECT_IDENTIFIER = "9206" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000600" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000600" (CHAR)

################# 9207 ########################
DEFINE _CONNECT_IDENTIFIER = "9207" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000700" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000700" (CHAR)

################# 9208 ########################
DEFINE _CONNECT_IDENTIFIER = "9208" (CHAR)
DEFINE _SQLPLUS_RELEASE = "902000800" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production" (CHAR)
DEFINE _O_RELEASE = "902000800" (CHAR)

################# 10103 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10103" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000300" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000300" (CHAR)</pre>

################# 10104 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10104" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000400" (CHAR)

################# 10105 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10105" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000500" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000500" (CHAR)

################# 10201 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10201" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)

################# 10202 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10202" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000200" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000200" (CHAR)

################# 10203 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10203" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000300" (CHAR)

################# 10204 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10204" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)

################# 10205 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "10205" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000500" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000500" (CHAR)

################# 11106 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "11106" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)

################# 11107 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "11107" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000700" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1101000700" (CHAR)

################# 11201 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "11201" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000100" (CHAR)

################# 11202 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "11202" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000200" (CHAR)

################# 11203 ########################
DEFINE _DATE = "17-MAY-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "11203" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000300" (CHAR)

Hope someone finds this useful.