Monday 23 June 2014

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(, , ) > 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','') from dual; >> If it will not find any valid conversion for any character it will put '?' for that character

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

No comments:

Post a Comment