Home » SQL & PL/SQL » SQL & PL/SQL » Chinese Characters not display in table (Oracle 12C UNIX)
Chinese Characters not display in table [message #652028] Tue, 31 May 2016 08:00 Go to next message
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.

Re: Chinese Characters not display in table [message #652029 is a reply to message #652028] Tue, 31 May 2016 08:04 Go to previous messageGo to next message
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?
Re: Chinese Characters not display in table [message #652030 is a reply to message #652029] Tue, 31 May 2016 08:20 Go to previous messageGo to next message
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)
Re: Chinese Characters not display in table [message #652031 is a reply to message #652029] Tue, 31 May 2016 08:21 Go to previous messageGo to next message
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
Re: Chinese Characters not display in table [message #652033 is a reply to message #652031] Tue, 31 May 2016 08:28 Go to previous messageGo to next message
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?

Re: Chinese Characters not display in table [message #652035 is a reply to message #652033] Tue, 31 May 2016 08:36 Go to previous messageGo to next message
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.
Re: Chinese Characters not display in table [message #652036 is a reply to message #652035] Tue, 31 May 2016 08:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post COPY & PASTE proof that "the characters was good" in flat file.
Re: Chinese Characters not display in table [message #652041 is a reply to message #652038] Tue, 31 May 2016 09:12 Go to previous messageGo to next message
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 436 times)
Re: Chinese Characters not display in table [message #652042 is a reply to message #652041] Tue, 31 May 2016 09:13 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Attaching another file for your reference.


thanks.

pstanand.
Re: Chinese Characters not display in table [message #652043 is a reply to message #652028] Tue, 31 May 2016 09:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Chinese Characters not display in table [message #652045 is a reply to message #652043] Tue, 31 May 2016 09:24 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
here is the snapshot of nls_database_parameters details.

thanks.
pstanand.
Re: Chinese Characters not display in table [message #652046 is a reply to message #652045] Tue, 31 May 2016 09:25 Go to previous messageGo to next message
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.
Re: Chinese Characters not display in table [message #652047 is a reply to message #652044] Tue, 31 May 2016 09:25 Go to previous messageGo to next message
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.
Re: Chinese Characters not display in table [message #652048 is a reply to message #652047] Tue, 31 May 2016 09:27 Go to previous messageGo to next message
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
Re: Chinese Characters not display in table [message #652049 is a reply to message #652043] Tue, 31 May 2016 09:31 Go to previous messageGo to next message
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
Re: Chinese Characters not display in table [message #652050 is a reply to message #652047] Tue, 31 May 2016 09:33 Go to previous messageGo to next message
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.
Re: Chinese Characters not display in table [message #652051 is a reply to message #652050] Tue, 31 May 2016 09:38 Go to previous messageGo to next message
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?

Re: Chinese Characters not display in table [message #652072 is a reply to message #652051] Tue, 31 May 2016 22:14 Go to previous message
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
Previous Topic: Filler / Bound Filler in External tables
Next Topic: E-Mail sending issue from Oracle Database
Goto Forum:
  


Current Time: Sun Aug 01 19:05:17 CDT 2021