Encoding issue: Oracle (Latin-1) → Viya 4 (UTF-8) via LIBNAME

[cristianstoe]

Hi everyone,

I’m connecting an Oracle database (Latin-1 character set) to a SAS Viya 4 (2024.03 LTS) session configured with UTF-8 encoding using the Oracle LIBNAME engine. When displaying data from tables containing accented characters, every non-ASCII byte appears corrupted as “?”.

For example:

• Original: "Não informado"

• Displayed: "N?o informado"

I've found the following encoding-related options for the LIBNAME statement:

• DBCLIENT_ENCODING_FIXED= (YES|NO, based on SAS encoding)

• DBCLIENT_MAX_BYTES= (Maximum number of bytes per character of the SAS session encoding)

• DBSERVER_ENCODING_FIXED= (YES|NO, based on Oracle server encoding)

• DBSERVER_MAX_BYTES= (usually 1)

What would be the recommended combination of these encoding options to correctly handle transcoding from Latin-1 to UTF-8?

Are these options the correct or the only way to force proper transcoding from Latin-1 (Oracle) to UTF-8 (Viya 4) without data corruption?

If not, could you recommend the best practice settings or any additional parameters required?

Any examples or guidance would be greatly appreciated!

Thanks in advance!

[SASKiwi]

I would first suggest you check what your SAS Viya session setting is for this option:

proc options option = dbclient_max_bytes;
run;

Then try increasing it 1 byte at a time. 

There maybe other encoding options worth trying like:

data MyOraTable (encoding = wlatin1);
  set MyOraLIB.MyOraTable;
run;

[Tom]

Going from a single byte encoding like LATIN1 to a multibyte encoding like UTF-8 should not normally cause issues since every LATIN1 character exists in the UTF-8 encoding.  But you might have truncation issues since the number of bytes needed to store some strings in UTF-8 will be more than the same strings would need in LATIN1.   But that would not cause trouble at the beginning of a string like in your example.

So somehow SAS is probably not understanding that the characters from Oracle are in LATIN1 and so then sees the byte string as invalid and hence the ? is displayed.

If you cannot get the connection fixed you might be able to fix the issue your self after getting the data in SAS by calling the KCVT function.

data saslib.mytable;
  set oralib.mytable;
  string = kcvt(myvar,'latin1','utf-8');
run;