create or replace FUNCTION CLOB_To_BLOB1 (p_CLOB IN CLOB) RETURN BLOB
AS
   v_BLOB	BLOB;
   v_RAW	RAW(32767);
   v_Start	PLS_INTEGER := 1;
   v_Buffer	PLS_INTEGER := 32767;
   v_CLOB_Len	NUMBER;
   v_Lob_Locator BLOB := EMPTY_BLOB();
   v_Loop_Ceil	NUMBER(10,4);
   xmllength    long;
BEGIN
     --If the Length of the CLOB is 0( NULL Clob then return) 
     xmllength := length(p_CLOB);
     dbms_output.put_line('p_CLOB length = '||xmllength); 
     v_CLOB_Len := DBMS_LOB.GETLENGTH(p_CLOB);

     dbms_output.put_line('Package p_CLOB length = '||v_CLOB_Len);

     IF NVL(v_CLOB_len,0) = 0 THEN
       DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE,DBMS_LOB.SESSION);
       v_Lob_Locator := v_BLOB ;
       RETURN	v_Lob_Locator;
     END IF;

     --If the Length of the CLOB less thab 32K then set the Buffer Length to CLOB Lenth
     IF v_CLOB_Len < 32767 THEN
        v_Buffer := v_CLOB_len;
     ELSE
        v_Buffer := 32767;
     END IF;

     dbms_output.put_line('Package v_Buffer1 length = '||v_Buffer);
     ----------------------------------------------------------------------------------
     DBMS_LOB.CREATETEMPORARY(v_BLOB, TRUE);
     v_Loop_Ceil:=v_CLOB_Len/v_Buffer; 
     dbms_output.put_line('Package v_Loop_Ceil length = '||v_Loop_Ceil);

     FOR i IN 1..CEIL(v_Loop_Ceil) LOOP
        dbms_output.put_line('inside loop1');
        v_RAW := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(p_CLOB, v_Buffer, v_Start));
        dbms_output.put_line('inside loop2');
        DBMS_LOB.WRITEAPPEND(v_BLOB, v_Buffer, v_RAW);
        dbms_output.put_line('inside loop3');
        v_Start := v_Start + v_Buffer;
        dbms_output.put_line('inside loop4');
        v_Buffer := v_clob_len - v_Start + 1 ;
        dbms_output.put_line('Package v_Buffer2 length = '||v_Buffer);

        IF (v_Buffer > 32767) THEN
           dbms_output.put_line('Package v_Buffer3 length = '||v_Buffer);
           v_Buffer := 32767; 
        END IF;
     END LOOP;

     v_Lob_Locator := v_BLOB;
     DBMS_LOB.FREETEMPORARY(v_BLOB);
     RETURN v_Lob_Locator;
END CLOB_To_BLOB1;

CREATE OR REPLACE FUNCTION clob_to_blob
  (p_clob      IN CLOB)
  RETURN          BLOB
AS
  v_blob          BLOB;
  v_dest_offset   INTEGER := 1;
  v_src_offset    INTEGER := 1;
  v_lang_context  INTEGER := 0;
  v_warning       INTEGER := 0;
  v_lob_locator   BLOB := EMPTY_BLOB ();
BEGIN
    DBMS_LOB.CREATETEMPORARY (v_blob, TRUE, DBMS_LOB.SESSION);  
    DBMS_LOB.CONVERTTOBLOB 
      (v_blob, 
       p_clob,
       DBMS_LOB.LOBMAXSIZE, 
       v_dest_offset, 
       v_src_offset, 
       DBMS_LOB.DEFAULT_CSID, 
       v_lang_context, 
       v_warning);
    v_lob_locator := v_blob;
    DBMS_LOB.FREETEMPORARY (v_blob);
    RETURN v_lob_locator;
END clob_to_blob;
/