(Oracle/SQL)将所有数据类型合并到一个列中 [英] (Oracle/SQL) Merge all data types into a single column

问题描述

让我解释一下为什么要这样做......我建立了一个Tableau仪表板，该仪表板允许用户浏览/搜索所有表&按架构，对象类型(表，视图，实例化视图)等在仓库中的列.我想添加一列，以从每个表的每个列中提取数据样本-这也可以完成，但是存在此问题. ..:

Let me explain why I want to do this... I have built a Tableau dashboard that allows a user to browse/search all of the tables & columns in our warehouse by schema, object type (table,view,materialized view), etc. I want to add a column that pulls a sample of the data from each column in each table - this is also done, but with this problem...:

结果列由不同类型的数据(varchar2，LONG等)组成.除了LONG之外，我基本上可以使每种类型的数据都符合单个数据类型-它不允许我将其转换为与其他所有内容兼容的任何其他内容(如果有意义的话...).我只需要将所有数据类型共存于一个列中即可.我已经尝试了许多不同的方法，并且已经阅读了大约一个星期的内容，但这听起来似乎无法完成，但是根据我的经验，总有一种方法……我想我会在承认失败之前，先与老师联系.

The resulting column is comprised of data of different types (varchar2, LONG, etc.). I can basically get every type of data to conform to a single data type except for LONG - it will not allow me to convert it to anything else compatible with everything else (if that makes sense...). I simply need all data types to coexist in a single column. I've tried many different things and have been reading up on the subject for about a week now, but it sounds like it just can't be done, but in my experience there is always a way... I figured I'd check with the guru's here before admitting defeat.

我尝试过的一件事:

--Here, from two different tables, I'm pulling a single piece of data from a single column and attempting to merge into a single column called SAMPLE_DATA

--OTHER is LONG data type
--ORGN_NME is VARCHAR2 data type

select 'PLAN','OTHER', cast(substr(OTHER,1,2) as varchar2(4000)) as SAMPLE_DATA from sde.PLAN union all  
select 'BUS_ORGN','ORGN_NME', cast(substr(ORGN_NME,1,2) as varchar2(4000)) as SAMPLE_DATA from sde.BUS_ORGN;

产生的错误:

Lookup Error
ORA-00932: inconsistent datatypes: expected CHAR got LONG

我该如何实现?

预先感谢

推荐答案

大多数应用程序基本上不使用长数据类型.我在想要搜索软件包内容的地方做了类似的事情.解决方案是使用流水线函数将LONG转换为CLOB. Adrian Billington的源代码可以在这里找到: https://github.com/oracle-developer/dla

Long datatypes are basically unusable by most applications. I made something similar where I wanted to search the contents of packages. The solution is to convert the LONG into CLOB using a pipelined function. Adrian Billington's source code can be found here: https://github.com/oracle-developer/dla

您最终得到一个可以查询的视图.即使在查看大型包装时，我也看不到任何性能下降，因此它应该对您有用.

You end up with a view that you can query. I did not see any performance hit even when looking at large packages so it should work for you.

这篇关于(Oracle/SQL)将所有数据类型合并到一个列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！