Database version dependent SQL scripts

I've been working with Oracle databases since version 7.1 and over time I've built up quite a collection of scripts - some of which were improved as later versions of the database provided additional information in the data dictionary (in some cases columns were renamed completely).

However because I never know when I might be working on a system running an earlier version of Oracle I sometimes keep multiple versions of scripts, usually with a suffix of v7, v8, etc. However, wouldn't it be nice if you didn't have to specify the version suffix - if somehow the correct script was run automatically?

Here's how I do it...

However because I  never know when I might be working on a system running an earlier version of Oracle I sometimes keep multiple versions of scripts, usually with a suffix of v7, v8, etc. However, wouldn't it be nice if you didn't have to specify the version suffix - if somehow the correct script was run automatically?

Here's how I do it...

As an example, I have a simple script called find.sql that I use whenever I want to find a database object that matches a search string, like so:

@find USERS

OWNER         OBJECT_NAME                           OBJECT_TYPE
------------- ------------------------------------- ------------
PUBLIC        ALL_USERS                             SYNONYM
PUBLIC        DBA_USERS                             SYNONYM
PUBLIC        DBA_USERS_WITH_DEFPWD                 SYNONYM
PUBLIC        PROXY_USERS                           SYNONYM
PUBLIC        PROXY_USERS_AND_ROLES                 SYNONYM
PUBLIC        USER_USERS                            SYNONYM
PUBLIC        V$PWFILE_USERS                        SYNONYM
SYS           ALL_USERS                             VIEW
SYS           DBMS_LDAP_UTL.GET_SUBSCRIBED_USERS    CODE
SYS           USER_USERS                            VIEW

The find.sql script actually calls one of two other scripts (find.v7-8.sql or find.v9.sql) depending upon the version of Oracle in use because in Oracle9i and later  there is a dictionary view called ALL_PROCEDURES which I used  to enhance the code to also search for procedure or function names that match the search string.

So, the contents of find.sql are as follows

col script new_value script
set term off
select decode(substr(version,1,instr(version,'.')-1)
             , 7 , 'v7-8'
             , 8 , 'v7-8'
             , 'v9+') script
  from product_component_version
 where lower(product) like 'oracle%edition%';
set term on
@find.&script..sql

Let's break down the script to see how we can automatically get the version of Oracle and then call a different script depending upon the result.

First, we tell SQL*Plus that we want to define a new variable called script which will be derived from the result of a SQL query:

col script new_value script

Now we turn off the terminal output so that the result of the query where we select the version number of the database from PRODUCT_COMPONENT_VERSION is hidden. In this scenario  we're only interested in the number before the first period so we use substr to extract it. We then wrap this result in DECODE statement to specify the script will have a suffix of "v7-8" for versions 7 and 8, and "v9+" for all other versions.

set term off
select decode(substr(version,1,instr(version,'.')-1)
             , 7 , 'v7-8'
             , 8 , 'v7-8'
             , 'v9+') script
  from product_component_version
 where lower(product) like 'oracle%edition%';

Note: The result is aliased as "script" because that's the name of the column we specified would hold the value to be assigned to the variable script.

Finally, we turn the terminal output back on and use  "@" in conjunction with the variable script to call the correct SQL script. (The double period is needed because the first period signifies the end of the variable name, and the second period is the beginning of the file .sql extension).

set term on
@find.&script..sql

Of course, this technique can be adapted to check more than just the major version number or to check something entirely different such as the name of the user currently logged on or whether a particular database option was enabled.