Extra trailing char when retreiving varchar2 from Oracle 11g with ASP.Net core 2 with OracleClient

Question

I'm currently facing a annoying problem and would gladly receive some insight.

I'm developping a simple app that retreives some records from an oracle 11g db and display them.

I'm using ASP.Net core 2 and the System.data.OracleClient package to perform a connection to the DB.

However, when i do a simple select and retreive varchar2 field like the following snippet:

// Init connection
var connection = new OracleConnection(connectionString);

// Create query command
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "select * from mytable";

// Execute query
var reader = cmd.ExecuteReader();

// Go through all retreived raw and display only the string fields
while (reader.Read()) {
    object[] rawRow = new object[reader.FieldCount];
    foreach(var o in rawRow) {
        if( o is string ){
            // Display the string
        }
    }
}

I retreive the string with an extra trailing garbage char like: "MYVALUE翽" The 翽 is really an extra char, it is not present in the field stored in the db. When i use sql+ client i dont get this char.

If first suspected a charset problem, but as the char i an extra one and the value field only contains ascii char, it should not be...

Ringing anyone bell?

DBM Oracle 11g

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_CHARACTERSET WE8MSWIN1252

Thanks in advance

Edit:

Client: Oracle 12C conf in path and reg ( either 32 or 64 bits ):

NLS_LANG FRENCH_FRANCE.WE8MSWIN1252

Edit 2:

The extra character can change, got ꌽ, ⌴ and some other. It really looks like a charset conversion issue, but at the connector level :(.

Edit 3:

I just tested with the unofficial mono based oracle client ( Mono.Data.OracleClientCore ) and i get the same results. Now i doubt it might be the connector. Maybe OCI based clients do not read path or registry conf...

Answer 1

The varchar2 field in Oracle stores data as bytes. So in order to effectively extract each values you can use the following data types:

1 - byte

2 - char

3 - char[]

4 - int

You can see this oracle document of how each oracle Data Type is mapped to .NET