Thursday, August 13, 2015

Find "Bad character in format or data" using OTranslate function

Bad character in format or data

This error is reported when you try to translate a (Varchar or char) column with special characters in it to (Decimal or integer).When a query fails with such errors, the primary challenge is to find our where the problems lies and which column might have special character. the answer is where there is explicit or implicit conversion happening from character columns to integer.

More cumbersome job is to find what is that value in column which is causing the issue, here is a technique using which one can identify the value quickly.


  sel roll from perf_app.$ab_temp   WHERE CHAR_LENGTH(roll) <> CHAR_LENGTH(oTranslate(roll, '/_#.'',-*)(:"', ''))

here the sys_fnlib.oTranslate function can identify the special character and provide the lenth of the value after replacing with ''(blank).