Saturday, February 17, 2007

Store MS Word Documents in Oracle

Follow the following steps..... :)

1) Create a table
CREATE TABLE my_docs (
name VARCHAR2(200) NOT NULL,
doc BLOB NOT NULL
);


2)Create a directory object which stores all your doc
CREATE OR REPLACE DIRECTORY documents AS 'C:\work';

3)Create a procedure
CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN my_docs.name%TYPE)
AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO my_docs (name, doc)
VALUES ( p_file_name, empty_blob())
RETURN doc INTO v_blob;
v_bfile := BFILENAME('documents', p_file_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);
COMMIT;
END;
/

4)Execute the db procedure...

[This is sgalaxy solution form Oracle forum]

2 comments:

Anonymous said...

great example. But after loading how do I search , retrieve or update doc.

Anonymous said...

how do I search , retrieve or update doc.