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.



on

27 comments:

  1. Anonymous19 March 2009 at 19:40

    How can I tell if I have oracle text installed?

    ReplyDelete
  2. Denes Kubicek19 March 2009 at 22:47

    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

    ReplyDelete
  3. Saima12 August 2009 at 16:48

    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

    ReplyDelete
  4. Denes Kubicek12 August 2009 at 16:59

    The step by step explanation says:

    "GRANT EXECUTE ON ctx_ddl TO schema_name;"

    Have you done that part?

    Denes Kubicek

    ReplyDelete
  5. Saima12 August 2009 at 19:54

    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...

    ReplyDelete
  6. Saima13 August 2009 at 16:04

    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

    ReplyDelete
  7. Sruthi8 October 2009 at 13:42

    Hi,

    The demo doen't work now. Also, you haven't mentioned how the search text is highlighted? Could you please explain that?

    Thanks,

    ReplyDelete
  8. Denes Kubicek8 October 2009 at 14:03

    The demo works now. Someone must have deleted the indexes. Highlighting is standard apex highlighting.

    Denes Kubicek

    ReplyDelete
  9. Unknown23 March 2010 at 20:32

    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?

    ReplyDelete
  10. Denes Kubicek23 March 2010 at 20:46

    I don't understand what you are asking.

    Denes Kubicek

    ReplyDelete
  11. Unknown23 March 2010 at 21:02

    as index a blob field?

    examples: .pdf, .docx, .txt...
    I need to seek within content

    ReplyDelete
  12. Denes Kubicek23 March 2010 at 21:04

    O.K. now I understand. See this example:

    http://apex.oracle.com/pls/otn/f?p=31517:76

    Denes Kubicek

    ReplyDelete
  13. Unknown25 March 2010 at 12:36

    Denes thanks is what I needed!
    I will try and tell you!!

    ReplyDelete
  14. Unknown25 March 2010 at 15:21

    Hi, Denes!
    There are problems with indexing versions .pdf or .docx ??

    ReplyDelete
  15. Denes Kubicek25 March 2010 at 15:23

    Are you using 11g database?

    Denes Kubicek

    ReplyDelete
  16. Unknown25 March 2010 at 15:42

    Yes, Oracle 11g.

    ReplyDelete
  17. Denes Kubicek25 March 2010 at 16:01

    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

    ReplyDelete
  18. Unknown25 March 2010 at 16:33

    is wonderful! It worked for. Pdf but not for. Docx
    Anyway it's great. Thank you very much!!

    ReplyDelete
  19. Denes Kubicek25 March 2010 at 16:35

    O.K. What worked?

    Denes Kubicek

    ReplyDelete
  20. Unknown25 March 2010 at 16:46

    as you say?

    ReplyDelete
  21. Denes Kubicek25 March 2010 at 16:51

    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

    ReplyDelete
  22. Unknown25 March 2010 at 16:58

    Yes!
    that part is it works:
    ...FILTER TXSYS.AUTO_FILTER...



    but the documents .docx not indexed

    ReplyDelete
  23. Denes Kubicek25 March 2010 at 16:59

    Does it index .doc files or .xls?

    Denes Kubicek

    ReplyDelete
  24. Unknown25 March 2010 at 19:02

    - 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.

    ReplyDelete
  25. Denes Kubicek25 March 2010 at 22:21

    O.K. thanks for checking. So only .docx and .xlsx are not indexed?

    Denes Kubicek

    ReplyDelete
  26. abulfeeed14 January 2017 at 21:18

    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

    ReplyDelete
  27. Denes Kubicek20 January 2017 at 13:21

    try rebuilding the index

    ReplyDelete