Recently, I have struggled to get Oracle Text working on Linux. It was supposed to be a simple addon based on a very simple idea:<\/p>\n
+ already installed Apache 2.2, PHP 5.3, Firebird 2.0; which are working fine<\/p>\n
+ install Oracle 10g Express Edition
\n+ create a simple table to store candidate ID and document itself
\n – binary contents of Word document (.doc) or PDF document (.pdf)<\/p>\n
I had to download the RPM file for Oracle 10g XE (Western Europe charset) and install it using yum. Then, run its configuration command (\/etc\/init.d\/oracle-xe configure) to finish the setup.<\/p>\n
After connecting with sqlplus, I created the table:<\/p>\n
\r\nCREATE TABLE mytable (\r\n id NUMBER PRIMARY KEY,\r\n cvfile BLOB DEFAULT EMPTY_BLOB()\r\n);\r\n\r\nCREATE INDEX mytable_cvfile_idx ON mytable (cv_file)\r\n INDEXTYPE IS CTXSYS.CONTEXT\r\n PARAMETERS ('SYNC (ON COMMIT)')\r\n);\r\n<\/pre>\n“How can I populate BLOB column of the table?”<\/strong><\/p>\n1. You can save files in the table as usual using PHP OCI functions<\/a> (PHP documentation<\/a>).<\/p>\n2. Or you can create\/execute a custom procedure that refers to your files inside a specific directory and let it do the rest using DBMS_LOB<\/a> package.<\/p>\nThe issue I experienced was that indexing failed with errors. When I looked at view (select * from CTX_USER_INDEX_ERRORS) I saw errors such as:<\/p>\n
“DRG-11207: user filter command exited with status 1”
\n“DRG-11222: Third-party filter does not support this known document format”<\/p>\n
However, the same structure worked perfectly on a Windows server (with ifilters<\/a> installed).<\/p>\nThen, I tried another method. First, I added a CLOB column into my table, and created similar index on the new column:<\/p>\n
\r\nALTER TABLE mytable\r\nADD cvtxt CLOB DEFAULT EMPTY_CLOB();\r\n\r\nUPDATE mytable SET cvtxt = EMPTY_CLOB();-- if there are records\r\n\r\nCREATE INDEX mytable_cvtxt_idx ON mytable (cv_file)\r\n INDEXTYPE IS CTXSYS.CONTEXT\r\n PARAMETERS ('SYNC (ON COMMIT)')\r\n);\r\n<\/pre>\nI had to populate the new column with text. I thought while I was saving the files in the database, I might as well extract text from Word documents and PDF documents and save the text in the CLOB column.<\/p>\n
Here comes the new challenge!<\/p>\n
Thanks to the open source world, we can find free solutions:<\/p>\n
+ Antiword<\/a> MS-Word reader (“.. converts the documents from Word 2, 6, 7, 97, 2000, 2002, and 2003 to text, Postscript, and XML\/DocBook”)<\/p>\nInstallation is easy:<\/p>\n
\r\nyum install antiword\r\n<\/pre>\n