ORA-29275 Partial multibyte character
Occasionally partial data is stored in the oracle database due to loading or character set errors. When selecting these records, Oracle will throw ORA-29275 Partial multibyte character error. If due to character set issue, CONVERT function can be used to convert to proper character set.
Though you’ll be able to run commands like CTAS without issues, any attempt to fetch the row will result in the error message. If you’re migrating to a different database platform - say using AWS DMS, the load will fail until the error is corrected.
The common error due to character set can be fixed with CONVERT as described here.
More often than not, the data itself is the issue - sometimes truncated by substrb to fit the length, sometimes extracted and loaded using wrong character set which cannot be fixed using the convert function.
Identifying the offending records
This is usually the hardest part especially if the table is large. One option is to split the selects into smaller subset until the offending rows were found.
The first option i tried after lot of online search is to append a NULL to the string and check the length - like this:
-- Assuming the "city" field has the bad data:
select emp_id, emp_name, dump(city),lengthb(city) from emp
where lengthb(city) != lengthb(city || null);
update emp
set city = city || null
where lengthb(city) != lengthb(city || null);
commit;
If there’s an hit, the offending record is identified. However, this fixes only the last byte. If the field has multiple partial multi-bytes, after fixing the record once, the same record will keep popping up as error and the process needs to be repeated.
The second option i found is to use the to_nchar function - this supposedly discards the partial characters.
-- Assuming the "city" field has the bad data:
select emp_id, emp_name, dump(city),lengthb(city) from emp
where lengthb(city) != lengthb(to_nchar(city));
-- Using MOD
select emp_id, emp_name, dump(city),lengthb(city) from emp
where mod(lengthb(city),lengthb(to_nchar(city))) > 0;
update emp
set city = to_nchar(city)
where lengthb(city) != lengthb(to_nchar(city));
commit;
The to_nchar method is able to identify fields that weren’t caught using the null append method.
Surely, there are more ways to address these. Use what works best.