Vishal Gupta's Blog

SQL*Plus Variable defined by default

Posted by Vishal Gupta on May 20, 2012

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.

About these ads

3 Responses to “SQL*Plus Variable defined by default”

  1. [...] List of #Oracle SQL*Plus Predefined Variables in Different Releases [...]

  2. Levi said

    I like tthe valuable info you supply in your articles.
    I’ll bookmark your blog and take a look
    at again right here regularly. I am moderately certain I’ll learn
    plenty of new stuff proper here! Goodd luck forr the following!

  3. Hi,

    What about if your database is down and you want to start it up, is there any way to se SQLPROMPT to show whatever is $ORACLE_SID is set to?

    Scenario is, your database is down you start sqlplus and then do conn / as sysdba, under this scenario you are not able to query any views. Also, I still got some Oracle8 DB that does not support some of the new pre-defined variables. Do you know of any way to be able to use $ORACLE_SID and other environment variable values. Only way I can think of at the moment it to invoke sqlplus from a script.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: