BLOB to CLOB Conversion : Cyrillic characters are not getting converted properly

Question

BLOB to CLOB Conversion : Cyrillic characters are not getting converted properly

Based on our product migration, I have an requirement to replace below mentioned single byte character to Double byte character in each and every record but the respective column is BLOB datatype encoded with AL32UTF8 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.

NLS_CHAR--> AL32UTF8

For ex: 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 Cyrillic character got corrupted.

select convert_to_clob(XMLRECORD) From F_TEC_OUTPUT_T

CLOB conversion with AL32UTF8 characterset:

ONE�АНУЛИРАНЕ НА ОПРОСТЕНА ФАКТУРА_�THREE

CLOB conversion with CL8ISO8859P5 characterset:

ONEўаааЃааа ааа аа ааа ааЁаЂааа аЄаааЂаЃа а_ўTHREE

select recid, dbms_lob.substr(XMLRECORD) as raw_xrec from F_TEC_OUTPUT_T

[enter image description here][1]

Please suggest the proper way to convert this.

Single byte ---> Double byte

ў --> яЃО

§ --> яЃН

ќ --> яЃМ

In RAW:

FE --> EFA3BE

FD --> EFA3BD

FC --> EFA3BC

FB --> EFA3BB

   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;```


  [1]: https://i.stack.imgur.com/t9w19.jpg