Oracle Script to find out Oracle version

versions.sql


set showmode off echo off
set heading off pagesize 0 linesize 240 timing off feedback off recsep off
set termout off verify off
column sortfield noprint
spool version1.sql
select rpad('1',25) sortfield,
'select chr(10) || ''               Host_Name: '' || host_name from v$instance' || chr(10) ||
'union all' || chr(10) ||
'select ''             DB_Instance: '' || rpad(name,10) || ''DBID: '' || dbid
from v$database' || chr(10) ||
'union all'
from dual
union all
select '2' || substr(substr(comments,1,instr(comments,' Version')-1),1,24),
'select ''' || lpad(substr(comments,1,instr(comments,' Version')-1),24) ||': '' || substr(max(to_char(' || table_name ||
'_stage_date,''YYYYMMDD'') || rpad(' || table_name ||'_release,8) || ''  '' || ' || table_name || '_stage_date),9) from ' ||
table_name || chr(10) ||
'union all'
from dba_tab_comments where table_name like '%VERS'
and table_type = 'TABLE'
union all
select '3',
'select ''          Oracle_Version: '' || version || chr(10) || chr(10) from v$instance;'
from dual order by 1;
spool off
set linesize 80 termout on
@version1
set heading on pagesize 24 timing on feedback 6 recsep wrap
set verify on echo on showmode both



SQL> @version

SQL> set showmode off echo off
old: showmode BOTH

               Host_Name: apt-amd-02
             DB_Instance: ORCL      DBID: 1267852645
          Oracle_Version: 11.2.0.1.0

new: showmode BOTH