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Ë')))
See also: https://stackoverflow.com/a/25990117
