Saturday, November 13, 2010

How to write blob into OS file ?

1. create a database directory :
create or replace database directory image_test_dir
as 'd:\db_dir\'

2. use dbms_lob and utl_file package to read data chunk wise.



declare
v_offset number := 1;
v_buffer_size number := 1000;
v_file_handle UTL_FILE.FILE_TYPE;
v_src_lob blob;
v_len integer;
v_buffer raw(1000);

begin
/*select image_front into v_src_lob
from inward_image where rownum=1;*/

select img into v_src_lob
from image_upload where rownum=1;

v_len := DBMS_LOB.GETLENGTH(v_src_lob);

v_file_handle := UTL_FILE.FOPEN('IMAGE_TEST_DIR', 'f.pdf', 'wb');

while v_offset < v_Len
loop

DBMS_LOB.READ(v_src_lob, v_buffer_size, v_offset, v_buffer );

UTL_FILE.PUT_RAW(v_file_handle, v_buffer);

--DBMS_OUTPUT.PUT_LINE( v_offset || ' ' || v_buffer_size );

v_offset := v_offset + v_buffer_size;


end loop;


--DBMS_OUTPUT.PUT_LINE();

UTL_FILE.FFLUSH(v_file_handle);

UTL_FILE.FCLOSE(v_file_handle);


end;

No comments:

Post a Comment