CODEWITHSUNDEEP
oracle-databaseoracle11goracle12c
akki2346
akki2346

Reputation: 113

Oracle version check before running queries

I'm giving a functionality which works on 12c and has JSON related operations. but when database is 11g the JSON queries should be skipped. Need if condition for this but this should consider future approach as in it should be able to handle next oracle version. E.g JSON supported in 12c and above but not below with v$version I can compare string in if condition but it is static current implementation as follows

SELECT substr(banner, 1, 19)banner 
into x_version 
FROM v$version 
WHERE banner LIKE 'Oracle Database 12c%';

IF x_version = 'Oracle Database 12c' THEN ...

Upvotes: 1

Views: 1118

Answers (2)

Alex Poole
Alex Poole

Reputation: 191415

You could use conditional compilation, based on dbms_db_version values:

declare
$if not dbms_db_version.ver_le_11_2 $then
  l_var number;  
$end
begin
$if dbms_db_version.ver_le_11_2 $then
  null;
$else
  select json_value('{x:42}', '$.x') into l_var from dual;
  dbms_output.put_line('12c+: ' || l_var);
$end
end;
/

On 11gR2 this just gets:

PL/SQL procedure successfully completed.

On 12c it gets:

12c+: 42

PL/SQL procedure successfully completed.

Wrap any variable declarations that are only relevant to 12c+ operations, and any code that you only want to call in 12c and above, in conditions.

Of course, this will only work on version 11gR2 or above; if you need to be able to run (without doing anything) on 11gR1 then change the check to ver_le_11. Even that won't be recognised if you try to run on a 10g or earlier database though.

If you need to handle other earlier versions, you can check the version number directly (as @Wernfried showed):

declare
$if dbms_db_version.version >= 12 $then
  l_var number;  
$end
begin
$if dbms_db_version.version >= 12 $then
  select json_value('{x:42}', '$.x') into l_var from dual;
  dbms_output.put_line('12c+: ' || l_var);
$else
  null;
$end
end;
/

The $else null; part is only needed because I have nothing else in my block - I would get PLS-00103: Encountered the symbol "END" ... without it (in 11g or earlier). If there is other unconditional code then that isn't necessary, though it may still be clearer to include it.

Upvotes: 5

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

What about this?

DECLARE
    ver number;
BEGIN
    DBMS_OUTPUT.PUT_LINE ( 'DBMS_DB_VERSION.VERSION = ' || DBMS_DB_VERSION.VERSION );
    DBMS_OUTPUT.PUT_LINE ( 'DBMS_DB_VERSION.RELEASE = ' || DBMS_DB_VERSION.RELEASE );
    ver := (10*DBMS_DB_VERSION.VERSION + DBMS_DB_VERSION.RELEASE) / 10;
    DBMS_OUTPUT.PUT_LINE ( 'ver = ' || ver );
end;


DBMS_DB_VERSION.VERSION = 12
DBMS_DB_VERSION.RELEASE = 1
ver = 12.1

Or you may use

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_RDBMS_VERSION';

Upvotes: 2

Related Questions