Q

Storing .pdf files inthe database

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

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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close