clob_to_varchar2

CREATE OR REPLACE FUNCTION kf_clob_to_varchar2 (

   p_clob_in                       CLOB )

   RETURN VARCHAR2

IS

   x_ctr                           PLS_INTEGER DEFAULT 1;

   l_chunk_size                    PLS_INTEGER DEFAULT 4000;

   l_return                        VARCHAR2 ( 32767 ) DEFAULT NULL;

BEGIN

   IF DBMS_LOB.getlength ( p_clob_in ) > 32767

   THEN

      RETURN NULL;

   END IF;



WHILE         ( NVL ( l_return,
0 ) ) <> DBMS_LOB.getlength ( p_clob_in )
LOOP
l_return := l_return || DBMS_LOB.SUBSTR ( p_clob_in,
l_chunk_size,
( l_chunk_size * ( x_ctr - 1 ) ) + 1 );
x_ctr := x_ctr + 1;
END LOOP;


RETURN l_return;
END kf_clob_to_varchar2;