Loading Word Doc In Oracle Database

Loading Word Doc In Oracle Database:

1). Create a table

For Example:

CREATE TABLE EMP_INFORMATION

(

EMP_ID NUMBER(6),

EMP_NAME VARCHAR2(50 BYTE),

EMP_RESUME BLOB

);

2). Create a Directory

For Example:

CREATE OR REPLACE DIRECTORY

RND_DIR AS 'E:\OraDir';

3). Create a procedure

For Example:

CREATE OR REPLACE PROCEDURE EMPINFO (

p_emp_id EMP_INFORMATION.EMP_ID%type,

p_emp_name EMP_INFORMATION.emp_name%type,

p_emp_resume varchar2

)

IS

f_lob bfile;

b_lob blob;

directory varchar2(30) :='RND_DIR' ;

filename varchar2(30) :=NULL;

BEGIN

filename := p_emp_resume;

INSERT INTO EMP_INFORMATION

(EMP_ID,EMP_NAME,EMP_RESUME)

VALUES (p_emp_id ,p_emp_name,empty_blob() )

RETURN EMP_RESUME into b_lob;

f_lob := BFILENAME(directory,filename);

dbms_lob.fileopen(f_lob, dbms_lob.file_readonly );

dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob) );

dbms_lob.fileclose(f_lob);

commit;

end;

/

 

4). Now Copy and Paste your doc file at the directory path.

5). And execute the procedure

DECLARE

P_EMP_ID NUMBER;

P_EMP_NAME VARCHAR2 (200);

P_EMP_RESUME VARCHAR2 (200);

BEGIN

P_EMP_ID:= NULL; --Number

P_EMP_NAME:= NULL; --This must be in Varchar type

P_EMP_RESUME:= NULL; --This Must be filename.doc type

EMPINFO (P_EMP_ID, P_EMP_NAME, P_EMP_RESUME);

COMMIT;

END;

6). Now a value is inserted in table.

If You are using TOAD then u can see the stored value.

By

Shrikesh K. Pandey