Skip to main content
Question

special characters

perry
Forum|alt.badge.img+2

Hello,

 

I'm reading data from an ORA database wicht contains

 

- a surname: Çad?rc?

 

- a name: ËCadõrcõ

 

In the inspector I see these names correct with encoded utf-8

when I write this information to an (different) ORA-database with Varchar2(800) the table contains:

 

- a surname: Çadrc (wrong)

 

- a name: ËCadõrcõ

When I put the writer items to AUTOMATIC it will give also the same output

When I change the TYPE in the writer from Varchar2 to NCHAR the table will contain:

 

- a surname: Çad?rc (wrong)

 

- a name: ËCadõrcõ

 

what am I doing wrong and how to correct this problem?

7 replies

erik_jan
Contributor
Forum|alt.badge.img+18
  • erik_janContributor
  • Contributor
  • April 20, 2017

And if you change the output type to NVARCHAR, but twice the size you had for VARCHAR2?

Seems you have a 2 bytes character set.

perry
Forum|alt.badge.img+2
  • perry
  • Author
  • April 20, 2017

Hi Erik_Jan,

Ik have followed your instructions and the surname is spelled correct now (Çad?rc?). I stil don't understand why the name: ËCadõrcõ do not have this problem.

I was trying to solve this problem with an attribute creator and @Evaluate([string map {Ä A ä a Ö O ö o Ü U ü u ß ss} )}]) but your solution is easier.

Anyway thanks for your quick response.

david_r
Celebrity
  • david_rCelebrity
  • April 21, 2017

Just for info, this is not an FME issue, but a more general one concerning unicode and how it relates to whatever character set is defined in your Oracle instance. My guess is that your Oracle instance has been set up with a character set that has support for the special characters in "ËCadõrcõ" but not for all the characters in "Çad?rc?". There is nothing you can do in FME that will change this, short of modifying the text (e.g. search/replace characters) before writing the text string to your Oracle table.

You can check which character set is used by your Oracle instance by executing

SELECT * FROM NLS_DATABASE_PARAMETERS

Using an NVARCHAR2 column in Oracle, as @erik_jan suggests, works around this limitation by using unicode rather than the Oracle defined character set.

perry
Forum|alt.badge.img+2
  • perry
  • Author
  • April 21, 2017

Hi @David and @Erik_Jan,

I believe David's suggestion is right.

I have changed every item with problems in the special characters to NVARCHAR with twice their size.

 

Some of them now show the correct data while other items do not have any changes.

 

i.e. name: Ülkü is still notated as ÈUlkÈu.

 

Unfortunately I have to deal with lots of foreign names in this workbench.

Do you have any suggestions about the characterset needed?

 

I do not have any knowledge about this.

Thanks for your support,

Perry

SELECT * FROM NLS_DATABASE_PARAMETERS

NLS_CALENDAR GREGORIAN

 

NLS_CHARACTERSET WE8MSWIN1252

 

NLS_COMP BINARY

 

NLS_CURRENCY $

 

NLS_DATE_FORMAT DD-MON-RR

 

NLS_DATE_LANGUAGE AMERICAN

 

NLS_DUAL_CURRENCY $

 

NLS_ISO_CURRENCY AMERICA

 

NLS_LANGUAGE AMERICAN

 

NLS_LENGTH_SEMANTICS BYTE

 

NLS_NCHAR_CHARACTERSET AL16UTF16

 

NLS_NCHAR_CONV_EXCP FALSE

 

NLS_NUMERIC_CHARACTERS .,

 

NLS_RDBMS_VERSION 11.2.0.4.0

 

NLS_SORT BINARY

 

NLS_TERRITORY AMERICA

 

NLS_TIME_FORMAT HH.MI.SSXFF AM

 

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

 

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

 

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

david_r
Celebrity
  • david_rCelebrity
  • April 21, 2017
perry wrote:

Hi @David and @Erik_Jan,

I believe David's suggestion is right.

I have changed every item with problems in the special characters to NVARCHAR with twice their size.

 

Some of them now show the correct data while other items do not have any changes.

 

i.e. name: Ülkü is still notated as ÈUlkÈu.

 

Unfortunately I have to deal with lots of foreign names in this workbench.

Do you have any suggestions about the characterset needed?

 

I do not have any knowledge about this.

Thanks for your support,

Perry

SELECT * FROM NLS_DATABASE_PARAMETERS

NLS_CALENDAR GREGORIAN

 

NLS_CHARACTERSET WE8MSWIN1252

 

NLS_COMP BINARY

 

NLS_CURRENCY $

 

NLS_DATE_FORMAT DD-MON-RR

 

NLS_DATE_LANGUAGE AMERICAN

 

NLS_DUAL_CURRENCY $

 

NLS_ISO_CURRENCY AMERICA

 

NLS_LANGUAGE AMERICAN

 

NLS_LENGTH_SEMANTICS BYTE

 

NLS_NCHAR_CHARACTERSET AL16UTF16

 

NLS_NCHAR_CONV_EXCP FALSE

 

NLS_NUMERIC_CHARACTERS .,

 

NLS_RDBMS_VERSION 11.2.0.4.0

 

NLS_SORT BINARY

 

NLS_TERRITORY AMERICA

 

NLS_TIME_FORMAT HH.MI.SSXFF AM

 

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

 

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

 

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

Looks like your Oracle instance is set up with Windows-1252 character set for your regular VARCHAR2 columns, which is fairly limited, see: https://en.wikipedia.org/wiki/Windows-1252

 

 

This means that you'll need to use NVARCHAR2 columns for all your text values.

 

 

If you still have encoding issues, a good start would be to work with your DBA and check that both Oracle instances and clients have been set up correctly.
S
perry wrote:

Hi @David and @Erik_Jan,

