Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem with fetching data from a cursor to a blob variable

KaZsaJan 26 2011 — edited Jan 26 2011
Hi,
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 23 2011
Added on Jan 26 2011
4 comments
729 views