Thanks for the question, m.
Asked: June 23, 2016 - 9:30 am UTC
Last updated: June 30, 2016 - 3:05 am UTC
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Viewed 1000+ times
You Asked
Hi there,
I'am new to programming at all and I've managed to form 2 SQL Statements, filtered by date, which returns multiple rows of BLOB data and the other statement returns the compiled name the BLOB's should have at the end.
The plan is to schedule a procedure which exports all BLOBS the Statement returns at a specific time to a given folder on the computer or network.
I couldn't get any code to work yet I found in the internet. It would be very nice of you if someone can help me.
Thanks in advance.
and Connor said...
I took from an existing question
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6379798216275 See how you go
declare
vblob blob;
vstart number:=1;
bytelen number := 32000;
len number;
my_vr raw(32000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'f:\gol\ora_vscan';
p_file varchar2(30) default 'CMsitegarden.pdf';
begin
-- get the blob locator
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
for l_cur in (select blob_col mylob from my_table)
loop
len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));
vstart := 1;
while (vstart < len) loop -- loop till entire data is fetched
dbms_output.put_line('vstart : ' || to_char(vstart));
DBMS_LOB.READ(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
vstart := vstart + bytelen ;
end loop;
utl_file.fclose(l_output);
end loop;
end ;