Question: How do I
store a Microsoft Word document in Oracle table? I
need to upload a Word document into an Oracle table and I
need to know the solution.
Answer: Oracle supports two ways
to store documents in an Oracle table:
- Internal LOBs (inline document):
The LOB is stored inside of the database table
in a way that optimizes performance and supplies an
efficient access environment, taking advantage of Oracle
Database security and reliability features.
- External LOBS (BFILE locator): The
LOBs remain stored outside of Oracle in an external file
location, as in an operating system flat file. The
database itself holds an internal reference to the
external file(s), enabling access to each external
file's data via SQL (data type BFILE).
The Oracle database has several methods for loading a
MS-Word doc file into an Oracle table:
- Use Apex to store the Word doc - Storing a word
document into an Oracle table
is easy if you use APEX. This uses the
wwv_flow_files Apex utility to make it simple to store
documents and images into Oracle table columns. See my notes
storing documents in Apex.
- Store the word document on the server and use BFILE - You can also leave the
Word documentss in a flat file, and use the
to map the document into Oracle using the the dbms_lob.loadfromfile
utility. The advantage is that you
don't have to actually load the Word document into an Oracle tablespace, but
the disadvantage is that the Word document is outside of Oracle, and it cannot
be managed (backed-up, kept consistent) is if the Word doc resided inside
an Oracle table.
- Store the Word document inside the Oracle table - The third
option is to use a PL/SQL procedure to store the PDF directly into
an Oracle table, and you use the same dbms_lob.loadfromfile utility
Also see my notes on storing a
PDF file in an Oracle table and
storing photographs in Oracle tables.
Loading a BFILE reference to a Word document into an Oracle
Here is a simple example of storing a reference to a Word
document in an Oracle table. Note that the MS Word doc
stays in /tmp/mydoc.doc, and only the reference to the Word
document is loaded into the Oracle table:
directory my_dir AS '/tmp';
(1, BFILENAME('my_dir', 'mydoc.doc'));
Loading a Word document into an
Note that you are better off by actually loading the
MS-Word document into a CLOB column of an Oracle table using
the dbms_lob package. This way, the MS Word document
can be managed and controlled by Oracle. See my notes
here on using
to load a document into an Oracle table.
Here is an example of physically loading a Word document
into an Oracle table. First, we create a table to hold
the MS Word document. This table has three columns, a
document number (a sequence), a MS-Word file name, and the
BLOB which holds the text of the Word document:
Next, we write a PL/SQL snippet to load the document:
create or replace procedure
p_file_name in store_files.file_name%type) as
return image into v_blob;
-- v_blob contains the initialized
a bfile locator that is associated with the physical file on
v_bfile := bfilename('src_files',
the file using dbms_lob
the file into the blob pointer
--close the file
Next, we can execute this code and load the Word document
mydoc.doc into our table
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.