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.

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;

And to add unistr() you can use regexp replace

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

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!

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"));

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

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"

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 

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!

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