Hi,

I am loading data using simple merge statement if records are present then update else insert.
Sepcial characters like é , á , ó. They are getting converted into '?'.
Database character set is AL32UTF8.

Thanks,
Amit

How many rows are inserted ? (by using putty server).

Same issue i got 3 months back.

to resolve this issue.

1) Delete those rows from table.
2)Use TOAD tool to reinsert

Hi,

I can not use any third party tool like TOAD to connect db. Could you please suggest any other way?

Regards,
Amit

Surely NOT use TOAD. You'll add one level in the mess.
You MUST first understand the problem.

So you think because something "worked", actually it does not you just have been lucky to get the expected result, it will work for ALL other people, without knowing what was your problem and what is his.

This is a silly behavior.

SELECT DECODE (parameter,
               'NLS_CHARACTERSET', 'CHARACTER SET',
               'NLS_LANGUAGE', 'LANGUAGE',
               'NLS_TERRITORY', 'TERRITORY'
              ) NAME,
       VALUE
  FROM v$nls_parameters
 WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')

Hi,

Putty server?

You cannot get it with this query.
If your client is an OCI one, version 11g+, you can get it with V$SESSION_CONNECT_INFO view.

Just execute "env | grep NLS" from Unix prompt.
You didn't say which client tool you use, is this SQL*Plus?

Just execute "env | grep NLS" from Unix prompt.
You didn't say which client tool you use, is this SQL*Plus?

SQL> @[%NLS_LANG%]
SP2-0310: unable to open file "[%NLS_LANG%].sql"
SQL>

do you have a data storage problem (incorrect characters in the database table)?
or
do you have a data presentation problem (incorrect characters displayed when SELECTED)?

SELECT COLUMN1, ASCIISTR(COLUMN1) FROM TABLEA WHERE ID = 1;

convert SELECT above to work in your environment & post results using COPY & PASTE

SQL> desc test_lang
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LANG                                               NVARCHAR2(50)

SQL> insert into test_lang values('Série');

1 row created.

SQL> select * from test_lang;

LANG
--------------------------------------------------
S??rie

SQL> commit;

Commit complete.

SQL> select lang,ASCIISTR(lang) from test_lang;

LANG
--------------------------------------------------
ASCIISTR(LANG)
--------------------------------------------------------------------------------
S??rie
S\FFFD\FFFDrie

Michel Cadot wrote on Mon, 23 May 2016 18:28

Just execute "env | grep NLS" from Unix prompt.
You didn't say which client tool you use, is this SQL*Plus?

I executed env | grep NLS but no output. seems the env variable has not declared.

Also executed following command and it is cleared that the variable NLS_LANG is not set in the environment.

SQL> @[%NLS_LANG%]
SP2-0310: unable to open file "[%NLS_LANG%].sql"
SQL>

amit.sonar wrote on Mon, 23 May 2016 15:43

Michel Cadot wrote on Mon, 23 May 2016 18:28

Just execute "env | grep NLS" from Unix prompt.
You didn't say which client tool you use, is this SQL*Plus?

I executed env | grep NLS but no output. seems the env variable has not declared.

Also executed following command and it is cleared that the variable NLS_LANG is not set in the environment.

SQL> @[%NLS_LANG%]
SP2-0310: unable to open file "[%NLS_LANG%].sql"
SQL>

If NLS_LANG is not set then you tell Oracle you use the default character set which does not contain any "special" character, so Oracle has to convert these ones to something you know and to do so it replaces these characters by one that is named a replacement character.

Tell Oracle the character set you actually used and there should be no problem.
As you are in Linux, generally your OS character set is UTF8 and so you have to set NLS_LANG with AL32UTF8.
Read Setting the NLS_LANG Environment Variable and Setting NLS_LANG.

With SQL*Plus, no.
What is the problem to set a variable?

It is an Oracle only variable.
If you not use it you have bad data in your database.
I don't see where is the problem.
So the choice is:

not use it and have bad data <-> use it and have correct data

Just tell that and you will have all approvals.

Hi Michel,

what columns values we need to fetch and see from V$SESSION_CONNECT_INFO View

SQL> desc V$SESSION_CONNECT_INFO
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SID                                       NUMBER
 SERIAL#                                   NUMBER
 AUTHENTICATION_TYPE                       VARCHAR2(26)
 OSUSER                                    VARCHAR2(30)
 NETWORK_SERVICE_BANNER                    VARCHAR2(4000)
 CLIENT_CHARSET                            VARCHAR2(40)
 CLIENT_CONNECTION                         VARCHAR2(13)
 CLIENT_OCI_LIBRARY                        VARCHAR2(27)
 CLIENT_VERSION                            VARCHAR2(40)
 CLIENT_DRIVER                             VARCHAR2(9)
 CLIENT_LOBATTR                            VARCHAR2(23)