Dynamic Oracle Table Function for use in Tableau

Question

We have a large amount of data in an Oracle 11g server. Most of the engineers use Tableau for visualizing data, but there is currently not a great solution for visualizing straight from the Oracle server because of the structure of the database. Unfortunately, this cannot be changed, as it's very deeply integrated with the rest of our systems. There is a "dictionary" table, let's call it tab_keys:

name  |   key
---------------
AB-7  |  19756
BG-0  |  76519
FY-10 |  79513
JB-2  |  18765
...
...

And there are also the tables actually containing the data. Each entry in tab_keys has a corresponding data table named by prefixing the key with an identifier, in this case, we'll use "dat_". So AB-7 will store all its data in a table called dat_19756. These keys are not known to the user, and are only used for tracking "behind the scenes". The user only knows the AB-7 moniker.

Tableau allows communication with Oracle servers using standard SQL select statements, but because the user doesn't know the key value, they cannot write a SQL statement to query the data.

Tableau recently added the ability for users to query Oracle Table Functions, so I started going down the road of writing a table function to query for the key, and return a table of the results for Tableau to use. The problem is that each dat_ table is basically unique with a different numbers of columns, labels, number of records, and datatypes from the next dat_ table.

What is the right way to handle this problem? Can I:

1) Write a function (which tableau can call inline in regular SQL) to return a bonified table name which is dynamically generated? I tried this:

create or replace FUNCTION TEST_FUNC 
(
  V_NAME IN VARCHAR2
) RETURN user_tables.table_name%type AS 
V_KEY VARCHAR(100);
V_TABLE user_tables.table_name%type;
BEGIN
  select KEY into V_KEY from my_schema.tab_keys where NAME = V_NAME;
  V_TABLE := dbms_assert.sql_object_name('my_schema.dat_' || V_KEY);
  RETURN V_TABLE;
END TEST_FUNC;

and then SELECT * from TABLE(TEST_FUNC('AB-7')); but I get:

ORA-22905: cannot access rows from a non-nested table item
22905. 00000 -  "cannot access rows from a non-nested table item"
*Cause:    attempt to access rows of an item whose type is not known at
           parse time or that is not of a nested table type
*Action:   use CAST to cast the item to a nested table type

I couldn't figure out a good way to CAST the table as the table type I needed. Could this be done in the function before returning?

2) Write a table function? Tableau can supposedly query these like tables, but then I run into the problem of dynamically generating types (which I understand isn't easy) but with the added complication of this needing to be used by multiple users simultaneously, so each user would need a data type generated for them each time they connect to a table (if I'm understanding this correctly).

I have to think I'm missing something simple. How do I cast the return of this query as this other table's datatype?

Answer 1

There is no simple way to have a single generic function return a dynamically configurable nested table. With other products you could use a Ref Cursor (which maps to ODBC or JDBC ResultSet object) but my understanding is Tableau does not support that option.

One thing you can do is generate views from your data dictionary. You can use this query to produce a one-off script.

select 'create or replace view "' || name || '" as select * from dat_' || key || ';'
from tab_keys;  

The double-quotes are necessary because AB-7 is not a valid object name in Oracle, due to the dash.

This would allow your users to query their data like this:

select * from "AB-7";

Note they would have to use the double-quotes too.

Obviously, any time you inserted a row in tab_keys you'd need to create the required view. That could be done through a trigger.

Answer 2

You can build dynamic SQL in SQL using the open source program Method4:

select * from table(method4.dynamic_query(
    q'[
        select 'select *  from dat_'||key
        from tab_keys
        where name = 'AB-7'
    ]'
));

A
-
1

The program combines Oracle Data Cartridge Interface with ANYDATASET to create a function that can return dynamic types.

There might be a way to further simplify the interface but I haven't figured it out yet. These Oracle Data Cartridge Interface functions are very picky and are not easy to repackage.

Here's the sample schema I used:

create table tab_keys(name varchar2(100), key varchar2(100));
insert into tab_keys
select 'AB-7' , '19756' from dual union all
select 'BG-0' , '76519' from dual union all
select 'FY-10', '79513' from dual union all
select 'JB-2' , '18765' from dual;

create table dat_19756 as select 1 a from dual;
create table dat_76519 as select 2 b from dual;
create table dat_79513 as select 3 c from dual;
create table dat_18765 as select 4 d from dual;