Function for converting BLOB to CLOB

The below is the function to convert BLOB to CLOB datatypes.

Before going to the function, lets understand what are those datatypes about:

BLOB data type

A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.
The length is given in bytes for BLOB unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Note: Length is specified in bytes for BLOB.

Syntax

{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]

CLOB data type


A CLOB (character large object) value can be up to 2,147,483,647 characters long. A CLOB is used to store unicode character-based data, such as large documents in any character set.

The length is given in number characters for both CLOB, unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Length is specified in characters (unicode) for CLOB.


Syntax
{CLOB |CHARACTER LARGE OBJECT} [ ( length [{K |M |G}] ) ]



FUNCTION:




CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)

RETURN CLOB

AS

     v_clob    CLOB;

     v_varchar VARCHAR2(32767);

     v_start      PLS_INTEGER := 1;

     v_buffer  PLS_INTEGER := 32767;

BEGIN

    
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

     

     FOR i IN
1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)

     LOOP

          

        v_varchar :=
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));



          
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);



          v_start := v_start +
v_buffer;

     END LOOP;

     

   RETURN v_clob;

  

END blob_to_clob;

/