accented letters and sql plus/AIX

Question

I have a plsql script that I modify in sql developer (encoding = UTF8). I put my script on the server using Filezilla (transfert binary). On the server, the NLS_LANG is AMERICAN_FRANCE.WE8ISO8859P1.

In my script I added :

ALTER SESSION SET NLS_LANG=FRENCH_FRANCE.UTF8;

I lauch the script in a sql plus session : the characters do not appear properly.

How can I make it work?

Thank you

In a putty session to launch the sql file I write this :

$ORACLE_HOME/bin/sqlplus id/psw@$ORACLE_SID @$P_SQL/myfile.sql

In the file, the display is like this for example :

center 'à è é ê ô à ë î ï ù' skip 1 -

inside a spool.

what does the script do? show us the line that reads/changes the characters. do you start sqlplus on the server or on your client? — kevinskio
what does myfile.sql do? If you are using putty please confirm you are launching myfile.sql on the server. Do you modify myfile.sql on the server or using sql developer on your desktop? — kevinskio
using sql developer on my desktop. the purpose of the sql is to spool some files and display some things — mlwacosmos
What happens if you set NLS_LANG to UTF8 on the server, before launching SQL*Plus? Do you not see the characters properly in the SQL*Plus session, or when you view the spooled file? — Alex Poole

Lorinczy Zsigmond Lorinczy Zsigmond · Accepted Answer · 2015-06-18T08:43:05

First of all, putty's setting Windos/Translation/Remote_character_set and environment variable LC_CTYPE should match, eg: ISO-8859-1 and fr_FR.ISO8859-1; or UTF-8 and fr_FR.UTF-8

Note: LC_CTYPE can be set remotely (putty: Connection/Data/Environment_variables), if /etc/ssh/sshd_config allows if (AcceptEnv)

Also it doesn't hurt if your AIX supports the selected charset, find out with locale -a

If/when every character works in ksh/bash/mc/etc then you can try to experiment with Oracle: set NLS_LANG to american_america.WE8ISO8859P1 or american_america.AL32UTF8 (in sync with LC_CTYPE and Translation)

PS: I think you should set NLS_LANG before starting any Oracle-program, ALTER SESSION doesn't change character set.

accented letters and sql plus/AIX

2 Answers