0

I have a web application developed by third parties running on CentOS consulting an Oracle database. The database is configured as follows:

NLS_LANGUAGE = BRAZILIAN PORTUGUESE 
NLS_TERRITORY = BRAZIL 
NLS_CHARACTERSET = WE8ISO8859P1 
NLS_NCHAR_CHARACTERSET = AL16UTF16

Syntax of the CONVERT command:

CONVERT (string_expression, to_data_set [, from_data_set]);

example:

SELECT
   CONVERT ('Ä Ê Í', 'US7ASCII', 'WE8ISO8859P1')
FROM
   DUAL;

In my case, I'm trying to do as follows:

SELECT
CONVERT (expression, 'UTF8', 'WE8ISO8859P1')
FROM ...

But I can't find the correct combination of to_data_set and from_data_set to display the accented characters correctly with my query.

Any suggestion (including a different approach than using CONVERT command)?

asfonseca
  • 1
  • 1
  • 3
    Oracle [discourages the use of the convert function](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CONVERT.html), and has for a long time. Have you looked at the alternatives mentioned in that note? And are you sure the display issue is because of the DB character set(s), rather than the client/web application/browser settings? – Alex Poole Aug 04 '20 at 17:40
  • Yes, I checked the suggestions given when creating this post and none of them apply to my problem. As for modifying the application environment, I thought about it too and made several changes without success (this is a third-party application, without developer support). I know the status given by Oracle to CONVERT, so I also ask for an alternative solution. – asfonseca Aug 04 '20 at 18:06
  • What data type is `string_expression` - varchar2 or nvarchar2? – Alex Poole Aug 04 '20 at 18:09
  • Have a look at https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600 set your local NLS_LANG properly. – Wernfried Domscheit Aug 04 '20 at 18:28
  • string_expression is VARCHAR2 (2160 BYTE). – asfonseca Aug 04 '20 at 19:31
  • Just a guess, but have you tried defining `string_expression varchar2(2160 CHAR)` yet? – Mark Stewart Aug 04 '20 at 19:57
  • Well, probably `string_expression varchar2(2000 CHAR)` (or less!) to stay under 4000 bytes for legacy `VARCHAR2` size constraint. – Mark Stewart Aug 05 '20 at 13:42

0 Answers0