Forum Discussion

MiquelDespuig's avatar
MiquelDespuig
Frequent Visitor
5 years ago
Solved

Error Oracle ORA-12704 character set mismatch when refreshing dataset

Hello Community!   Since I've installed December 2020 updtade of the On-premise data gateway; a previously working dataset, which was connecting to an Oracle Datasource now throws an error related ...
Need Help
  • MiquelDespuig's avatar
    MiquelDespuig
    4 years ago

    After tinkering a lot with this problem. I've figured out how to avoid the problem and it works flawlessly although I don't really understand why.

    The merge step generated by the power query editor is like so:

    Table.CombineColumns(#"Removed Other Columns",{"IMDSC1", "IMDSC2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")

    You get always this error:

    QuoteStyle.None generated

     

    The QuoteStyle.None function inside the Combiner.CombineTextByDelimiter makes the whole thing fail. If you remove this piece of code like so:

    Table.CombineColumns(#"Removed Other Columns",{"IMDSC1", "IMDSC2"},Combiner.CombineTextByDelimiter(""),"Merged")

    Magic happens:

    QuoteStyle Removed

     You get your data back as you would expect.

    The reason why this is the problem escapes my understanding. On top of that the documentation on these functions is less than ideal. If anyone knows why this happens let me know! I'm now curious ğŸ˜‹

MiquelDespuig's avatar
MiquelDespuig
Frequent Visitor
4 years ago

Still not working!

After near a year of dealing with the error I finally have some time to focus on trying to solve it. But with no success ğŸ˜¥. I'll try to add as much information as I can, and if someone has solved the same kind of error in another DBMS system may work as well.

Setup

Current running software versions at the time of writing:

  • Server Side
  • Client Side
    • Windows 10 Pro, Version 20H2, Build 19042.1165
    • Power BI Desktop 2.98.1025.0 64-bit (October 2021)
    • Oracle Data Access Components for Oracle 12.2.0.1.0

The Error

Originally, I found the error with the data gateway but, the error can be reproduced in Power BI Desktop as well. The error happens when:

  • Trying to merge two text columns with NCHAR (30) types without manually specifying its type first.

 

  • The error always occurs on the native Oracle database connector. If you setup a connection with a DSN (Data Source Name) for an ODBC connection and repeat the same steps you're able to repeat the exact same steps without getting the error (loosing query folding ğŸ˜®). Here's the code of the m query, and some screenshots to understand how the PBIX file is organized:

The code for both queries is the same except for the source definition.

let
    Source = #"PRODDTA ODBC",
    Table = Source{[ Name = "F4101" ]}[Data],
    #"Removed Other Columns" = Table.SelectColumns ( Table, { "IMDSC1", "IMDSC2" } ),
    #"Merged Columns" = Table.CombineColumns (
        #"Removed Other Columns",
        { "IMDSC1", "IMDSC2" },
        Combiner.CombineTextByDelimiter ( "", QuoteStyle.None ),
        "IMDSC"
    )
in
    #"Merged Columns"
Power Query Formatter
 
Any clue why this might be happening? Thank again for the help!