Set serveroutput on
DECLARE
M BLOB;
n NUMBER:=1;
c blob;
l_limit NUMBER:=4000;
vm RAW(32767);
BEGIN
Dbms_Output.Put_Line('DECLARE
c BLOB;
BEGIN
dbms_lob.createtemporary(c,true);');
SELECT  BLOB_COL INTO M FROM TEST_BLOB;
--Dbms_Output.Put_Line(Length(M));
while (n+l_limit<=length(m)) loop
     vm:=(dbms_lob.substr(M,l_limit,n));
     Dbms_Output.Put_Line('dbms_lob.writeappend(c,'||Length(vm)/2||','''||VM||''');');
     n:=n+l_limit;
END LOOP;
vm:=(dbms_lob.substr(M,length(m)-n+999,n));
Dbms_Output.Put_Line('dbms_lob.writeappend(c,'||Length(vm)/2||','''||VM||''');');
Dbms_Output.Put_Line('INSERT INTO blob_test VALUES (c,9);
END;
/');
END;
/