CODEWITHSUNDEEP
oracle-databaseblob
Bilel Chaouadi
Bilel Chaouadi

Reputation: 933

BlobToClob not working as expected in AL32UTF8

I have recently exported a database from WE8MSWIN1252 charset to AL32UTF8

I have a function to convert a BLOB to CLOB

    FUNCTION BlobToClob( stream IN BLOB ) RETURN CLOB IS
     {
                         .
                         .
        DBMS_LOB.WriteAppend(cResult,DBMS_LOB.GETLENGTH(stream)-nPosition+1,UTL_RAW.cast_to_varchar2(DBMS_LOB.SUBSTR(stream,DBMS_LOB.GETLENGTH(stream)-nPosition+1,nPosition)));
                         .
                         .
      }

I get the following erro when I run this function in AL32UTF8 database.

ORA-06512 length of input buffer is smaller than amount requested

Do you have any idea ?

Thanks,

Bilel

Upvotes: 0

Views: 208

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Use the standard function DBMS_LOB.CONVERTTOCLOB. You'll need to provide the source encoding, for instance:

DECLARE
   l_dest_lob     CLOB;
   l_src_blob     BLOB;
   l_dest_offset  INTEGER := 1;
   l_src_offset   INTEGER := 1;
   l_lang_context INTEGER;
   l_warning      INTEGER;
BEGIN
   dbms_lob.converttoclob(dest_lob     => l_dest_lob,
                          src_blob     => l_src_blob,
                          amount       => dbms_lob.blobmaxsize,
                          dest_offset  => l_dest_offset,
                          src_offset   => l_src_offset,
                          blob_csid    => nls_charset_id('WE8MSWIN1252'),
                          lang_context => l_lang_context,
                          warning      => l_warning);
END;

Upvotes: 1

Related Questions