2

Please find below my function:

CREATE OR REPLACE FUNCTION TESTER_TESTING (    P_STRING         IN VARCHAR2) 
RETURN NUMBER IS
BEGIN
Dbms_Output.Put_Line('P_STRING:'||P_STRING);
return 1;
END TESTER_TESTING;
/

When i execute from sqlplus using:

DECLARE 
  RetVal NUMBER;
  P_STRING VARCHAR2(32767);

BEGIN 
  P_STRING := '006µ-540';
  RetVal := VISION.TESTER_TESTING ( P_STRING );
  DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));
  DBMS_OUTPUT.Put_Line('');
COMMIT; 
END; 
/

Output is: P_STRING:006??-540 RetVal = 1

Output

Please advise on why the special character is not being passed & how to fix it.

-Oracle Version: 11g
-Linux: RHEL 7.2
-Script executed using sqlplus.
pOrinG
  • 896
  • 3
  • 13
  • 27

2 Answers2

2

SQL*Plus inherits character encoding from command-line. You can interrogate by chcp.

Before you start sqlplus you must set NLS_LANG parameter accordingly, e.g. 

C:\>chcp
Active code page: 850

C:\>SET NLS_LANG=.WE8PC850    
C:\>sqlplus ...

Of course it also works the other way around, e.g.

C:\>echo %NLS_LANG%
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

C:\>chcp 1252
Active code page: 1252

C:\>sqlplus ...

NLS_LANG can be also set in Registry, see HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 64 bit), resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32 bit Oracle at 64 bit Windows).

Have a look at this post for further details: OdbcConnection returning Chinese Characters as "?"

I just see, you use Linux, not Windows. Use locale charmap or echo $LANG instead of chcp to get character set of your terminal.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Hi, what you have explained works well with windows however I am still facing the same issue in Linux. $LANG = en_US.UTF-8 and our Oracle DB has AMERICAN_AMERICA.AL32UTF8. Please advise. Thanks for your time. – pOrinG May 31 '17 at 10:30
  • Maybe your terminal program does not support it. Check settings there. – Wernfried Domscheit May 31 '17 at 10:40
  • For terminal program we are using PuTTY in which we have mentioned UTF-8 as Remote character set. – pOrinG May 31 '17 at 10:45
  • Also when using sqlldr we are using the following syntax : "load data CHARACTERSET UTF8 infile..... " which correctly identifies and loads data properly from the same terminal program & linux server. – pOrinG May 31 '17 at 10:53
  • Did you try `export NLS_LANG=AMERICAN_AMERICA.AL32UTF8` ? – Wernfried Domscheit May 31 '17 at 10:55
  • 1
    For reference: [link](http://docs.oracle.com/cd/E12102_01/books/AnyInstAdm784/AnyInstAdmPreInstall18.html) – pOrinG May 31 '17 at 11:03
0

Oracle is returning ASCII CODE of µ as 49845 (In my case) . However ASCII code of µ is 230 as per mentioned in ASCII chart available on internet. So definately 49845 ascii code coming from database to client machine when gets converted to some "I dont know " figure.

Himanshu
  • 91
  • 7