Querying database table columns that don’t exist

I had a recent client request that, distilled down to its very basics, was ‘could you please write some example code that demonstrates to us how we can generically modify our existing application API to query a database table or view for fields/columns regardless of whether they exist or not’.   Yes I did write that, and yes, that was the requirement.

So, here we go.

Here is some sample data from a test table:

SQL>
SQL>
SQL> select * from tblTest;

        ID          A B          C         D
---------- ---------- ---------- --------- -
     13654          1 one        31-JUL-16 Y
     13655          2 two        01-AUG-16 Y
     13656          3 three      02-AUG-16 Y
     13657          4 four       03-AUG-16 N

SQL>
SQL>

The client database API is implemented as a set of Persistent Stored Modules, or PSM’s in SQL parlance, that was the sole interface to the middleware. Almost all of the base tables or views etc defined in the database logical schema were not exposed to the middleware, nor other processes. The application architecture and security policy mandated that this remained the case. The client wanted to pass in a set of comma delimited fields where, ultimately, if the field name existed as a table column name, the data was returned in a result set, and if not, a named column was included in the result set containing all NULL values. In pseudo code, for the test table as shown above, ‘SELECT A,B,C, V,H FROM tblTest’ should, through the API, return:

         A B          C         H V
---------- ---------- --------- - -
         1 one        31-JUL-16
         2 two        01-AUG-16
         3 three      02-AUG-16
         4 four       03-AUG-16

Clearly columns H and V do not exist in the test table definition, and therefore the SQL snippet above is invalid as written. The requirement however is to return columns H and V, with NULL content. So, how can this be done?

The massaged version of the code I wrote is shown below. The key point is an OUTER JOIN on USER_TAB_COLS, and an Oracle cursor executing in-part dynamically created content with NULL/missing columns aliased.

SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure procTest(id in number := null, fieldList in varchar2, rc out sys_refcursor) is
  2   selectList varchar2(100);
  3   tableName constant varchar2(30) := 'TBLTEST';
  4  begin
  5  with x as (select fieldList cols, '[^,]+' regex FROM DUAL),
  6       s as (
  7              select trim(regexp_substr(x.cols, x.regex, 1, level)) column_name
  8               from x
  9                connect by regexp_substr(x.cols, x.regex, 1, level) is not null
 10            )
 11   select listagg(case when utc.column_name is null then 'null as ' else null end || s.column_name, ',')
 12             within group (order by 1) colname
 13    into selectList
 14     from s
 15       left outer join user_tab_cols utc
 16         on   utc.column_name = s.column_name
 17          and utc.table_name = tableName;
 18    open rc for 'select ' || selectList || ' from ' || tableName || ' where id = nvl(:id, id)' using id;
 19  end;
 20  /

Procedure created.

SQL> show errors
No errors.
SQL>

Here are some usage examples, executed within Oracle SQL/Plus

SQL>
SQL>
SQL>
SQL>
SQL> var rc refcursor
SQL> exec procTest(id => 13654, fieldList => 'B,V,H,A,D', rc => :rc);

PL/SQL procedure successfully completed.

SQL> print rc

         A B          D H V
---------- ---------- - - -
         1 one        Y

SQL>
SQL> exec procTest(fieldList => 'B,V,H,A,C', rc => :rc);

PL/SQL procedure successfully completed.

SQL> print rc

         A B          C         H V
---------- ---------- --------- - -
         1 one        31-JUL-16
         2 two        01-AUG-16
         3 three      02-AUG-16
         4 four       03-AUG-16

And now I am going to add a new column, and populate it with some data (so column V will exist in the test table with some data after the DDL/DML, whereas column H will not). The PSM isn’t modified, and the field list/column list of  ‘B,V,H,A,C’ remains the same.

SQL>
SQL> alter table tblTest
  2    add v varchar2(5);

Table altered.

SQL> update tblTest set v = upper(b);

4 rows updated.

SQL>
SQL> exec procTest(fieldList => 'B,V,H,A,C', rc => :rc);

PL/SQL procedure successfully completed.

SQL> print rc

         A B          C         V     H
---------- ---------- --------- ----- -
         1 one        31-JUL-16 ONE
         2 two        01-AUG-16 TWO
         3 three      02-AUG-16 THREE
         4 four       03-AUG-16 FOUR

SQL>

The code doesn’t need explanation – it is quite minimal and concisely speaks for itself. What is does functionally however is provides a mechanism by which table columns that don’t exist in a base table can be returned, where the field list/column list is passed to a stored procedure as CSV.

Even though it’s very simple, I think it is quite neat! My client thought it was quite neat and simple too, especially as it meant no architectural change to their application.

The test code was developed using:

SQL>
SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

Just a brief note to detail why the client had this peculiar requirement. In essence the client front-end application allowed users to search on custom fields associated with a chemical structure. No front-end client validation of the field names was implemented, and the field names were, ultimately, mapped directly to columns in a database table added ad hoc by DBA’s and software developers. The client requirement could be reworded – ‘Return as many fields/columns of data that exist in the database table, but where the columns don’t exist, return a new column containing all NULL values and, specifically, do not error’. I think it is a fair requirement, even though it only became clear after I suggested this approach.

Lastly, and to directly quote Jon Skeet from C# In Depth, ‘Didactic code isn’t production code’. Put another way, the code above works, but is minimally contrived to show my approach to a problem and little else.

— Published by Mike, 10:38:56 30 July 2016