CODEWITHSUNDEEP
oracle-databaseplsql
prashant1988
prashant1988

Reputation: 282

Character encoding issue in Oracle PL/SQL

I'm facing a character discrepancy issue while extracting data from db tables.

I've written a PL/SQL code to spool some data to .txt file from my db tables and running this sql using unix shell but when I'm getting the spooled file, the result set is a changed one from the one at back end.

For example: 

At back end: SADETTÝN

In Spooled txt file :  SADETTŸN

If you look at the Y character, it is a changed one. I want to preserve all the characters the way they are at back end.

My db's character set:

SELECT * FROM v$nls_parameters WHERE parameter LIKE 'NLS%CHARACTERSET'
PARAMETER              VALUE 
NLS_CHARACTERSET       WE8ISO8859P1 
NLS_NCHAR_CHARACTERSET WE8ISO8859P1

And Unix NLS_LANG parameter :

$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

I tried changing NLS_LANG parameter to WE8ISO8859P9(Trukish characterset) but no help!

Could anyone let me know the solution to this problem?

Upvotes: 2

Views: 16998

Answers (2)

BulentB
BulentB

Reputation: 316

I presume that you are trying to visualize your file with "vi" or something similar.NLS_LANG parameter is used only by your database to export to your file.For your editor(vi), you need to set the LANG parameter to the corresponding value to your NLS_LANG. Exemple : For ISO8859P1 american english you have to do export LANG=en_US.ISO8859-1 In other words your file is just fine it's your editor who doesn't know what to do with your Turkish characters.

Upvotes: 3

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

You should use NCHAR data types. More information is available at Oracle Documentation - SQL and PL/SQL Programming with Unicode

For spooling from SQL*Plus, you need to set the NLS_LANG environment variable correctly. Here is a similar question in stackoverflow.

Upvotes: 2

Related Questions