CODEWITHSUNDEEP
sqloracle-databaseplsql
Bhanu Yadav
Bhanu Yadav

Reputation: 169

Convert function not working with CLOB datatype

We have some data of type lob and varchar2 which is wrongly encoded to AL32UTF8and we need to convert its encoding to we8mswin1252 character set. I got convert function which is used for character set conversion and this function is working fine with varchar2 datatype but its giving some fuzzy characters when used with CLOB. My current encoding is AL32UTF8.

 select value,CONVERT(value,'we8mswin1252','AL32UTF8'),CONVERT(to_clob(value),'we8mswin1252','AL32UTF8') from temp;

enter image description here

Database: Oracle 12c

select * from nls_database_parameters where parameter like  '%CHARACTERSET%';

enter image description here

Upvotes: 0

Views: 1491

Answers (1)

Bhanu Yadav
Bhanu Yadav

Reputation: 169

Answer I got from Oracle: "The CONVERT function is a legacy function that should not be used for any application-related character set conversion. CLOB is especially tricky as it uses a special storage encoding in multibyte databases, such as AL32UTF8. CONVERT is useful for some repair tasks only. The supported way of processing data in character sets other than the database character set is to use the RAW data type and the conversion procedures from the package UTL_I18N. However, UTL_I18N does not support CLOBs. Therefore, you do have to read CLOBs in 32K chunks, convert them to WE8MSWIN1252 with UTL_I18N.STRING_TO_RAW, do your post-processing and write the file with UTL_FILE.PUT_RAW."

Also I got another unix command iconv which converts file encoding so basically I was writing db output to file so converted its encoding and transfered the data.

Thanks

Upvotes: 0

Related Questions