PL/SQL: how to convert CLOB to BLOB
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