docker + oracle instant client : encoding problem

Question

I use this image:

image: sflyr/sqlplus:latest

and this script (gitlab-ci):

  script:  
  - |
    /instantclient_11_2/sqlplus login/pwd@//myserveur.prod:1521/sid.mysid<<EOF 
    @myscript.sql
    EOF

myscript.sql:

select 'é' from dual;

Result:

SQL> SQL> 
'?
--
??

There is an encoding problem...

I try to had this to the script to fix it, but it doesnt work:

script:  
      - apt-get update && apt-get install locales
      - apt-get update
      - dpkg-reconfigure locales
      - locale-gen
      - export LANG=en_US.UTF-8 
      - locale
      - |
        /instantclient_11_2/sqlplus login/pwd@//myserveur.prod:1521/sid.mysid <<EOF 
        @myscript.sql
        EOF

Result:

$ locale-gen
Generating locales (this might take a while)...
Generation complete.
$ export LANG=en_US.UTF-8
$ locale
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
locale: $ /instantclient_11_2/sqlplus login/pwd@//myserveur.prod:1521/sid.mysid <<EOF  # collapsed multi-line command
Cannot set LC_ALL to default locale: No such file or directory
SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 14 21:47:41 2021
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connecte a :
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
SQL> SQL> 
'?
--
??

Any idea on how to fix this ?

Setting NLS_LANG as shown in the answer should help. As an aside, I would strongly suggest getting a more recent version of SQL*Plus. Look at Oracle's Dockerfiles https://github.com/oracle/docker-images/tree/main/OracleInstantClient Even the most current release (21.1) will connect to your 12.2 DB. Also see https://blogs.oracle.com/opal/docker-for-oracle-database-applications-in-nodejs-and-python-part-1 if you want to continue using a Debian based Linux distribution. — Christopher Jones, Mar 16 '21 at 02:26

score 2 · Answer 1 · answered Mar 15 '21 at 06:58

Which editor did you use to create the .sql script? Which encoding did you set there?

Once you know this information, you have to tell it Oracle. This is done by the NLS_LANG parameter, e.g. export NLS_LANG=.AL32UTF8

See OdbcConnection returning Chinese Characters as "?"

docker + oracle instant client : encoding problem

1 Answers1