Fix of "ORA-29275: partial multibyte character"
This happened to me when I was trying to migrate the Oracle Database to Advanced Server & PostgreSQL.
This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown.
ORA-29275:
partial multibyte character
Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.
Which doesn't give much information.
To find the the column and rows which have those junk/invisible character user can try following trick.
To get the data, without junk/invisible characters, user can also use Convert Function.
To fix this issue, I have used the convert Function of oracle to display the result.
Using Above function, I have created a view on top of the Table Data and using that view, I have been able to Migrate the data of Oracle.
This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown.
select * from junk_character; 'ORA-29275: partial multibyte character'Oracle Descritption for this error is given below:
ORA-29275:
partial multibyte character
Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.
Which doesn't give much information.
To find the the column and rows which have those junk/invisible character user can try following trick.
a. Select the data column wise as given below: select col1 from tablename; select col2 from tablename;If you are sure about the columns which has those junk character, then you can do the range data SELECT using ROWNUM to find the Rows which has those Junk Character.
b. select col1,col2 fromAfter finding the rownum, user either can update the column value Or delete the rows if those are not important.rownum between lowerbound and upperbound;
To get the data, without junk/invisible characters, user can also use Convert Function.
To fix this issue, I have used the convert Function of oracle to display the result.
CONVERT(COLUMN NAME,'NLS_CHARACTERSET','NLS_CHARACTERSET');Above function helped me.
Using Above function, I have created a view on top of the Table Data and using that view, I have been able to Migrate the data of Oracle.
Hi,
ReplyDeleteI'm getting the below error:
CONVERT(EMP_NAME,'NLS_CHARACTERSET','NLS_CHARACTERSET'),
*
ERROR at line 12:
ORA-01482: unsupported character set
Thanks,
Prashanth
NLS_CHARACTERSET is parameter of Oracle. Please check that parameter in oracle database and use it accordingly.
DeleteThanks works.
ReplyDeleteAnother trick is to append an empty string to the end of the column
ReplyDeleteselect col1 || '' from
Worked for me thanks. here is what I used
ReplyDeleteconvert(COLNAME,'US7ASCII','WE8ISO8859P1')
It worked for me too. Thanks. - sfbayman
DeleteThe empty string option worked well :)
ReplyDelete