Handling special characters

[kris007]

Does it give out any error or warning messages? I was able to successfully insert it into Oracle in the past.

[Krazykoolrohit]

try changing "contains terminator" to yes.

this can be done bi right clicking each column and choosing yes from "contains terminator" box

do this for all varchar columns and check if it makes a difference.

If this does not work try and identify the character which is causing the error. Presense of '?' should not disrupt the flow.

[narasimha]

Ok..
Do you want to store such special characters in the database?
What is your database character set?
Is it capable of storing such special characters?
We had situations where we wanted to store some non printable characters in the database so we changed our
oracle database character set from WE8ISO8859P1 to WE8MSWIN1252.

HTH

[DeepakCorning]

I do not think so there should be a problem while inserting special characters in oracle DB but the job mau show warnings becuase of the NLS settings of that particuar job.

I think changing the NLS setting for that particular job will yield positive results.

[vsi]

narasimha,
Where i can check for database characterset?

Thanks
vsi

[Krazykoolrohit]

you cannot ignore termination for the last column.

is the problem in last row? is this actually a '?' or any other character? if it is un necessary can you strip your records of it?

[vsi]

DeepakCorning,

unfortunately i dont have rights to change the NLS Settings.

Thanks
vsi

[Krazykoolrohit]

but what exactly is it set to?

still i dont think there is any NLS setting which does not recognise a '?'

[DeepakCorning]

As far as I know it the special chracter in the data is not '?' but it is something else. As the job is not able to handle it it changes the special character in a '?'.

Depending on what special character it is you can chaneg the NLS settings in the job and then it will handle the special character till end.

Are you getting an "ORA" error or a normal error? This will clearly tell us that it is at the DB level or job level.

[narasimha]

If you have oracle client installed, then check if NLS_LANG is set in the registery

In your SQL Plus prompt type

Code: Select all

SQL> @.[%NLS_LANG%].

If you get something like:
unable to open file ".[AMERICA_AMERICA.WE8MSWIN1252]."
the "file name" between the '[]' is the value of the registry parameter.

Could you please post the warning message you are getting

[meena]

Hi Vsi,
If you are not sure about the data, check with the concern person and try to replace or delete the record.But most of the times this happens because of NLS settings..and special character gets rejected in oracle database...Try checking by setting the contain terminator to yes for the row...If you get a problem:"Column Description cannot contain a non-quoted terminator " then try to check as Kris007 suggested.....

i can strip this character. but i am not always sure,that this is the character which i receive from Source.

seems the record is corrupted.

somehow,the record should be inserted as it is..

is it possible?

If it is the only character,i can use Ereplace to ignore this character.
but i am not sure, the souce may contain only this character.

[pkomalla]

You can use

Iconv(Iconv(coulmnname, "MCA"),"MCN") to get only alphabetic and numerical values from the column and avoid all other.

[pkomalla]

You can use

Iconv(columnname,"MCA"),"MCN") which gives data containing only alphabets and numerics, all other or removed