Forum Discussion
Binary data Oracle data type
Hi ger,
Which kind of Binary data type did you store on Oracle database? What do you mean " Can see the columns while in query view. When the query is run the columns disappear from the report view and cannot be used. This happens in both import and direct query mode."? Can you share some screenshots to clarify the issue?
By the way, make sure you are using the latest desktop version 2.43.4647.541.
Best Regards,
Qiuyun Yu
Hi,
Desktop is the latest version 2.43.4647.541 64-bit (February, 2017)
The database is the backend of an off-the-shelf product.
This is the Query Editor view of one of the tables. The Binary data type is highlighted.
It cannot be expanded using the down arrows, as would be seen with a file.
To simply the example, two cols are chosen.
Close and apply the query. Look at the Feilds list in Report view, CATEGORY (alpha numeric type) is there, AFFECTED_RC is not.
This is direcy query mode. Its the same behaviour in import mode, which can't be used anyway because of volume of data.
The data in the fields, and there are many like this. The content is not an image or other binary type, is more like a alphanumneric string.
Does this help clarify?
You can apply a transformation for the Binary column as Text.FromBinary.
= Table.AddColumn(YourTable, "Text_CATEGORY", each Text.FromBinary([CATEGORY]))
Unfortunately no, it gives a warning about adding a step that results in a query that is not supported
in DriectQuery mode.
Am trying something like this -
let
Source = Oracle.Database("odsprd", [HierarchicalNavigation=true]),
DTMRT_ADS = Source{[Schema="DTMRT_ADS"]}[Data],
TRILL_CALL_REQ1 = DTMRT_ADS{[Name="TRILL_CALL_REQ"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(TRILL_CALL_REQ1,{"AFFECTED_RC", "CATEGORY", "ID", "ZCR_LOCATION"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "AFFECTED_RC", "AFFECTED_RC - Copy"),
#"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each Text.FromBinary([AFFECTED_RC]))
in
#"Added Custom"Interestingly, if the offending column is simply copied and no manual transform applied, it seems to work. I suspect the problem centres on trying to find the data type from the binary.
This does not feel like a solution, maybe I got lucky with the one piece of data where this will work.
What if you do Table.TransformColumnTypes and choose a different type then binary?
#"Added Custom" = Table.TransformColumnTypes( #"Duplicated Column",{ "AFFECTED_RC",type text})
