I need a generic PL/SQL code for getting converted the special characters into standard ASCII characters as follows:- ÜNLÜ--> UNLU, JÓNÁS-- JONAS etc

Question

Tried working the script but special characters are getting changed to question mark.

Answer 1

Thanks to @Wernfried Domscheit for pointing out the flaws in my answer that may cause it not to work for you. I have now edited my answer to address those issues.

Firstly, in order to see and enter the accented characters, you need to have your client system working in a character set that supports these characters. US ASCII 7-bit does not support accented characters. (Explanation here.)

UTF-8 is now by far the most popular character set on the internet and is becoming more popular in commercial systems, because it does support just about every character system on the planet. Other character sets that support accented characters include the Windows-12xx family and the ISO-8859 family. If you can tell us more about the client system (Windows? Mac? UNIX?) and the application you are using to access the database, we can be more specific.

I can reproduce the symptoms of your problem and solve it in my case.

First of all I check the server characterset:

select * from nls_database_parameters where parameter like '%CHARACTERSET%';

PARAMETER              VALUE
---------------------- ----------
NLS_CHARACTERSET       AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

So varchar2 columns will be encoded in UTF-8 on my server.

I'm running Oracle on Linux with $LANG=en.US.UTF-8 on my client. I can confuse the client by defining $NLS_LANG (for the client) to use an ISO-8859 character set:

$ export NLS_LANG=ENGLISH_AMERICA.WE8ISO8859P1

Then in SQL*Plus I select a varchar2 column:

select word from test;

and the result is:

WORD
--------------------------------
�B�D�FGH�J

The question marks (actually "unknown character") are highlighting the mismatch between the characters I selected and what I told the client to expect.

If, at the operating system prompt, I set $NLS_LANG to match the client set-up, like this:

$ export NLS_LANG=ENGLISH_AMERICA.AL32UTF8 

and run exactly the same query on the same data in SQL*Plus:

select word from test;

the result is:

WORD
--------------------------------
ÁBÇDÉFGHÍJ

If your server is storing accented characters correctly then it must be using a character set that supports these (examples above). Your client also needs to support a character set that can handle accented characters and your NLS_LANG setting need to match what the client can support. How you do that will depend on what client system you are using.

When you have a client that can display, and allow you to enter, accented characters, then you can solve your original problem. You don't need a PL/SQL function to do the conversion, you simply use the Oracle translate function, like this:

select word, translate(word, 'ÁÇÉÍ', 'ACEI') as no_accents from test;

WORD       NO_ACCENTS
---------- ----------
ÁBÇDÉFGHÍJ ABCDEFGHIJ