CODEWITHSUNDEEP
oracle-databaseencodingoracle11gsql-loader
Sadeq Aramideh
Sadeq Aramideh

Reputation: 53

Gibberish filed values when Loading data with Oracle SQL Loader (sqlldr)

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]

Upvotes: 0

Views: 589

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

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.

Upvotes: 1

Related Questions