Search through MS Word documents in Oracle-db?

[efrijters]

Hello guys,

I have a small problem. I want to upload MS Word documents into an Oracle db. Users must be able to search for words in these documents and if a certain document is found, the user must be able to download it.

I guess BLOB's are good to up/download the doc's, but I don't know how to search through these documents.

Which techniques can I use for this? Does anyone have an example? I looked at AskTom.com, but his links are all dead

Thanks in advance,
Erik

[DaPi]

Hi Erik, I very much suspect that you can't simply search a Word doc stored as a BLOB (dump a Word doc and you will see why). I think you will need to extract the text and store that in a separate column. I'm sure I've seen tools on the web for this kind of conversion. (We use http://neevia.com for Word to pdf conversion - not sure if you can get to plain text with that).

[efrijters]

Thanks DaPi! A third party tool is not applicable (there's little budget)

Hmmm, so if OLE would be possible through PL/SQL, I could read the text and then place that in a different column and search on that text.

So my thoughts are to build a insert/update-trigger that start a stored procedure which opens the document via OLE and extracts all required texts. Finally it stores the text in a VARCHAR2-column.

Does anyone think this is possible? I'll search the web for examples, I'll keep you posted.

[efrijters]

I'm now downloading "OLE DB Provider" for my 9i release 2 db.

I think this does the trick.

[efrijters]

I'm a bit dissapointed: OLE DB Provider and OLE DB Objects are all for communications from other programs. (contact Oracle from within Visual Basic, etc)

Does anyone know if it is possible to start OLE Automation from within oracle? If it is not, I must create a different program (in a different language), but I want to solve this problem in Oracle...

[onlysimon]

intermedia has an MS word import filter (doesn't it?)

[efrijters]

OLE is no good, COM Automation is the promissed land. I'm now reinstalling my examples so I can test something...

OnlySimon, I'll look into that aswell !!

Progress:
- executed '[oracle_home]\com\comwrap.sql';
- executed '[oracle_home]\com\demos\wordsol.sql';
- executed '[oracle_home]\com\demos\worddem.sql';
- worddem.sql halts on a ORA-28575 error. (reading metalink for solution)

Followed Note 70638.1 and created a separate listener for extproc. "TNSPING extproc" works fine now.

- executed '[oracle_home]\com\demos\worddem.sql' and it creates a MS Word-document! Let the party begin...!!

COM Automation & Oracle is mega-cool!

[efrijters]

Hey guys,

(Maybe this question belongs in the development-section, but this still belongs in this thread).

Since the MS Word example works, I've been looking for a way to create a function that reads the text from within a document.

FUNCTION InsertText(textstr varchar2) return binary_integer IS
BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(textstr,'BSTR');
    i:=ORDCOM.Invoke(applicationToken, 'Insert', 1, DummyToken);
    IF (i!=0) THEN
    ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
    END IF;
    return i;
END InsertText; 

I have a question (given the write-example above):
The "Invoke" function calls a specific function in MS Word: 'Insert'. Where can I find a list of available functions and their parameters of MS Word?

Thanks in advance,
Erik

[efrijters]

Progress:

It must be something like:

HRESULT:=ORDCOM.GetProperty(applicationToken, 'Text', 0, chartoken); 

, but I keep on getting the error "-2147352567 wrong number of parameters"... :(

I looked at AskTom + metalink + oracle documentation + otn + technet + installed com examples + google.com, but nobody does a read from a document...

Am I missing something? (I don't want to use forms or extra tools, just PL/SQL!)

[onlysimon]

if you hit a brick wall, look at using intermedia with the INSO_FILTER.