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!

BLOB-->CLOB-->BLOB conversion reducing length of BLOB

515994Jun 1 2006 — edited Jun 1 2006
Hi,
I am using the following BLOB-->CLOB and CLOB-->BLOB conversion functions :
CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar RAW(32001);
v_varchar1 VARCHAR2(32001);

v_start INTEGER := 1;
v_buffer INTEGER := 32001;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);


FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP

DBMS_LOB.READ(blob_in,v_buffer,v_start,v_varchar);
DBMS_LOB.WRITEAPPEND(v_clob, utl_raw.length(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;

RETURN v_clob;

END blob_to_clob;
/


CREATE OR REPLACE FUNCTION clob_to_blob (clob_in IN CLOB)
RETURN BLOB
AS
v_blob BLOB;
v_varchar RAW(32001);
v_varchar1 RAW(32001);
v_start INTEGER := 1;
v_buffer INTEGER := 32001;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(clob_in) / v_buffer)
LOOP
DBMS_LOB.READ(clob_in,v_buffer,v_start,v_varchar);
DBMS_LOB.WRITEAPPEND(v_blob, utl_raw.length(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;

RETURN v_blob;

END clob_to_blob;
/

I am using these functions to convert image files, stored as BLOB to CLOB for intermediate storage, and then converting them back to BLOB. In this process, I see a loss of information when the image is converted from CLOB to BLOB ...which is seen by using dbms_lob.getlength() .Using the following sql query:
select dbms_lob.getlength(file_body),
dbms_lob.getlength(blob_to_clob(file_body)),
dbms_lob.getlength(clob_to_blob(blob_to_clob(file_body)))
from my_table where image_id in (8819)
i see that returned length is usually half in the 3rd column, i.e. for dbms_lob.getlength(clob_to_blob(blob_to_clob(file_body))). There is no change in length for 1st and 2nd columns. Could you please let me know why length is getting lost in the CLOB to BLOB conversion ? Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 29 2006
Added on Jun 1 2006
2 comments
567 views