Oracle PL/SQL - Loading data to a BLOB

Introduction

To load an image to the database.

To map a folder to you local folder C:\IO, execute the following SQL commands:

create directory IO as 'C:\IO';
grant read, write on directory IO to public;

drop table product;
create table product(
   id number,
   name VARCHAR2(2000),
   manual_cl CLOB,
   firstpage_bl BLOB,
   mastertxt_bf BFILE
);

declare
     v_file_bf  BFILE:= BFILENAME ('IO','picture.gif'); --2
     v_firstpage_bl   BLOB;
     src_offset_nr       NUMBER := 1;
     dst_offset_nr       NUMBER := 1;
begin
     update t_product                                           
        set firstpage_bl = EMPTY_BLOB()
     where id = 1;

     select firstpage_bl
        into v_firstpage_bl
        from t_product
     where id = 1;

     DBMS_LOB.fileopen (v_file_bf, DBMS_LOB.file_readonly);
     DBMS_LOB.loadblobfromfile (v_firstpage_bl,
                                v_file_bf,
                                DBMS_LOB.getlength (v_file_bf),
                                dst_offset_nr, 
                                src_offset_nr);
     DBMS_LOB.fileclose (v_file_bf);
end;
/

Oracle PL/SQL - Loading data to a BLOB

Introduction

Related Topic