ORA-29275 - partial multibyte character Enterprise Guide

[ArmyMP]

Using SAS EG with an Oracle connection. Whenever I pull the Name field or Address field from Table A, I get ORA-29275 partial multibyte character error. My understanding is that there are characters within these columns that SAS EG may not recognize because the encoding from Oracle does not align with SAS EG.

This happens when applying a simple filter which includes those columns or if I create a join to another table.

I've tried using the TRIM function, KTRIM, formatting the fields as UTF8 to no avail.

I'm just trying to quickly find a workaround as I'm up against a deadline and I'm hitting a roadblock. I've read the forums and can't seem to find a solution. Any assistance is greatly appreciated.

[Sven111]

If you're using explicit pass-through sql or can create views and pull from those, try wrapping the troublesome columns in the Oracle ASCIISTR function.  This will replace any non-ASCII chars with the UTF-16 code unit.  

This error seems odd though.  Both SAS and Oracle are generally pretty good in my experience at handling multiple encodings. Can you pull the data without error if you omit those columns?

[ArmyMP]

---

@Sven111 wrote:

If you're using explicit pass-through sql or can create views and pull from those, try wrapping the troublesome columns in the Oracle ASCIISTR function.  This will replace any non-ASCII chars with the UTF-16 code unit.  

This error seems odd though.  Both SAS and Oracle are generally pretty good in my experience at handling multiple encodings. Can you pull the data without error if you omit those columns?

---

I think I would need to get a DBA involved to do your first suggestion.

If I omit those columns from my output then I do not get an error. Unfortunately the data I need is from those columns.

[Sven111]

You may not have the permissions to create views in the schema you're accessing, but I think if your able to connect using implicit SQL pass-through (which you seem to have), then you should be able to connect via explicit SQL pass-through without needing assistance or modification of the DB.  Try something like this:

LIBNAME OracSAS ORACLE USER=&UserNm PASS=&PassWd PATH='DB_NAME' CONNECTION=GLOBAL SCHEMA=SCHEMA_NAME; 
PROC SQL;
    CONNECT USING OracSAS AS OracDB;

    SELECT *
    FROM   CONNECTION TO OracDB (SELECT ASCIISTR(COLUMN_A) AS COL_A_ASCII
                                 FROM   TABLE_1);
 DISCONNECT FROM OracDB;
;QUIT;