Oracle database type varray not supported

[oktober42]

Hello,

I'm trying to pull data in from an Oracle database and I'm running into errors like "DataSource.Error: Values of column 'COLUMN' have type 'type_number_varray' which is not supported" and "DataSource.Error: Values of column 'COLUMN' have type 'type_string_varray' which is not supported." It looks like a varray is just a collection of known size, and viewing it in TOAD reveals a sequence of comma-delimited data. If I could get that imported in literally any format (nested table, text string) I could work with it. Is there a trick I can do in advanced editor to bring these in?

[Anonymous]

Hi @oktober42 ,

You can try to turn off automatic detection of data types. When you turn off this function, when you import data, all data will only be displayed in text format, you need to manually detect the data type or manually modify the data type.

Data types in Power Query - Power Query | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[oktober42]

Thanks for the reply! Alas, it was a structured source, so this didn't fix my issue.

[lbendlin · Accepted solution]

You will have to convert (or exclude) these columns as part of your custom SQL query.

[oktober42]

After some fighting and a call for help, we arrived at this query for our source:

= Oracle.Database("our.source/table", [HierarchicalNavigation=true, Query="SELECT ITEM1, ITEM2, JSON_OBJECT(NUM_VALUE) FROM THESOURCE"])

Thanks!