PL/SQL File to Blob

January 12, 2015 · by altunkan · in PL/SQL

Hello,

Converting physical files into binary data type is an important requirement. In this tutorial, I am going to explain converting files into blob data type.

Basically, Oracle has two large object data types.

BLOB (Binary Large Object): Used for keeping images, binary documents such as word, excel, pdf..
CLOB (Character Large Object): Used for keeping huge strings where varchar2 is not enough.

BFILE is also a data type which points or locates physical file.

Follow these steps in order to convert file to blob data type.

Create database directory which is set as the physical file’s path.
Define BLOB and BFILE variables.
Insert empty blob into table.
Return blob data type into blob variable.
Open blob and bfile.
Load bfile into blob by using dbms_lob package.
Close all lob objects.
Commit.

Create table with following script:


create table xx_oracle_text_ornek (
    sira_id             number,
    dokuman             blob,
    kayit_tarihi        date
);


alter table hr.xx_oracle_text_ornek
 add constraint xx_oracle_text_ornek_pk
 primary key
 (sira_id);

create table xx_oracle_text_ornek (

sira_id number,

dokuman blob,

kayit_tarihi date

);

alter table hr.xx_oracle_text_ornek

add constraint xx_oracle_text_ornek_pk

primary key

(sira_id);

Create database directory object. This object is the equivalent of the physical file in machine. Please pay attention that the path you define is physically exist.


create or replace directory xx_win_anil_dir as 'c:\blog';

create or replace directory xx_win_anil_dir as 'c:\blog';

Declare BLOB and BFILE variables:


declare
    l_blob_dosya        blob;
    l_dosya             bfile := bfilename('XX_WIN_ANIL_DIR', 'a.pdf');

declare

l_blob_dosya blob;

l_dosya bfile := bfilename('XX_WIN_ANIL_DIR', 'a.pdf');

Insert empty blob into table and table column into blob variable:


begin

    insert into xx_oracle_text_ornek
    (
        sira_id,
        dokuman,
        kayit_tarihi
    )
    values
    (
        1,
        empty_blob(),
        sysdate
    ) returning dokuman into l_blob_dosya;
    
    /*select dokuman into l_blob_dosya from xx_oracle_text_ornek where sira_id = 1;*/

begin

insert into xx_oracle_text_ornek

(

sira_id,

dokuman,

kayit_tarihi

)

values

(

empty_blob(),

sysdate

) returning dokuman into l_blob_dosya;

/*select dokuman into l_blob_dosya from xx_oracle_text_ornek where sira_id = 1;*/

Open lobs in read and read-write modes.


dbms_lob.open(l_dosya, dbms_lob.lob_readonly);
dbms_lob.open(l_blob_dosya, dbms_lob.lob_readwrite);

dbms_lob.open(l_dosya, dbms_lob.lob_readonly);

dbms_lob.open(l_blob_dosya, dbms_lob.lob_readwrite);

Load binary data into blob variable from bfile by using dbms_lob.loadfromfile.


dbms_lob.loadfromfile(dest_lob      => l_blob_dosya, 
                      src_lob       => l_dosya, 
                      amount        => dbms_lob.getlength(l_dosya));

dbms_lob.loadfromfile(dest_lob => l_blob_dosya,

src_lob => l_dosya,

amount => dbms_lob.getlength(l_dosya));

Close the lobs and commit the transaction.


dbms_lob.close(l_dosya);
dbms_lob.close(l_blob_dosya);
    
commit;

dbms_lob.close(l_dosya);

dbms_lob.close(l_blob_dosya);

commit;

All script:


declare
    l_blob_dosya        blob;
    l_dosya             bfile := bfilename('XX_WIN_ANIL_DIR', 'a.pdf');
begin

    insert into xx_oracle_text_ornek
    (
        sira_id,
        dokuman,
        kayit_tarihi
    )
    values
    (
        1,
        empty_blob(),
        sysdate
    ) returning dokuman into l_blob_dosya;
    
    /*select dokuman into l_blob_dosya from xx_oracle_text_ornek where sira_id = 1;*/

    dbms_lob.open(l_dosya, dbms_lob.lob_readonly);
    dbms_lob.open(l_blob_dosya, dbms_lob.lob_readwrite);
    dbms_lob.loadfromfile(dest_lob      => l_blob_dosya, 
                          src_lob       => l_dosya, 
                          amount        => dbms_lob.getlength(l_dosya));

    dbms_lob.close(l_dosya);
    dbms_lob.close(l_blob_dosya);
    
    commit;

end;