Storing/Retrieving Chinese/Unicode in PL/SQL

Question

We are on Oracle9i Enterprise Edition. I tried the following in PL/SQL. MANUFACTUERER Field is defined as VARCHAR2(75). I tried similar table with NVARCHAR2(75) instead, and same result.

INSERT INTO TLP.part_extensions_tab(MANUFACTURER, MANUFACTURER_ADDRESS_LINE ) 
SELECT 'Neal Chinese Test 06', '统一通信系统）【功能性备注：集团电话' FROM DUAL;
commit;
select MANUFACTURER, MANUFACTURER_ADDRESS_LINE from TLP.part_extensions_tab 
where MANUFACTURER like 'Neal Chinese Test 06' ;

The SQL runs, but returns the following question marks; so I'm not sure the insert worked, or if I have a problem displaying the text back in Chinese.

My other Googling lead me down this path (not sure if it is a dead end or not):

DECLARE 
   MyLanguage nvarchar2(30);

BEGIN 
  --EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''SPANISH''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''SIMPLIFIED CHINESE_CHINA.ZHS16GBK''';

  select Value into MyLanguage from nls_database_parameters where Parameter = 'NLS_LANGUAGE'; 
  DBMS_OUTPUT.put_line('MyLanguage=' || MyLanguage);  
  --DBMS_OUTPUT.put_line('NLS_LANG=' || NLS_LANG);  
END;

--SELECT USERENV ('language') FROM DUAL;

But this gives me "ORA-12705; invalid or unknown NLS parameter value specified. I don't know if that language needs to be installed on my client, my server, or my syntax is just wrong. When I tried Spanish, the SQL ran without error, and it shows Spanish.American in the result. emphasized text

Answer 1

You can not store Chinese characters in a Western European characterset (WE8ISO8859P1).

Define the MANUFACTURER_ADDRESS_LINE column as NVARCHAR2 to use AL16UTF16 and insert the string as below (notice the N modifier):

INSERT INTO tc(MANUFACTURER, MANUFACTURER_ADDRESS_LINE ) 
SELECT 'Neal Chinese Test 06', N'统一通信系统）【功能性备注：集团电话' FROM DUAL;
commit;

Answer 2

For PL/SQL Developer: change the code editor font to 'Segoe UI Symbol' or 'Segoe MDL2 Assets'. Other preferences (regedit, NLS* params) should not be changed.