SQL & PL/SQL

Hi all,

with the below code i was able to insert the PDF file into the Table

create pdm(pk number primary key, pdf_file blob, pdf_clob clob);

DECLARE
src_file BFILE := BFILENAME('SSS_DIR', '1232.pdf');
lgh_file BINARY_INTEGER;
dst_file BLOB;
BEGIN
INSERT INTO pdm
VALUES (1,'1232.pdf', EMPTY_BLOB (),null)
RETURNING pdf_file
INTO dst_file;
DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);

dbms_lob.close(src_file);
commit;
END;



Once the PDF file is inserted in the PDM table, i am not able to read the PDF data from the below code, This code converts the binary data into the Character data i.e BLOB data into the CLOB, but still the data is not proper which is getting inserted

Also the l_clob_len is 61506


create or replace procedure blob_to_clob is

vBuffer VARCHAR2(32767);
l_amount BINARY_INTEGER := 10000;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;

v_file_clob clob;
v_file_size integer := dbms_lob.lobmaxsize;
v_dest_offset integer := 1;
v_src_offset integer := 1;
v_blob_csid number := dbms_lob.default_csid;
v_lang_context number := dbms_lob.default_lang_ctx;
v_warning integer;
plob blob;
BEGIN
select pdf_file into plob from pdm where id_pk=1;

IF plob IS NOT NULL
AND LENGTH(plob) > 0
THEN
dbms_lob.createtemporary(v_file_clob, TRUE);
dbms_lob.converttoclob(v_file_clob,
plob,
v_file_size,
v_dest_offset,
v_src_offset,
v_blob_csid,
v_lang_context,
v_warning);
IF dbms_lob.NO_WARNING != v_warning
THEN
dbms_output.put_line('Function blob_to_clob warning:' || v_warning);
END IF;

l_clob_len := dbms_lob.getlength(v_file_clob);
dbms_output.put_line('l_clob_len'||l_clob_len);

update pdm
set pdf_clob=v_file_clob
where id_pk=1;

commit;
DBMS_LOB.FREETEMPORARY(v_file_clob);

ELSE
--RETURN NULL;
dbms_output.put_line('Not working' || v_warning);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Function blob_to_clob error:' || SQLCODE || SQLERRM);
-- RETURN NULL;
END ;



can anyone please help me..