BLOB-->CLOB-->BLOB conversion reducing length of BLOB
515994Jun 1 2006 — edited Jun 1 2006Hi,
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.