I am stuck at this for a while and had been Googling for answer but can't seem to find any....
I have an Oracle 9i table with
BLOB field that I need to extract the
BLOB field into a raw file on the LINUX server, there are thousand of rows so I need to write a kind of loop to accomplish this.
I got this :
DECLARE i NUMBER := 0; l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob BLOB; l_blob_len INTEGER; BEGIN FOR rec IN (SELECT id, fname, fblob FROM tablename where fname like '%png') LOOP i := i + 1; --DBMS_OUTPUT.put_line ('Record ' || i || ' id ' || rec.id); --DBMS_OUTPUT.put_line ('Record ' || i || ' fname ' || rec.fname); l_blob_len := DBMS_LOB.getlength(rec.fblob); -- Open dest file. l_file := UTL_FILE.fopen('BLOBDIR',rec.fname,'w', 32767); -- Read chunks of the BLOB and throw them to the file until done. WHILE l_pos < l_blob_len LOOP DBMS_LOB.read(rec.ATTACHMENT_BLOB, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP; END LOOP; -- DBMS_OUTPUT.put_line ('Procedure Looping Example is done'); END;
I tested the Loop with the
DBMS_OUTPUT and it worked fine. However:
- When I ran the code all 10 of the *.png files show up in my BLOBDIR - but the files is empty.
- When I ran the code for each individual file (i.e.
FOR rec IN (SELECT id, fname, fblob FROM tablename where fname ='filename.png')) - I got the file and it has data.
- When I ran the code with only 2 files in the loop (i.e.
FOR rec IN (SELECT id, fname, fblob FROM tablename where fname in ('filename.png', 'filename2.png')) - both files got written to the BLOBDIR - but only the first file has data, but the second file show 0 data.
I ran this code through TOAD, and through sqlplus - and same result. Can someone help me with this ? Thank you.