Thursday, 19 March 2009
Using Oracle Text with Table Data
Using LIKE clause with large tables may be a serious performance problem, involving full table scans for even a very small result set. The time you need to get a result will increase tremendously. Using Oracle Text may help you overcome those issues. In this simple example in my Demo Application I am showing how to start using Oracle Text on a table. There are also many other features included with Oracle Text such as preferred terms, fuzzy searches, highlighting, and much more. All explained in Oracle Text Documentation. Try it out.
Subscribe to:
Post Comments (Atom)
27 comments:
How can I tell if I have oracle text installed?
John,
Oracle Text should be there by default. Check if there is CTXSYS User. If not, then either the database has been upgraded from an early version where it was not a default, or someone has removed it.
Denes Kubicek
Hello There...
I am using Oracle 9i and logged in by SCOTT schema is EMP, I've used your code script as it is.. but got this error message
ORA-20000 :Oracle Text Error
DRG-12607: Only CTXSYS can create Multi-Column preferences
Is your script is complete?? Please need further help.Thanks
The step by step explanation says:
"GRANT EXECUTE ON ctx_ddl TO schema_name;"
Have you done that part?
Denes Kubicek
Really thanks for your urgent Reply!
Yes have tried... and got thir error message
ORA-04042: Procedure,function,package,or package body does not exist
Again thanks and Regrads...
Hello
Now have successfully granted.
Now in my Enterprise Manager of Oracle 9i I can view object of my schema scott as
GRANTED
Priviliege | Schema | Object
EXECUTE | CTXSYS | CTX_DDL
But facing the same problem of that erroe have earlier posted to you.
Please help if you can,
Thanks and Best Regards
Hi,
The demo doen't work now. Also, you haven't mentioned how the search text is highlighted? Could you please explain that?
Thanks,
The demo works now. Someone must have deleted the indexes. Highlighting is standard apex highlighting.
Denes Kubicek
It can index a blob field or should I do differently?
Because I did it like you explain but just wanted varchar fields and not by the content of the blob field
which is my fault?
I don't understand what you are asking.
Denes Kubicek
as index a blob field?
examples: .pdf, .docx, .txt...
I need to seek within content
O.K. now I understand. See this example:
http://apex.oracle.com/pls/otn/f?p=31517:76
Denes Kubicek
Denes thanks is what I needed!
I will try and tell you!!
Hi, Denes!
There are problems with indexing versions .pdf or .docx ??
Are you using 11g database?
Denes Kubicek
Yes, Oracle 11g.
I had the same problem with 11g and I asked for a solution here:
http://forums.oracle.com/forums/message.jspa?messageID=4044126#4044126
Never had a chance to try the tips. You may give it a shot.
Denes Kubicek
is wonderful! It worked for. Pdf but not for. Docx
Anyway it's great. Thank you very much!!
O.K. What worked?
Denes Kubicek
as you say?
My question was:
Was it this part:
"I have found that, although it should not make a difference, sometimes it helps to explicitly declare the filter in your parameters when creating the index:"
?
Denes Kubicek
Yes!
that part is it works:
...FILTER TXSYS.AUTO_FILTER...
but the documents .docx not indexed
Does it index .doc files or .xls?
Denes Kubicek
- Document generated by Office Word 2007 with extension .docx are not indexed.
- Document generated by Office Word 2003 if they are indexed.
- Document generated by Office Excel 2003 with extension .xls if they are indexed but not displayed for ctx_doc.snippet.
Document generated by Office Excel 2007 with extension .xlsx are not indexed.
O.K. thanks for checking. So only .docx and .xlsx are not indexed?
Denes Kubicek
This is great. I followed your instructions and it worked beautifully. When my search term is more than one word, it doesn't work. Do you know how i can fix that?
thank you
try rebuilding the index
Post a Comment