This content has been marked as final. Show 11 replies
It is because your Database Character-Set doesn't support this symbol.
Most probably your Character-Set is UTF8 or US7ASCII.
This pound symbol is supported by WE8MSWIN1252
Please check this and reply...
and check for these parameter's values -
SELECT * FROM nls_database_parameters;
For me, the values are -
NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_CHARACTERSET UTF8
Edited by: ranit B on Dec 5, 2012 5:24 PM
NLS_CHARACTERSET AL32UTF8Could you please try altering this to WE8MSWIN1252?
Any pointers here?
This might work.
try changing the column where this character is stored to NVARCHAR2
Refer -- http://dba.stackexchange.com/questions/20016/euro-sign-symbol-not-showing-up-on-oracle-10gr2-and-aix-6-1
As of now, let's keep the NLS_CURRENCY as '#'.
Refer -- http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams136.htm
Hope this was helpful,
Edited by: ranit B on Dec 5, 2012 5:57 PM
Your advise is wrong. Please, do not advise serious database changes or application redesign without first confirming the cause of the problem.
AL32UTF8 is perfectly capable of supporting the pound symbol '£' as well as most other characters in everyday use in VARCHAR2 columns. Do not change anything in the database yet.
The problem is, most probably, in the loading script. By default, the file to be loaded will be interpreted in US7ASCII, which is not capable of supporting the pound symbol, indeed. A bit confusing symptom is that the result of the loading process is the reversed question mark -- this is however possible if you look with an WE8ISO8859P1/WE8MSWIN1252 client at the loaded data. When looking with SQL Developer, you should see an "empty box" character.
You need to set the environment variable NLS_LANG to ENGLISH_UNITED KINDGDOM.WE8MSWIN1252 before sqlldr is started or add the CHARACTERSET WE8MSWIN1252 clause to the sqlldr control file. The second option is valid only if the data is not in the control file itself. This advice assumes that the pound symbol in the file is encoded in WE8ISO8859P1 or its superset WE8MSWIN1252. It can also be encoded in AL32UTF8, in which case NLS_LANG and/or control file should use AL32UTF8 in place of WE8MSWIN1252. You can verify the encoding by opening the input file in a hex editor and checking the hex code of the pound symbol. If it is one byte 0xA3, use WE8MSWIN1252. If it is two bytes 0xC2 0xA3, use AL32UTF8.
By the way, the NLS_CURRENCY value has no meaning for the encoding of the currency symbol. It is relevant for the TO_CHAR/TO_NUMBER conversion (explicit and implicit). Even then, NLS_DATABASE_PARAMETERS value is used for CHECK constraints and virtual columns. For queries and DML, NLS_SESSION_PARAMETERS matters.
Thanks for your reply.
The control file dont have any data,if i want add this to control file, how to add this?
Do you have any example?
The second is we are calling sql loader shell script through ODI Procedure, so in case if want to set NLS_LANG to ENGLISH_UNITED KINDGDOM.WE8MSWIN1252 where we need to set this? Is it by using alter session set NLS_LANG=ENGLISH_UNITED KINDGDOM.WE8MSWIN1252?or can we set this though same shell script that call sql loader?Any example?
It will be really helpful if you can give some info on the above things.
The syntax of the SQL*Loader control file is documented here:
The NLS_LANG variable can be set in the script
LOAD DATA CHARACTERSET WE8MSWIN1252 INFILE 'sample.dat' BADFILE 'sample.bad' DISCARDFILE 'sample.dsc' APPEND INTO TABLE emp . . .
#!/bin/sh . . . NLS_LANG="ENGLISH_UNITED KINGDOM.WE8MSWIN1252"; export NLS_LANG sqlldr ... . . .
#!/bin/csh . . . setenv NLS_LANG "ENGLISH_UNITED KINGDOM.WE8MSWIN1252" sqlldr ... . . .