| |
 |
|
Storing MS-Word doc types in Oracle
Oracle Tips by Burleson Consulting |
QUESTION: Oracle has rich
multi-media content with LOB's, and I wonder how to store and search
MS-Word documents within my Oracle database.
ANSWER: The Oracle dbms_lob package or
bfile procedures allows for storing of MS-Word docs, and several
people have published instructions for storing MS Word documents
inside Oracle.
This article by James Koopman notes the DDL to create a table to
store MS Word doc types:
CREATE TABLE my_docs
(doc_id NUMBER,
bfile_loc BFILE,
doc_title VARCHAR2(255),
doc_blob BLOB DEFAULT EMPTY_BLOB());
Koopman also shows a sample load procedure for
storing MS Word docs into Oracle tables:
bfile_loc
:= BFILENAME('DOC_DIR', in_doc);
INSERT INTO my_docs (doc_id, bfile_loc, doc_title) VALUES (1,
bfile_loc, in_doc);
SELECT doc_blob INTO temp_blob FROM my_docs WHERE doc_id = in_id
FOR UPDATE;
DBMS_LOB.OPEN(bfile_loc, DBMS_LOB.LOB_READONLY);
Open the external blob object with the MS Word document for
reading.
DBMS_LOB.OPEN(temp_blob, DBMS_LOB.LOB_READWRITE); Open the
temporary blob object for reading and writing.
DBMS_LOB.LOADFROMFILE (temp_blob, bfile_loc, Bytes_to_load);
Finally, Koopman shows how to search Oracle for
MS Word documents using the utl_raw package:
Pattern :=
utl_raw.cast_to_raw(in_search);
SELECT doc_blob INTO lob_doc FROM my_docs WHERE doc_id = in_id;
DBMS_LOB.OPEN (lob_doc, DBMS_LOB.LOB_READONLY);
Position := DBMS_LOB.INSTR (lob_doc, Pattern, Offset,
Occurrence);
|