I believe David's suggestion is right.

I have changed every item with problems in the special characters to NVARCHAR with twice their size.

 

Some of them now show the correct data while other items do not have any changes.

 

i.e. name: Ülkü is still notated as ÈUlkÈu.

 

Unfortunately I have to deal with lots of foreign names in this workbench.

Do you have any suggestions about the characterset needed?

 

I do not have any knowledge about this.

Thanks for your support,

Perry

SELECT * FROM NLS_DATABASE_PARAMETERS

NLS_CALENDAR GREGORIAN

 

NLS_CHARACTERSET WE8MSWIN1252

 

NLS_COMP BINARY

 

NLS_CURRENCY $

 

NLS_DATE_FORMAT DD-MON-RR

 

NLS_DATE_LANGUAGE AMERICAN

 

NLS_DUAL_CURRENCY $

 

NLS_ISO_CURRENCY AMERICA

 

NLS_LANGUAGE AMERICAN

 

NLS_LENGTH_SEMANTICS BYTE

 

NLS_NCHAR_CHARACTERSET AL16UTF16

 

NLS_NCHAR_CONV_EXCP FALSE

 

NLS_NUMERIC_CHARACTERS .,

 

NLS_RDBMS_VERSION 11.2.0.4.0

 

NLS_SORT BINARY

 

NLS_TERRITORY AMERICA

 

NLS_TIME_FORMAT HH.MI.SSXFF AM

 

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

 

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

 

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

I have problems with special charachter in reading oracle layer with where condition. It doesnt recocnize Ë or Ç so i get the error

 

ORACLE Reader: Unable to execute SQL statement `SELECT "IPRO2013"."PARCELE_TSUR"."FID", "IPRO2013"."PARCELE_TSUR"."AREA", "IPRO2013"."PARCELE_TSUR"."AREA_NOMINAL", "IPRO2013"."PARCELE_TSUR"."EXACT_AREA", "IPRO2013"."PARCELE_TSUR"."STATE", "IPRO2013"."PARCELE_TSUR"."KONTROLLUAR", "IPRO2013"."PARCELE_TSUR"."LEVIZJE_TOTALE", "IPRO2013"."PARCELE_TSUR"."LIDHUR", "IPRO2013"."PARCELE_TSUR"."ZK", "IPRO2013"."PARCELE_TSUR"."NR_PAS", "IPRO2013"."PARCELE_TSUR"."PROBLEM", "IPRO2013"."PARCELE_TSUR"."LLONI_PAS", "IPRO2013"."PARCELE_TSUR"."PRONARI", "IPRO2013"."PARCELE_TSUR"."KONFIRMIM", "IPRO2013"."PARCELE_TSUR"."MARKIM", "IPRO2013"."PARCELE_TSUR"."KONFIRMIM_OLD", "IPRO2013"."PARCELE_TSUR"."GEOM" FROM "IPRO2013"."PARCELE_TSUR" WHERE fid in (select fid_tsur from parcele_tcen where fid_centroid in

(select fid from parcele where k_perfundimtar=1 AND zk!=1 and shtet = 0 and bashkia_G = (

'K?LCYR?')))' for table `IPRO2013.PARCELE_TSUR'

3"."PARCELE_TSUR" WHERE fid in (select fid_tsur from parcele_tcen where fid_centroid i

david_r
Celebrity
  • david_rCelebrity
  • May 1, 2020
shefqetlulja wrote:

I have problems with special charachter in reading oracle layer with where condition. It doesnt recocnize Ë or Ç so i get the error

 

ORACLE Reader: Unable to execute SQL statement `SELECT "IPRO2013"."PARCELE_TSUR"."FID", "IPRO2013"."PARCELE_TSUR"."AREA", "IPRO2013"."PARCELE_TSUR"."AREA_NOMINAL", "IPRO2013"."PARCELE_TSUR"."EXACT_AREA", "IPRO2013"."PARCELE_TSUR"."STATE", "IPRO2013"."PARCELE_TSUR"."KONTROLLUAR", "IPRO2013"."PARCELE_TSUR"."LEVIZJE_TOTALE", "IPRO2013"."PARCELE_TSUR"."LIDHUR", "IPRO2013"."PARCELE_TSUR"."ZK", "IPRO2013"."PARCELE_TSUR"."NR_PAS", "IPRO2013"."PARCELE_TSUR"."PROBLEM", "IPRO2013"."PARCELE_TSUR"."LLONI_PAS", "IPRO2013"."PARCELE_TSUR"."PRONARI", "IPRO2013"."PARCELE_TSUR"."KONFIRMIM", "IPRO2013"."PARCELE_TSUR"."MARKIM", "IPRO2013"."PARCELE_TSUR"."KONFIRMIM_OLD", "IPRO2013"."PARCELE_TSUR"."GEOM" FROM "IPRO2013"."PARCELE_TSUR" WHERE fid in (select fid_tsur from parcele_tcen where fid_centroid in 

(select fid from parcele where k_perfundimtar=1 AND zk!=1 and shtet = 0 and bashkia_G = (

'K?LCYR?')))' for table `IPRO2013.PARCELE_TSUR'

3"."PARCELE_TSUR" WHERE fid in (select fid_tsur from parcele_tcen where fid_centroid i

I'm assuming the column BASHKIA_G is of type NVARCHAR2 or similar.

Try prefixing the string literal with an N'...' to tell Oracle that it's an Unicode string that shouldn't be parsed, e.g.

where k_perfundimtar=1 AND zk!=1 and shtet = 0 and bashkia_G = (N'KËLCYRË')))

See also: https://stackoverflow.com/a/25990117

 

Reply


Helpful Members This Week

Community Stats

31,358
Posts
118,948
Replies
38,974
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.