OraFAQ Forum: SQL & PL/SQL » Chinese Characters not display in table

Home » SQL & PL/SQL » SQL & PL/SQL » Chinese Characters not display in table (Oracle 12C UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Chinese Characters not display in table [message #652028]

Tue, 31 May 2016 08:00

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I'm involving in a data migration project.
I have table sap_description in which short_desc nvarchar2(500) and long_desc NCLOB columns.
Some of the rows for this columns having chinese characters.

I used SQL server import export wizard to export the data in a flat file.
When I opened the file in textpad, these characters are changed to ?????.
I set up the textpad Configure -> Preferences ->Document Classes ->Text -> Font->Arial Unicode MS ->Script ->CHINESE_BIG5.
Even after these settings my file doesn't show the correct chinese characters.

This file when I read through external tables loaded values as ????? instead chinese characters.
So I checked my NLS_Parameters settings is there anything to be modified there to view the correct characters.
Attached is my NLS_Parameters snapshot.
I use SQL developer tool where also I did Tools->Preferences->Environment ->Encoding ->Cp1252.
But nothing is working for me.

I request your help to load my data properly for this table.

Appreciate your help in this regard.

thanks.

pstanand.

Attachment: NLS_Parameters.PNG
(Size: 21.32KB, Downloaded 788 times)

Report message to a moderator

Re: Chinese Characters not display in table [message #652029 is a reply to message #652028]

Tue, 31 May 2016 08:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I used SQL server import export wizard to export the data in a flat file.
if flat file contains incorrect characters, then the database will have incorrect characters.
is SQL Server running on the same system as Oracle database server? if not, how was the file transported from MS system to Oracle system? FTP in BINARY mode?

Report message to a moderator

Re: Chinese Characters not display in table [message #652030 is a reply to message #652029]

Tue, 31 May 2016 08:20

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If the NLS charset that the database or workstation uses isn't a character set that can support the Chinese characters then oracle replaces the characters with a question mark when it displays it (problem with workstation settings) or when it stores it (database settings)

Report message to a moderator

Re: Chinese Characters not display in table [message #652031 is a reply to message #652029]

Tue, 31 May 2016 08:21

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
when generating the flat file the characters was good.
But when opening it through textpad it got changed.

The flat file was transferred to Oracle system using the Bitvise SSFT tool through.

The external table reads all the records. But when viewing through SQL Developer tool the characters are not displayed in chinese.

Kindly let me for any additional info to resolve this issue.

Thanks.

pstanand

Report message to a moderator

Re: Chinese Characters not display in table [message #652033 is a reply to message #652031]

Tue, 31 May 2016 08:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

pstanand wrote on Tue, 31 May 2016 06:21

Hi,
when generating the flat file the characters was good.
But when opening it through textpad it got changed.

post COPY & PASTE proof that "the characters was good" in flat file.

Why open the file with textpad?

Report message to a moderator

Re: Chinese Characters not display in table [message #652035 is a reply to message #652033]

Tue, 31 May 2016 08:36

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

When I read the file through external table some of the records are pushed out in to the bad file.
so to avoid I opened the flat file in textpad and saved it again as File_format as UNIX and Encoding as UTF_8.
Then this file is read by the external table. When selecting the external table the records are displayed not correctly.

Am'I missing anything here. Please guide me.

Report message to a moderator

Re: Chinese Characters not display in table [message #652036 is a reply to message #652035]

Tue, 31 May 2016 08:37

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The only thing that that means is that you do not have your external table setup correctly yet. Get it working. The log will show you why it fails. Fix the problems.

[Updated on: Tue, 31 May 2016 08:37]

Report message to a moderator

Re: Chinese Characters not display in table [message #652038 is a reply to message #652036]

Tue, 31 May 2016 08:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post COPY & PASTE proof that "the characters was good" in flat file.

Report message to a moderator

Re: Chinese Characters not display in table [message #652041 is a reply to message #652038]

Tue, 31 May 2016 09:12

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
Attached my flat file and the textpad error.
the last line in the flat file is converted to ??????? when I opened it through textpad by ignoring the error.

Please have a look and suggest me an idea.

Thanks.

Attachment: FlatFile.PNG
(Size: 9.70KB, Downloaded 989 times)

Report message to a moderator

Re: Chinese Characters not display in table [message #652042 is a reply to message #652041]

Tue, 31 May 2016 09:13

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Attaching another file for your reference.

thanks.

pstanand.

Attachment: TextPad Error.PNG
(Size: 9.25KB, Downloaded 980 times)

Report message to a moderator

Re: Chinese Characters not display in table [message #652043 is a reply to message #652028]

Tue, 31 May 2016 09:18

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Run the following command and paste the results into this issue.

SELECT * FROM nls_database_parameters;

I forgot to also mention

select * from nls_session_parameters;

to see what your session parameters are set to.

[Updated on: Tue, 31 May 2016 09:24]

Report message to a moderator

Re: Chinese Characters not display in table [message #652044 is a reply to message #652042]

Tue, 31 May 2016 09:19

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

And this way I used to generate the flat file from SQL Server import export wizard.

thanks

pstanand.

Attachment: Sql server export.pdf
(Size: 212.29KB, Downloaded 1570 times)

Report message to a moderator

Re: Chinese Characters not display in table [message #652045 is a reply to message #652043]

Tue, 31 May 2016 09:24

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
here is the snapshot of nls_database_parameters details.

thanks.
pstanand.

Attachment: nls_database_parameters.PNG
(Size: 22.23KB, Downloaded 949 times)

Report message to a moderator

Re: Chinese Characters not display in table [message #652046 is a reply to message #652045]

Tue, 31 May 2016 09:25

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Please do not make images. Just run the command in sqlplus and copy and paste into this issue.

Report message to a moderator

Re: Chinese Characters not display in table [message #652047 is a reply to message #652044]

Tue, 31 May 2016 09:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Unless & until you can successfully get flat file to Unix with correct characters, you will NEVER get good data into Oracle.
You can use "od" on Unix to inspect raw content of flat file.

Root cause & solution are 100% external to Oracle.
It is possible to CREATE DATABASE LINK to access SQL Server tables directly from Oracle DB.

Report message to a moderator

Re: Chinese Characters not display in table [message #652048 is a reply to message #652047]

Tue, 31 May 2016 09:27

Bill B
Messages: 1971
Registered: December 2004

Senior Member

And if you don't/can't make a database link you can easily make a linked server link from sql server into the oracle database

Report message to a moderator

Re: Chinese Characters not display in table [message #652049 is a reply to message #652043]

Tue, 31 May 2016 09:31

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Here is the nls_session_parameters

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Report message to a moderator

Re: Chinese Characters not display in table [message #652050 is a reply to message #652047]

Tue, 31 May 2016 09:33

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
could you please guide how to create db link for SQL server.
I have oradba privileges. Is that privilege is enough or should I need any more privilege?

Kindly help.

Report message to a moderator

Re: Chinese Characters not display in table [message #652051 is a reply to message #652050]

Tue, 31 May 2016 09:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

pstanand wrote on Tue, 31 May 2016 07:33

Hi,
could you please guide how to create db link for SQL server.
I have oradba privileges. Is that privilege is enough or should I need any more privilege?

Kindly help.

is GOOGLE broken for you?

Report message to a moderator

Re: Chinese Characters not display in table [message #652072 is a reply to message #652051]

Tue, 31 May 2016 22:14

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi
is there any other way other than the db link and sql linked server?
Because the problem is when the flat file is opened through textpad it changes the chinese characters.
To create sql linked server I don't have the privilege of sysadmin role and that will not available.
So please help me to see the chinese characters in textpad and hope that will solve my problem.

thanks

Report message to a moderator

Previous Topic:	Filler / Bound Filler in External tables
Next Topic:	E-Mail sending issue from Oracle Database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 14 17:53:29 CDT 2024