Hi:

I have a Blob type field in an oracle 9i databse which stores employee pictures. Is there a way to create a job that copies these pictures into a folder?

name of field: emp_pic
name of db: employees
path of folder to copy to: G:\employee\pic

please help.

Thanks

Hi,

Here is a proc to put a clob in a file, try it with blob.

create or REPLACE procedure clob_to_file( p_dir in varchar2,p_file in varchar2,p_clob in clob )
as
l_output utl_file.file_type;
l_amt number default 32000;
l_offset number default 1;
l_length number default nvl(dbms_lob.getlength(p_clob),0);
 BEGIN
 l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
 while ( l_offset < l_length ) loop
   utl_file.put(l_output,
   dbms_lob.substr(p_clob,l_amt,l_offset) );
   utl_file.fflush(l_output);
   l_offset := l_offset + l_amt;
 end loop;
 utl_file.new_line(l_output);
 utl_file.fclose(l_output);
end;
/

You need a directory to xecute this proc.

Thanks to T. Kyte for the proc http://asktom.oracle.com/

By