在Tableau中使用的动态Oracle表函数

Question

我们在Oracle 11g服务器中有大量数据。大多数工程师使用Tableau来可视化数据，但由于数据库的结构，目前还没有一个很好的解决方案可以直接从Oracle服务器进行可视化。不幸的是，这是无法改变的，因为它与我们的其他系统深度集成在一起。这里有一个“字典”表，我们称它为tab_keys

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

还有一些实际包含数据的表。tab_keys中的每个条目都有一个对应的数据表，其命名方式是在关键字前加上标识符，在本例中，我们将使用"dat_“。因此，AB-7会将其所有数据存储在一个名为dat_19756的表中。这些密钥对用户来说是未知的，并且仅用于跟踪“幕后”。用户只知道AB-7的绰号。

Tableau允许使用标准SQL select语句与Oracle服务器通信，但是因为用户不知道键值，所以他们不能编写SQL语句来查询数据。

Tableau最近为用户添加了查询Oracle表函数的功能，因此我开始编写一个表函数来查询键，并返回一个结果表供Tableau使用。问题在于，每个dat_表基本上都是唯一的，与下一个dat_表具有不同的列数、标签、记录数和数据类型。

处理这个问题的正确方法是什么？我可以：

1)编写一个函数(在常规SQL中哪个tableau可以内联调用)，返回一个动态生成的加密表名称？我试过这个：

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;

然后是SELECT * from TABLE(TEST_FUNC('AB-7'));，但是我得到了：

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

我找不到一种好的方法来将表转换为我需要的表类型。这可以在返回之前在函数中完成吗？

2)写一个表函数？Tableau理应可以像表一样查询这些类型，但后来我遇到了动态生成类型的问题(我知道这并不容易)，但由于需要由多个用户同时使用，因此每个用户在每次连接到表时都需要为他们生成一个数据类型(如果我理解正确的话)。

我不得不认为我错过了一些简单的东西。如何将此查询的返回转换为另一个表的数据类型？

Answer 1

没有一种简单的方法可以让单个泛型函数返回一个可动态配置的嵌套表。对于其他产品，您可以使用引用游标(它映射到ODBC或JDBC ResultSet对象)，但我的理解是Tableau不支持该选项。

您可以做的一件事是从数据字典生成视图。您可以使用此查询生成一次性脚本。

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

双引号是必需的，因为由于破折号，AB-7在Oracle中不是有效的对象名称。

这将允许您的用户像这样查询他们的数据：

select * from "AB-7";

请注意，它们还必须使用双引号。

显然，每当在tab_keys中插入一行时，都需要创建所需的视图。这可以通过触发器来完成。

Answer 2

可以使用开放源码程序Method4在SQL中构建动态SQL。

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

A
-
1

该程序将Oracle Data Cartridge Interface与ANYDATASET相结合，创建了一个能够返回动态类型的函数。

也许有一种方法可以进一步简化界面，但我还没有弄明白。这些Oracle Data Cartridge接口函数非常挑剔，不容易重新打包。

下面是我使用的示例模式：

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;