/*
*  Based on Vishal Gupta script
*/

set echo off
set term off
set trimspool on
set tab off
set feed off
set arraysize 5000
set lines 250
set pages 5000
set verify off
set sqlblankline on

set serveroutput on


-- Set the terminal output off. Otherwise it puts the blank lines on console for 
-- query executed with noprint option below to version variable


define _IF_ORA_8i_OR_HIGHER="--"
define _IF_ORA_9iR2_OR_HIGHER="--"
define _IF_ORA_10gR1_OR_HIGHER="--"
define _IF_ORA_10gR2_OR_HIGHER="--"
define _IF_ORA_11gR1_OR_HIGHER="--"
define _IF_ORA_11107_OR_HIGHER="--"
define _IF_ORA_11gR2_OR_HIGHER="--"
define _IF_ORA_11202_OR_HIGHER="--"
define _IF_ORA_11204_OR_HIGHER="--"
define _IF_ORA_12cR1_OR_HIGHER="--"

define _IF_ORA_9iR2_OR_LOWER="--"
define _IF_ORA_10gR1_OR_LOWER="--"
define _IF_ORA_10gR2_OR_LOWER="--"
define _IF_ORA_11gR2_OR_LOWER="--"

col oraverion_8i_or_higher        new_value _IF_ORA_8i_OR_HIGHER     noprint
col oraverion_9iR2_or_higher      new_value _IF_ORA_9iR2_OR_HIGHER   noprint
col oraverion_10gR1_or_higher     new_value _IF_ORA_10gR1_OR_HIGHER  noprint
col oraverion_10gR2_or_higher     new_value _IF_ORA_10gR2_OR_HIGHER  noprint
col oraverion_11gR1_or_higher     new_value _IF_ORA_11gR1_OR_HIGHER  noprint
col oraverion_11107_or_higher     new_value _IF_ORA_11107_OR_HIGHER  noprint
col oraverion_11gR2_or_higher     new_value _IF_ORA_11gR2_OR_HIGHER  noprint
col oraverion_11202_or_higher     new_value _IF_ORA_11202_OR_HIGHER  noprint
col oraverion_11204_or_higher     new_value _IF_ORA_11204_OR_HIGHER  noprint
col oraverion_12cR1_or_higher     new_value _IF_ORA_12cR1_OR_HIGHER  noprint

col oraverion_9iR2_or_lower       new_value _IF_ORA_9iR2_OR_LOWER    noprint
col oraverion_10gR1_or_lower      new_value _IF_ORA_10gR1_OR_LOWER   noprint
col oraverion_10gR2_or_lower      new_value _IF_ORA_10gR2_OR_LOWER   noprint
col oraverion_11gR2_or_lower      new_value _IF_ORA_11gR2_OR_LOWER   noprint


SELECT CASE WHEN &&_O_RELEASE >= 0801000000 THEN '' ELSE '--' END oraverion_8i_or_higher
     , CASE WHEN &&_O_RELEASE >= 0902000000 THEN '' ELSE '--' END oraverion_9iR2_or_higher
     , CASE WHEN &&_O_RELEASE >= 1001000000 THEN '' ELSE '--' END oraverion_10gR1_or_higher
     , CASE WHEN &&_O_RELEASE >= 1002000000 THEN '' ELSE '--' END oraverion_10gR2_or_higher
     , CASE WHEN &&_O_RELEASE >= 1101000000 THEN '' ELSE '--' END oraverion_11gR1_or_higher
     , CASE WHEN &&_O_RELEASE >= 1101000700 THEN '' ELSE '--' END oraverion_11107_or_higher
     , CASE WHEN &&_O_RELEASE >= 1102000000 THEN '' ELSE '--' END oraverion_11gR2_or_higher
     , CASE WHEN &&_O_RELEASE >= 1102000200 THEN '' ELSE '--' END oraverion_11202_or_higher
     , CASE WHEN &&_O_RELEASE >= 1102000400 THEN '' ELSE '--' END oraverion_11204_or_higher
     , CASE WHEN &&_O_RELEASE  < 1001000000 THEN '' ELSE '--' END oraverion_9iR2_or_lower
     , CASE WHEN &&_O_RELEASE  < 1002000000 THEN '' ELSE '--' END oraverion_10gR1_or_lower
     , CASE WHEN &&_O_RELEASE  < 1101000000 THEN '' ELSE '--' END oraverion_10gR2_or_lower
     , CASE WHEN &&_O_RELEASE  < 1201000000 THEN '' ELSE '--' END oraverion_11gR2_or_lower
     , CASE WHEN &&_O_RELEASE >= 1201000000 THEN '' ELSE '--' END oraverion_12cR1_or_higher
  FROM dual
  ;

set term on