Return NULL if the column does not exist

It is a very common challenge for a dba to create scripts that work on every version.

How do you return NULL if a column does not exists?

Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS.


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

Let’s imagine I try to run 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 !

SQL> create or replace function retention_type return varchar2 is
begin return null; end;
/

Function created.

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

Very simple workaround, is not it?

6 thoughts on “Return NULL if the column does not exist”

  1. Anon

    Another approach would be to create the view as follows:

    create or replace force view v
    as
    select table_name, column_name, cast(null as varchar2(30)) as retention_type
    from user_lobs

    In this case you would have different views for different database versions, but no need to create a dummy function.

  2. Laurent Schneider Post author

    yes, you will have a different view for different versions (that is exactly what I want to avoid), but with my option you have only one view and one function (that function will not be unused in 11.2.0.2 as the column exists)

    another approach would be execute immediate and catching exceptions in a table function in plsql

  3. Laurent Schneider Post author


    SQL> create or replace force view v as select table_name,column_name,retention_type from user_lobs;
    View created.
    SQL> create or replace function retention_type return varchar2 is
    begin return null; end;
    /
    Function created.
    SQL> select version from v$instance;

    VERSION
    -----------------
    11.2.0.2.0
    SQL> select * from v

    TAB COL RETENTION_TYPE
    --- --- --------------
    T1 C YES
    T2 C NO

    As the column exists, the column is returned in 11.2.0.2

Comments are closed.