Browse DevX
Sign up for e-mail newsletters from DevX


Create a Robust Document Management System with Oracle Text : Page 6

Oracle Text has a powerful text-search engine that allows users to search and manage documents and URLs as quickly and easily as any other data. Learn to use these features to deliver a robust content management search engine.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Step 5: Ad hoc Query
Suppose you had some data sitting in another table, schema, or even on a different database. You could still harness the power of the Oracle Text index you previously defined to perform searches on this disconnected data source. The policy-based services of "ctx_doc.policy_filter" allow text processing of ad hoc data. Notice in the example below that the MARKETING_DATA table does not have an Oracle Text index set on it. You pull the "text" column from this table and use your previously defined "idxContentMgmtBinary" index to filter the data:

declare output clob; input blob; vfile_name varchar2(255); NUMBER_FOUND NUMBER; begin select text, file_name into input, vfile_name from scott.marketing_data where marketing_data_id = 1; ctx_doc.policy_filter('idxContentMgmtBinary',input,output); -- now query using the in memory CLOB data select count(1) INTO NUMBER_FOUND from dual where upper(output) like '%ORACLE%'; IF NUMBER_FOUND > 0 THEN dbms_output.put_line(vfile_name || ' contains the query string specified.'); ELSE dbms_output.put_line('No records matched your search criteria'); END IF; dbms_lob.freetemporary(output); end; /

Provided the document sitting in your MARKETING_DATA table contains the query string you specified, you will see the following output:

MarketingInfo.pdf contains the query string specified.

Blow Your Users Away
You've seen how using Oracle Text can help you deliver highly sophisticated search techniques. Using this article's examples, you showed your query results via SQL*Plus. Typically, you would present the document/URL returned by the example queries as a "clickable" link to the user. If you wanted to get fancy, you could present the documents with the query terms highlighted by using the CTX_DOC package.

For more information and instructions on utilizing Oracle Text, see the Oracle Text pages. As a next step, you could review the document management/content management needs your business users have and pilot the Oracle Text functionality with them. The business users at my company were blown away when we showed them how quickly we could put this together.

Brian Carr is a Senior Oracle DBA and an Oracle Certified Professional living in Akron, Ohio. His articles have appeared in Database Trends and Applications, JavaWorld, and SELECT Journal.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date