Hi,
I want to export blob content via PL/SQL and I have a code about that. The problem is that when I execute this code, I can export all files to system file but only first pic has display or image. the other ones are empty. What is the problem with my code I can not see now.
Regards,
Gunce
create or replace procedure export_files is
cursor c_export is select emplid,acad_career,stdnt_car_nbr from ps_stu_info where acad_prog='AAA';
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;
v_emplid number;
v_stdnt_car_nbr number;
v_acad_career varchar(4 char);
BEGIN
-- Get LOB locator
for f1 in c_Export loop
begin
select emplid,acad_career,stdnt_car_nbr,employee_photo
into v_emplid,v_acad_career,v_stdnt_car_nbr,l_blob
from ps_stu_info where emplid=f1.emplid and f1.acad_career=acad_career and f1.stdnt_car_nbr=stdnt_car_nbr;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('BANKA',v_emplid||'.jpg','w', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
end;
end loop;
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
raise;
END;
/