Special/French Characters Conversion

Sometimes you may get special/french characters in your data which can cause
'ORA-06502: PL/SQL: numeric or value error: character string buffer too small' error.

Example : ÖÄ é è
Reason   : These single characters taking two byte length.

To overcome this issue, either increase the length of your column(sometimes it is not advisable due to your business need/design) or use convert function to convert these characters.

--> Get the NLS Character set defined
select value
  from nls_database_parameters
where parameter='NLS_CHARACTERSET';

--> Use Convert function
CONVERT(<char>, <destination_char_set>, <source_char_set>)

> Use destination Character set as 'US7ASCII' -> US 7-bit ASCII character set
> Use Source Character set as Character set returned above.

Example : select convert('Repport ÖÄ é è  de öäå','US7ASCII','<char set returned above>')
                  from dual;

>> If it will not find any valid conversion for any character it will put '?' for that character