Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Data Corruption (Cyrillic characters) while converting the column from BLOB to CLOB

Based on product migration, I want to replace below mentioned single byte character to Double byte character in each and every record but the respective column is BLOB datatype so I'm using the below procedure to convert column from BLOB to CLOB and replace the single byte character to Double byte character and convert back to CLOB to BLOB.
Single byte ---> Double byte
ў --> яЃО
§ --> яЃН
ќ --> яЃН
But when i tried with CL8ISO8859P5 character set in CLOB conversion function, Bulgarian data is corrupted and If i tried with AL32UTF8 character set then the respective byte got corrupted.
Please advice how to proceed with this conversion
CLOB Bulgarian conversion.JPGCLOB unicode conversion.JPG
CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
l_clob CLOB;
l_dest_offset NUMBER := 1;
l_src_offset NUMBER := 1;
l_lang_context NUMBER := dbms_lob.default_lang_ctx;
l_warning NUMBER;
BEGIN
dbms_lob.createtemporary(l_clob, TRUE);
dbms_lob.converttoclob(dest_lob => l_clob,
src_blob => l_blob,
amount => dbms_lob.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => nls_charset_id('CL8ISO8859P5'),
lang_context => l_lang_context,
warning => l_warning);
RETURN l_clob;
END convert_to_clob;
/

CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
l_blob BLOB;
l_dest_offset NUMBER := 1;
l_src_offset NUMBER := 1;
l_lang_context NUMBER := dbms_lob.default_lang_ctx;
l_warning NUMBER;
BEGIN
dbms_lob.createtemporary(l_blob, TRUE);
dbms_lob.converttoblob(dest_lob => l_blob,
src_clob => l_clob,
amount => dbms_lob.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => nls_charset_id('AL32UTF8'),
lang_context => l_lang_context,
warning => l_warning);
RETURN l_blob;
END convert_to_blob;
/

CREATE OR REPLACE PROCEDURE convert_blob_file(p_tname in varchar2,parallel_no_cnt in number default 1,record_cmt_cnt in number default 1) IS
TYPE cur_typ IS REF CURSOR;
getcursordata cur_typ;
ConversionRecID VARCHAR2(2000);
BinaryValueSource BLOB;
BinaryValueDest BLOB;
BinaryValueTemp CLOB;
cnt NUMBER := 1;
Dicttablename VARCHAR2(2000);
Marker_R_count NUMBER := 0;
XmlrecordTemp CLOB;
Selectstring VARCHAR2(2000);
select_table_query VARCHAR2(1000):= 'SELECT /*+ PARALLEL('''||parallel_no_cnt||''') */ RECID,XMLRECORD FROM ' || p_tname || ' WHERE XMLRECORD IS NOT NULL';
BEGIN
OPEN getcursordata FOR select_table_query;
LOOP
FETCH getcursordata INTO ConversionRecID, BinaryValueSource;
EXIT WHEN getcursordata%NOTFOUND;
BinaryValueTemp := convert_to_clob(BinaryValueSource);
BinaryValueTemp := REPLACE(BinaryValueTemp,CHR(53662),CHR(15705022)); -- @FM Separator
BinaryValueTemp := REPLACE(BinaryValueTemp,CHR(49831),CHR(15705021)); -- @VM Separator
BinaryValueTemp := REPLACE(BinaryValueTemp,CHR(53660),CHR(15705020)); -- @SM Separator
BinaryValueTemp := REPLACE(BinaryValueTemp,CHR(53659),CHR(15705019)); -- @TM Separator
BinaryValueDest := convert_to_blob(BinaryValueTemp);
EXECUTE IMMEDIATE 'UPDATE /*+ PARALLEL('''||parallel_no_cnt||''') */'||p_tname||' SET XMLRECORD = :v1 WHERE RECID= :v2' USING BinaryValueDest,ConversionRecID;
IF cnt=record_cmt_cnt THEN
cnt:=1;
COMMIT;
ELSE
cnt := cnt + 1;
END IF;
END LOOP;
CLOSE getcursordata;
DBMS_OUTPUT.PUT_LINE('Time------'||to_char(sysdate, 'HH24:MI:SS'));
END;
/

This post has been answered by odie_63 on Apr 28 2022
Jump to Answer
Comments
Post Details
Added on Apr 27 2022
7 comments
1,085 views