You may be lucky and work in an all-11g (or all-10g, etc) environment… but I have to manage everything from version 7 upwards. This makes it tricky to write any SQL scripts which can be guaranteed to run in all environments. Try this, for example, in 10g and everything will be fine:

select a.sid, a.serial#, substr(b.sql_text,1,40) as sql, b.hash_value, b.sql_id
from v$session a,
     v$sqlarea b
where a.sql_address=b.address;

But run it against a version 9 (or earlier) database, and you’ll merely get an ORA-00904, because “SQL_ID” is not a column in the V$SQLAREA view in those versions. Of course, I could remove that column from my query entirely: coding to the least-common denominator and mentioning only things which exist in all possible versions… but that would mean missing out on useful information when I run against 10g or 11g databases. Why miss out on knowing stuff when the information is available?

What I want, of course, is one SQL script that can run in 9i, 10g and 11g environments without error, returning me SQL_ID if it’s possible and skipping it if it’s not.

Well, a possible generic query to tell you what version of the database you are running on might look like this:

select
 case when banner like '%9i%' then '9i'
 when banner like '%10g%' then '10g'
 when banner like '%11g%' then '11g'
 else '0'
 end
 from v$version where banner like 'Oracle%';

If the result of that query is selected ‘into’ a variable, we can test that variable’s value in an anonymous PL/SQL block and do conditional execution accordingly. In other words, logically, you’d have this sort of thing:

if v_version = '9i' then
  --run version of SQLAREA query that misses out SQL_ID column;
elsif v_version = '10g' or v_version = '11g' then
  --run version of SQLAREA query that includes the SQL_ID column;
end if;

Of course, at this point, you’re writing an anonymous PL/SQL block and you’ve accordingly lost the ability to have the results of queries effortlessly displayed. The trick there is to declare a refcursor variable, have your query select its results into that variable and then have SQL*Plus print out the contents of the refcursor.

You also can’t mention ‘SQL_ID’ even in the v_version=’10g’ section of the code, because it will be spotted at compile time as being a reference to a column that doesn’t exist on a 9i database. An error will then be thrown before the 9i-flavour of the query even gets a chance to run. The workaround for that is to use dynamic SQL -wrap the code in quotes, basically- so that what is inside the quotes is only evaluated at run-time …and if the logic means one part of your code isn’t actually run, then any “errors” it contains won’t be triggered.

Taking those things into account, then, you might end up with code like this:

set autoprint on
variable y refcursor
declare
 v_version varchar2(5);
 v_sql9 varchar2(4000);
 v_sql10 varchar2(4000);
 p_results sys_refcursor;
begin

v_sql9 := 'select a.sid,
 a.serial#,
 substr(b.sql_text,1,40) as sql,
 b.hash_value
 from v$session a,
 v$sqlarea b
 where a.sql_address=b.address';

v_sql10:= 'select a.sid, 
 a.serial#,
 substr(b.sql_text,1,40) as sql,
 b.hash_value,
 b.sql_id
 from v$session a,
 v$sqlarea b
 where a.sql_address=b.address';

select
 case when banner like '%9i%' then '9i'
 when banner like '%10g%' then '10g'
 when banner like '%11g%' then '11g'
 else '0'
 end
 into v_version
 from v$version where banner like 'Oracle%';

if v_version = '9i' then
 open :y for v_sql9;
elsif v_version = '10g' or v_version = '11g' then
 open :y for v_sql10;
end if;
end;
/

Taking that apart, bit by bit:

set autoprint on
variable y refcursor

Autoprint enables the contents of bind variables to be automatically displayed, once a query has completed. The next line then creates such a variable, called “y”, which is declared to be of type ‘refcursor‘ (essentially, a resultset, containing multiple rows).

declare
 v_version varchar2(5);
 v_sql9 varchar2(4000);
 v_sql10 varchar2(4000);

Here, I simply create three varchar2 variables to hold the contents of the Oracle version, the 9i and the 10g version of the query. The fourth variable is declared as type sys_refcursor, so that multi-row results can be selected into it.

v_sql9 := 'select a.sid,
 a.serial#,
 substr(b.sql_text,1,40) as sql,
 b.hash_value
 from v$session a,
 v$sqlarea b
 where a.sql_address=b.address';

Now I set up the actual values for the version-dependent query variables. Note that they are wrapped in single quotes: this stops the PL/SQL compiler spotting that the v_sql10 one contains a reference to something that doesn’t actually exist in a 9i database. The 9i version omits reference to the SQL_ID column; otherwise, the two are identical.

select
 case when banner like '%9i%' then '9i' [...]

Now comes the version-detection query I mentioned before, only this time it selects its results into the v_version variable previously declared.

if v_version = '9i' then
 open :y for v_sql9;
elsif v_version = '10g' or v_version = '11g' then
 open :y for v_sql10;
end if;

This is the bit of code that does all the work: it executes one of the dynamic SQL statements, depending on the Oracle version detected, selecting its results into the ‘y’ bind variable I declared right at the start. When this query has completed, the contents of ‘y’ will be printed out automatically (because of the autoprint setting I made at the start, too).

Using the one SQL script, therefore, I can now get results from both 9i and 10g databases:

SQL> /
PL/SQL procedure successfully completed.
SID SERIAL# SQL HASH_VALUE SQL_ID
---------- ---------- ---------------------------------------- ---
------ -------------
 306 38043 select 207344822 77s0p3s65rp5q
 295 17502 INSERT /*+ append */ into scott.EMP 2033560799 0qtz8ftw
bb6z
 299 44668 INSERT /*+ append */ into scott.EMP 2033560799 0qtz8ftwmbb6z
 293 9141 INSERT /*+ append */ into scott.EMP 2033560799 0qtz8ftwmbb6z

SQL> /

PL/SQL procedure successfully completed.
 SID SERIAL# SQL HASH_VALUE
---------- ---------- ---------------------------------------- ---
------
 6 1 select con#,type#,condlength,intcols,rob 1937775682
 7 1 select o.owner#,o.name,o.namespace,o.rem 431456802
 213 11205 SELECT /*+ index(ORDER_INFO ORDER_INFO_P 1942381241
 283 56071 declare nm owa.vc_arr; vl owa. 1860200125

My choice of a ‘CASE’ statement means this code isn’t truly cross-version-compatible, because it won’t work in version 7 or 8i, and I haven’t bothered to write any code in this example that handles the ”not 9i, 10g or 11g” situation, though the v_version variable is set to ’0′ in those cases and could be dealt with if needed. But that’s trivial (I could always stick to the decode function if I really wanted to, instead of using “CASE”, for example)… and not something I feel the need to do right now!