Problem with fetching data from a cursor to a blob variable
KaZsaJan 26 2011 — edited Jan 26 2011Hi,
I am trying to retrieve the images stored as blob in a table and save them to a folder in my machine.I managed to do it to a one image file in the table and I was able to store it in my machine.
But now I am trying take blobs from the table and store it to a folder in my machine. The changes I did to the working code were,
1. Use a cursor to get the image blobs.
2. Fetch the images one by one to a one blob variable inside a loop.
Apart from above changes, I used the same code that was successful in the first instance. This is the code with the error,
DECLARE
v_lob_loc Hr.per_Images.IMAGE%type;
v_buffer RAW(32767);
v_buffer_size BINARY_INTEGER;
v_amount BINARY_INTEGER;
v_offset NUMBER(38) := 1;
v_chunksize INTEGER;
v_out_file UTL_FILE.FILE_TYPE;
MY_IMAGE_FILES VARCHAR2(200);
emp_no NUMBER := 1;
file_name VARCHAR2(20);
Cursor c1 is
Select IMAGE from HR.PER_IMAGES ;
BEGIN
select DIRECTORY_PATH INTO MY_IMAGE_FILES from dba_directories where directory_name = 'MY_IMAGE_FILES';
open c1;
loop
fetch c1 into v_lob_loc;
v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);
IF (v_chunksize < 32767) THEN
v_buffer_size := v_chunksize;
ELSE
v_buffer_size := 32767;
END IF;
v_amount := v_buffer_size;
file_name := emp_no || '.jpg';
v_out_file := UTL_FILE.FOPEN(
location => 'MY_IMAGE_FILES',
filename => file_name,
open_mode => 'wb',
max_linesize => 32767);
emp_no := emp_no + 1;
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => v_lob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT_RAW (
file => v_out_file,
buffer => v_buffer,
autoflush => true);
UTL_FILE.FFLUSH(file => v_out_file);
END LOOP;
UTL_FILE.FFLUSH(file => v_out_file);
UTL_FILE.FCLOSE(v_out_file);
exit when c1%notfound;
end loop;
END;
But I get the below error.
Error report:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_LOB", line 863
ORA-06512: at line 61
01403. 00000 - "no data found"
*Cause:
*Action:
Can someone help me to resolve this error.
The table has data. And from what I found out, error is coming from DBMS_LOB.READ function.I am using oracle 11g.
Thanks in advance.