Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL: how to convert CLOB to BLOB

vivalavidaMar 28 2012 — edited Mar 29 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2012
Added on Mar 28 2012
7 comments
12,705 views