Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: