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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2003