Return NULL if the Column Does Not Exist

By Laurent Schneider

It is a very common task for a DBA to be asked to create scripts that work on every version. But how do you return NULL if a column does not exist?  Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS as follows:

SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,
column_name,retention_type from user_lobs;

View created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1 C YES
T2 C NO

Now try running this on an "antique" version of Oracle:

SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0

SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,column_name,retention_type from user_lobs;

Warning: View created with compilation errors.

SQL> select * from v where table_name in ('T1','T2');
select * from v where table_name in ('T1','T2')
*
ERROR at line 1:
ORA-04063: view "SCOTT.V" has errors

Obviously the RETENTION_TYPE did not exist in that version. Let�s default this to NULL !

It really is a surprisingly simple workaround, isn't it?