CODEWITHSUNDEEP
sqloracle-databasetypesmerge
Drivium
Drivium

Reputation: 537

(Oracle/SQL) Merge all data types into a single column

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...:

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.

One of the things I've tried:

--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;

Resulting error:

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

How can I achieve this?

Thanks in advance

Upvotes: 2

Views: 645

Answers (1)

kevinskio
kevinskio

Reputation: 4551

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.

Upvotes: 3

Related Questions