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-8when 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 outputWhen 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?

special characters

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.

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(astring map {Ä A ä a Ö O ö o Ü U ü u ß ss} )}]) but your solution is easier.

Anyway thanks for your quick response.

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.

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

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.

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

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Ë')))

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded