SQL & PL/SQL

Hi ,
I've went through this forum looking for some solutions about "how to convert clob to blob". I tried most of them, but they didn't succeed in our case :-(

Here's the code that I used:

create or replace procedure convertClobToBlob_V2
as

vBlob blob;
vClob clob;

vDestOffset integer := 1;
vSrcOffset integer := 1;

vCharset number;
vWarning number;
vErrCode number;
vInputLength number;
vCurrentPlace number := 1;
vPieceMaxSize number := 1000; -- the max size of each piece
vIndex number := 0;
vLangContext number := DBMS_LOB.default_lang_ctx;

vPiece varchar2(1000);
vErrMessage varchar2(250);


cursor cCursor is
select id
, col_clob
, col_blob
from test_table;

BEGIN

SELECT nls_charset_id('AL32UTF8')
INTO vCharset
FROM dual;

for vCursor in cCursor
loop

vIndex := vIndex + 1;
vCurrentPlace := 1;

vBlob := vCursor.col_blob;
vClob := vCursor.col_clob;

DBMS_LOB.createtemporary(vBlob, FALSE);

-- find the lenght of the clob
vInputLength := DBMS_LOB.getLength(vClob);

if (vInputLength > 0) then
-- loop through each piece
loop
-- get the next piece and add it to the clob
vPiece := DBMS_LOB.substr(vClob, vPieceMaxSize, vCurrentPlace);

-- append this piece to the BLOB
DBMS_LOB.writeappend(vBlob, length(vPiece)/2, HEXTORAW(vPiece));

vCurrentPlace := vCurrentPlace + vPieceMaxSize ;

EXIT
WHEN vInputLength < vCurrentPlace;
end loop;
end if;
end loop;

commit;

exception
when OTHERS then
vErrCode := SQLCODE;
vErrMessage := substr(SQLERRM, 1, 250);
SYSTEMTECHNIK.Logging.writeLog('CLOB2BLOB', 'ORACLE-Exception in <convertClobToBlob_V2>: ', null, null, vErrCode, vErrMessage);
SYSTEMTECHNIK.Logging.writeLog('CLOB2BLOB', 'Prozedur <convertClobToBlob> mit Fehler beendet');
null;

end;
/

As a matter of fact the procedure works fine without any error - and the processing of each record takes some time. At least that shows me that something is happening at all :-)

But when I run this query afterwards

select col_clob, length(clob), col_blob length(col_blob)
from test_table;

only just a few columns of col_blob are filled with something, havaing a length > 0.
Pretty strange to me, but I currently don't know why the columns of col_blob don't get filled with anything.

Any help will be appreciated

Rgds
JH