0

Using

sqlldr

to load data in an oracle table doesn't result in any errors but the value of the fields are gibberish. not all the values of course, only the values which was in persian(arabic) format. already read many questions here but could not resolve the issue, most relatable topic to this problem were :[Unreadable character in generated sqlplus file although the values in my case isn't question mark, but something like ,ÑÇå Âåä or äÇ ãÔÎÕ

Also played with the NLS_LANG environment variable but it was to no avail.

Created different oracle databases with different character set's, this also was to no avail.

I'm new to oracle, so it is highly likely that I'm making a rookie mistake while creating database and setting character set or something else, to be honest i have no idea. but tried many responses from users and here i am.

I Uploaded the table schema and Ctl extension File to replicate the problem, the link to the related files are resided in this link: [https://www.dropbox.com/sh/ejxvast0ruioksk/AABXhjujqzhRpuMVjl7V-zxUa?dl=0][1]

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Sadeq Aramideh
  • 43
  • 1
  • 2
  • 7

1 Answers1

1

In total you have three character sets or encodings.

  1. What is the encoding of your file? Check the save options of the editor or the application which created the file.
  2. The character set of your command line window cmd.exe, called "codepage". You can interrogate (or change) with command chcp
  3. The character set of your database.

1) and 2) must be the same. Use command chcp to set them equal (or change settings in your editor)

3) can be different but the character set must support persian/arabic characters, so most likely AL32UTF8 which is the default nowadays.

Use the NLS_LANG value to tell the database which character set is used for 1) and 2), example

C:\>chcp 1256
Aktive Codepage: 1256.

C:\>set NLS_LANG=.AR8MSWIN1256

C:\>sqlldr ...

You can get a list of codepages vs. Oracle character set with this query:

SELECT VALUE AS ORACLE_CHARSET, UTL_I18N.MAP_CHARSET(VALUE) AS IANA_NAME
FROM V$NLS_VALID_VALUES
WHERE PARAMETER = 'CHARACTERSET';

And here a list of Code Page Identifiers

See also OdbcConnection returning Chinese Characters as "?" to get more details.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Hello , I created two database one is NLS_CHARACTERSET AR8ISO8859P6 NLS_NCHAR_CHARACTERSET AL16UTF16 , the other one is NLS_CHARACTERSET WE8MSWIN1252 NLS_NCHAR_CHARACTERSET AL16UTF16 also is set the chcp to 1256 and 1252 but it didnt work either. any suggesion? – Sadeq Aramideh Oct 22 '18 at 05:48
  • Strangely i tried on another pc and this time it worked! – Sadeq Aramideh Oct 22 '18 at 12:12