Storing .pdf files inthe database

I have scanned images in .pdf format. How can I insert them into the database?

    Requires Free Membership to View

    Login

In order to store any binary type file (.pdf, .xls, .avi, .jpg) you will either use a BLOB or BFILE datatype. If you choose BLOB, the PDFs would actually be stored in the database. If you choose BFILE, only a pointer to the file on the OS would be stored in the database. Which you choose will depend on what you plan to do with the files, what kind of apps and users need to get to them, etc.

Here's a quick example of how to take any external file (PDF or any other) and store it into a BLOB column. 

create table demo
( id        int primary key,
  theBlob   blob
)
/

create or replace directory MY_FILES as '/export/home/public_files'; <- this is where the files you want to load are

create or replace procedure load_a_file( p_file in varchar2 )
as
    l_blob  blob;
    l_bfile bfile;
begin
    insert into demo values ( 1, empty_blob() ) 
    returning theBlob into l_blob;
    l_bfile := bfilename( 'MY_FILES', p_file );
    dbms_lob.fileopen( l_bfile );
    dbms_lob.loadfromfile( l_blob, l_bfile,
                           dbms_lob.getlength( l_bfile ) );
    dbms_lob.fileclose( l_bfile );
end;
/
This would give you a procedure to call to load a file into the database. It also shows you how to create a BFILE as it created one temporarily to load the file. I could have just inserted l_bfile instead of writing l_blob if I wanted the files "external" (loaded as a BFILE type instead of a BLOB) from the database.

I would also suggest looking at interMedia and its capabilities as well. It can add some interesting features and give you useful information about your images (such as type, size and so on). See http://www.oracle.com/intermedia/ for more info on that.

For More Information


This was first published in February 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top