Running SQL*Plus with bash causes wrong encoding

9,996

Ok, the problem wasn't in the file (encoding was UTF-8 as it should be) but in the setting of Oracle NLS_LANG environmental variable. So solution was putting this line before executing SQL*Plus script

NLS_LANG="CZECH_CZECH REPUBLIC.UTF8" export NLS_LANG

Share:
9,996
Petr Mensik
Author by

Petr Mensik

I mostly do Java and Java EE stuff although I am currently also interested in security and big data. SOreadytohelp

Updated on September 18, 2022

Comments

  • Petr Mensik
    Petr Mensik over 1 year

    I have a problem with running SQL*Plus in the bash. Here is my code

    #!/bin/bash
    
    #curl http://192.168.168.165:8080/api_test/xsql/f_exp_order_1016.xsql > script.sql
    wget -O script.sql 192.168.168.165:8080/api_test/xsql/f_exp_order_1016.xsql
    set NLS_LANG=_.UTF8
    sqlplus /nolog << ENDL
    connect login/password 
    set sqlblanklines on
    start script.sql
    exit
    <<endl
    

    I download the insert statements from our intranet, put it into sql file and run it through SQL*Plus. This is working fine. My problem is that when I save the file script.sql my encoding goes wrong. All special characters(like íášč) are broken and that's causing inserting wrong characters into my DB. Encoding of that file is UTF-8, also UTF-8 is set on the XSQL page on our intranet. So I really don't know where could be a problem.

    And also any advices regarding to my script are welcomed, I am total newbie in Linux scripting:-)

  • Petr Mensik
    Petr Mensik about 12 years
    Thanks for advice, I was able to set characters in file right through iconv -f UTF8 -t CP1250 < temp.sql > script.sql(ISO8859-2 didn't work for me), so the file is looking fine. But characters in my DB are still broken as they were after insertion. So I am inserting a correct file with correct characters and it's still broken in DB. I also looked to DB encoding at oracle server and it's UTF8. So am I doing something wrong?
  • ConcernedOfTunbridgeWells
    ConcernedOfTunbridgeWells about 12 years
    You could load the data into a staging table with the columns as CP1250 then do a secondary load that explicitly does the conversion. However, if this worked before I'd suspect that maybe there's something wonky in your configuration.
  • Petr Mensik
    Petr Mensik about 12 years
    I don't have direct access to DB so I can't do such a change. Also I doubt that my boss would like this solution:-)But thanks for advice, at least I am closer to solution:-)