Oracle BFILE 操作

 

create procedure set_video(dir_alias varchar2,dept_id number) is
       filename varchar2(40);
       file_ptr BFILE;
       CURSOR emp_csr is
              select first_name from employees
              where department_id = dept_id for update;
begin
  for rec in emp_csr loop
    filename := rec.first_name || '.gif';
    file_ptr := BFILENAME(dir_alias,filename);
    
    update employees 
    set video = file_ptr
    where current of emp_csr;
    
    dbms_output.put_line('file: ' || filename ||' size: '||dbms_lob.getlength(file_ptr));
    dbms_lob.fileclose(file_ptr);
  end loop;
end;