Cannot Store Chinese Characters In Oracle 9.2 Database

From: <kennymcetech_at_googlemail.com>
Date: 17 Jan 2007 02:26:45 -0800
Message-ID: <1169029605.772800.3800@m58g2000cwm.googlegroups.com>

Hi,

I'm having trouble localizing my Oracle 9.2 / ASP web application for our Chinese-speaking users.

My Oracle 9.2 Database has NLS_NCHAR_CHARACTERSET set to AL16UTF16.

I've set up a test table thus:

CREATE TABLE "TBL_TEST_CH"
(

field1 					NVARCHAR2(40),
field2 					NVARCHAR2(40)

)
/

I have the chinese character set installed on my database / web server
(same box), as well as a test client machine. I can see chinese
characters in my web browser, and can enter them in a test ASP page I've set up. When I execute an insert statement via ADO, the insert statement seems to work, but the result is that the data seems to be stored as upside-down question marks.

I thought perhaps the data was being somehow scrambled between the web app and the database, so I set up an external table import the chinese data from a unicode text file:

CREATE TABLE kenny.ch_import

	(
	FIELD1		NVARCHAR2(255),
	FIELD2		NVARCHAR2(255)
	)
	ORGANIZATION EXTERNAL (TYPE oracle_loader
	DEFAULT DIRECTORY ext_dat_dir
	ACCESS PARAMETERS
	(RECORDS DELIMITED BY ":"
	FIELDS TERMINATED BY "~"
	missing field values are null)
	LOCATION (ext_dat_dir:'test_ch.txt'))
	reject limit unlimited

/

However, when I query the data in the external table using my web application, it comes back with garbage like "ÿþ1" and the like.

I'd really appreciate any suggestions on what I could do next to determine exactly where the problem lies. I've not been able to convince myself that the database is correctly storing the chinese character data, but I appreciate equally that the problem could lie elsewhere.

Thanks in advance,

Kenny McEwan. Received on Wed Jan 17 2007 - 04:26:45 CST