Solved

Special characters garbaged during installer file executor

  • 21 November 2019
  • 10 replies
  • 585 views

Userlevel 7
Badge +16
  • Superhero (Partner)
  • 389 replies

We noticed that special characters (Eg Swedish ÅÖÄ) are garbaged when deploy via IFS installer file executor. This happens to be due to the UTF-8 file encoding and it works fine when the file encoding is changed to ANSI (can test with attached script).

Has anyone encountered the same issue and found a smart way to get over it rather than changing file encoding?

Version: Apps10 UPD 6

 

Cheers!

icon

Best answer by dsj 27 November 2019, 10:17

View original

10 replies

Userlevel 7
Badge +17

Hi @dsj,

 

You can try to deploy the script to the database directly from a SQL tool like PLSQL Developer and it will deploy the script correctly since oracle supports multilingual databases with Unicode.

 

Hope this helps!

 

Himasha

Userlevel 7
Badge +16

Hi @dsj,

 

You can try to deploy the script to the database directly from a SQL tool like PLSQL Developer and it will deploy the script correctly since oracle supports multilingual databases with Unicode.

 

Hope this helps!

 

Himasha

@Himasha Kapugeekiyanage it works fine when deploying from SQLplus or  PLSQL developer.

We usually face this problem with sqlplus when NLS_LANG is not set properly but installer.cmd sets  NLS_LANG=AMERICAN_AMERICA.UTF8 so I guess it’s not the issue here.

 

Userlevel 5
Badge +10

Hi,
you should use UNISTR function.

SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;
UNISTR
------
abcåñö

To convert you can use function ASCISTR

SELECT ASCIISTR('abcåñö') FROM DUAL;
ASCISTR
-------
abc\00e5\00f1\00f6

To convert whole file you can use something like this:

DECLARE
str_ varchar2(32000) := q'[select 'ěščřžýáíé' from dual]';
BEGIN
dbms_output.put_line(asciistr(str_));
END;

 

Userlevel 5
Badge +10

And to add unistr() you can use regexp replace

SEARCH:
'[^']*\\[^']*'
REPLACE:
UNISTR\($0\)

Userlevel 7
Badge +16

Thanks a lot for all answers. unistr and asciistr won’t help in this situation since it’s not a problem with the database characterset but the problem relies with how the IFS installer process the file encoding. I’m specifically looking for a way to force the installer to use UTF-8 encoding during db file execution.

If it’s not possible, best thing from the developer perspective is to change the encoding of the file to ANSI if the script contains special characters.

Cheers!

Userlevel 7
Badge +16

This is a limitation of the installer.

 

I believe the problem is inside PlsqlFileReader.java.

BufferedReader input = new BufferedReader(new FileReader(fullFileName));

 

The problem with this line is explained here:

https://stackoverflow.com/questions/696626/java-filereader-encoding-issue

 

The constructors of FileReader always use the platform default encoding which is generally a bad idea.

Instead of FileReader you need to use new InputStreamReader(new FileInputStream(pathToFile), <encoding>).

 

I’m guessing this is the solution, but I haven’t tested it.

BufferedReader input = new BufferedReader(new InputStreamReader(new FileInputStream(fullFileName), "UTF8"));

Userlevel 7
Badge +16

 

Adding a byte order mark to the file header doesn’t help with UTF-8 detection.

Userlevel 7
Badge +16

I recommend executing your script with SQL*Plus. You can set your operating system environment variable NLS_LANG before execution to change the way it interprets your file.

(My example uses the EZCONNECT syntax. You may prefer to use TNSNAMES.)

 

C:\Users\Administrator>set NLS_LANG=AMERICAN_AMERICA.UTF8

C:\Users\Administrator>sqlplus ifsapp/password@databasehost:1521/servicename @"update_customer_name.sql"

Userlevel 7
Badge +16

Thanks a lot @durette . You are truly a mastermind!

Your findings shed the light to the issue. I was thinking the issue was with the database driver and I always had faith in Java :laughing:

You are absolutely correct that the issue is with encoding is not defined in the input stream reader and java uses machine default encoding, but… you can force the JVM to use a UTF-8 encoding by using -Dfile.encoding=UTF-8 parameter.

 

My issue was finally fixed by modifying the installer.cmd. 

"%INSTALLER_JAVA_HOME%\bin\java" %JAVA_DEBUG% -Xmx512m -classpath "%CLASSPATH%" -Dfile.encoding=UTF-8 …..

 

Not sure where else was broken with this but I hope not!

 

Cheers!

Userlevel 7
Badge +16

This is a limitation of the installer.

 

I believe the problem is inside PlsqlFileReader.java.

BufferedReader input = new BufferedReader(new FileReader(fullFileName));

 

The problem with this line is explained here:

https://stackoverflow.com/questions/696626/java-filereader-encoding-issue

 

The constructors of FileReader always use the platform default encoding which is generally a bad idea.

Instead of FileReader you need to use new InputStreamReader(new FileInputStream(pathToFile), <encoding>).

 

I’m guessing this is the solution, but I haven’t tested it.

BufferedReader input = new BufferedReader(new InputStreamReader(new FileInputStream(fullFileName), "UTF8"));

 

Above answer should be the permanent fix for this. It is also possible to enforce the encoding via -Dfile.encoding JVM param.

 

Issue was fixed by changing the installer.cmd with forcing the UTF-8 encoding

"%INSTALLER_JAVA_HOME%\bin\java" %JAVA_DEBUG% -Xmx512m -classpath "%CLASSPATH%" -Dfile.encoding=UTF-8 

 

Reply