3 Replies Latest reply: Jul 2, 2014 12:31 AM by Barbara Boehmer RSS

    Getting error with CATSEARCH

    Abbas85

      Hi All,

       

      I am trying to learn Oracle Text from :

      Getting Started with Oracle Text

       

      I am going very nice but I got stuck in one place, right now am trying to learn Catalog Application catagory of Oracle Text.

      For that section I have given all the required privillages to user.

       

      I have created table :

      CREATE TABLE auction(

      item_id NUMBER,

      title VARCHAR2(100),

      category_id NUMBER,

      price NUMBER,

      bid_close DATE);

       

      Table created.

       

      Populated data as well:

       

      INSERT INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, '24-OCT-2002');

      INSERT INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, '25-OCT-2002');

      INSERT INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, '26-OCT-2002');

      INSERT INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, '27-OCT-2002');

       

      Now I am trying to Create the Sub-Index to Order by Price, for that need to create an index set called auction_set and add a sub-index for the price column:

      So I am using:

       

      Call CTX_DDL.CREATE_INDEX_SET('auction_iset');

      Call CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/

       

      Index set and sub index get created.

       

      Now I am trying to Create the CTXCAT Index

      so I am using :

      CREATE INDEX auction_titlex ON AUCTION(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set auction_iset');

      ctxcat index created as well, but when I am trying to select data by using catsearch, I am getting error:

       

      SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;

       

      error:  ORA - 29902: error in executing ODCIIndexStart() routine

                ORA - 20000: Oracle Teat error:

                DRG - 10844: Index cannot execute this structured peredicate efficiently

                DRG - 10845: column PRICE is not indexed

       

       

      Thanks

        • 1. Re: Getting error with CATSEARCH
          Barbara Boehmer

          You need to remove the comment from the following line:

           

          Call CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/

           

          changing it to:

           

          Call CTX_DDL.ADD_INDEX('auction_iset','price');

           

          because the comment prevents the code from executing properly.

           

          Please see the reproduction of the problem and solution below:

           

          SCOTT@orcl12c> -- reproduction of problem:

          SCOTT@orcl12c> CREATE TABLE auction

            2    (item_id      NUMBER,

            3      title         VARCHAR2(100),

            4      category_id  NUMBER,

            5      price         NUMBER,

            6      bid_close    DATE)

            7  /

           

          Table created.

           

          SCOTT@orcl12c> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'

            2  /

           

          Session altered.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, '24-OCT-2002')

            3  INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, '25-OCT-2002')

            4  INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, '26-OCT-2002')

            5  INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, '27-OCT-2002')

            6  SELECT * FROM DUAL

            7  /

           

          4 rows created.

           

          SCOTT@orcl12c> Call CTX_DDL.CREATE_INDEX_SET('auction_iset');

           

          Call completed.

           

          SCOTT@orcl12c> Call CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/

            2  /

          Call CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/

                                                        *

          ERROR at line 1:

          ORA-00933: SQL command not properly ended

           

           

          SCOTT@orcl12c> CREATE INDEX auction_titlex ON AUCTION(title)

            2  INDEXTYPE IS CTXSYS.CTXCAT

            3  PARAMETERS ('index set auction_iset')

            4  /

           

          Index created.

           

          SCOTT@orcl12c> COLUMN title FORMAT A30

          SCOTT@orcl12c> SELECT title, price FROM auction

            2  WHERE  CATSEARCH(title, 'CAMERA', 'order by price')> 0

            3  /

          SELECT title, price FROM auction

          *

          ERROR at line 1:

          ORA-29902: error in executing ODCIIndexStart() routine

          ORA-20000: Oracle Text error:

          DRG-10844: index cannot execute this structured predicate efficiently

          DRG-10845: column PRICE is not indexed

           

           

          SCOTT@orcl12c> -- solution:

          SCOTT@orcl12c> DROP INDEX auction_titlex

            2  /

           

          Index dropped.

           

          SCOTT@orcl12c> Call CTX_DDL.ADD_INDEX('auction_iset','price');

           

          Call completed.

           

          SCOTT@orcl12c> CREATE INDEX auction_titlex ON AUCTION(title)

            2  INDEXTYPE IS CTXSYS.CTXCAT

            3  PARAMETERS ('index set auction_iset')

            4  /

           

          Index created.

           

          SCOTT@orcl12c> SELECT title, price FROM auction

            2  WHERE  CATSEARCH(title, 'CAMERA', 'order by price')> 0

            3  /

           

          TITLE                               PRICE

          ------------------------------ ----------

          PENTAX CAMERA                         200

          CANON CAMERA                          250

          OLYMPUS CAMERA                        300

          NIKON CAMERA                          400

           

          4 rows selected.

           

          SCOTT@orcl12c>

           

          Message was edited by: Barbara Boehmer (corrected reproduction of problem)

          • 2. Re: Getting error with CATSEARCH
            Abbas85

            Hi Barbara,

            Thanks for reply, but I tried with :

             

            Call CTX_DDL.ADD_INDEX('auction_iset','category_id');

            I am using pl/sql developer so I get Done for this call statment,

            after that I tried:

            SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by category_id')> 0;

             

            but still getting same error.

             

            and one more thing could you plz tell me how to drop index_set and sub indexes taht I created here. because Im trying following both ways to drop but getting error in both:

            Remove and drop.

             

            Could you plz also tell me the process to delete those type of indexes.

             

            Thanks

            • 3. Re: Getting error with CATSEARCH
              Barbara Boehmer

              Some of the syntax in the documentation is a bit odd.  For example, you don't see CALL used very often.  If you were executing from SQL*Plus, most people would use EXEC instead.  PL/SQL Developer may not accept either, but an anonymous PL/SQL block should be safe anywhere.  Also, they have entered dates without either altering the session to match the date format or using TO_DATE.  I have provided a script below that you can copy and paste and run instead, that includes the drop commands that you requested, all in the proper order.  If it does not run properly from PL/SQL Developer, then I suggest that you try running it from SQL*Plus.  I have included a copy and paste of a run of the code from SQL*Plus on my system after the script, so that you can see that it works for me.  If it does not work for you, then please provide a copy and paste of a complete run of the script from SQL*Plus as I did, so that we can see what you ran and exactly what went wrong where.

               

              -- script for you to copy and paste and run:

              DROP INDEX auction_titlex

              /

              BEGIN

                CTX_DDL.DROP_INDEX_SET ('auction_iset');

              END;

              /

              DROP TABLE auction

              /

              CREATE TABLE auction

                (item_id      NUMBER,

                 title        VARCHAR2(100),

                 category_id  NUMBER,

                 price        NUMBER,

                 bid_close    DATE)

              /

              INSERT ALL

              INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, TO_DATE ('24-OCT-2002', 'DD-MON-YYYY'))

              INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, TO_DATE ('25-OCT-2002', 'DD-MON-YYYY'))

              INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, TO_DATE ('26-OCT-2002', 'DD-MON-YYYY'))

              INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, TO_DATE ('27-OCT-2002', 'DD-MON-YYYY'))

              SELECT * FROM DUAL

              /

              BEGIN

                CTX_DDL.CREATE_INDEX_SET('auction_iset');

              END;

              /

              BEGIN

                CTX_DDL.ADD_INDEX('auction_iset','price');

              END;

              /

              CREATE INDEX auction_titlex ON AUCTION(title)

              INDEXTYPE IS CTXSYS.CTXCAT

              PARAMETERS ('index set auction_iset')

              /

              COLUMN title FORMAT A30

              SELECT title, price FROM auction

              WHERE  CATSEARCH(title, 'CAMERA', 'order by price')> 0

              /

               

              -- execution of script above on my system:

              SCOTT@orcl12c> DROP INDEX auction_titlex

                2  /

               

              Index dropped.

               

              SCOTT@orcl12c> BEGIN

                2    CTX_DDL.DROP_INDEX_SET ('auction_iset');

                3  END;

                4  /

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> DROP TABLE auction

                2  /

               

              Table dropped.

               

              SCOTT@orcl12c> CREATE TABLE auction

                2    (item_id      NUMBER,

                3      title         VARCHAR2(100),

                4      category_id  NUMBER,

                5      price         NUMBER,

                6      bid_close    DATE)

                7  /

               

              Table created.

               

              SCOTT@orcl12c> INSERT ALL

                2  INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, TO_DATE ('24-OCT-2002', 'DD-MON-YYYY'))

                3  INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, TO_DATE ('25-OCT-2002', 'DD-MON-YYYY'))

                4  INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, TO_DATE ('26-OCT-2002', 'DD-MON-YYYY'))

                5  INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, TO_DATE ('27-OCT-2002', 'DD-MON-YYYY'))

                6  SELECT * FROM DUAL

                7  /

               

              4 rows created.

               

              SCOTT@orcl12c> BEGIN

                2    CTX_DDL.CREATE_INDEX_SET('auction_iset');

                3  END;

                4  /

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> BEGIN

                2    CTX_DDL.ADD_INDEX('auction_iset','price');

                3  END;

                4  /

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> CREATE INDEX auction_titlex ON AUCTION(title)

                2  INDEXTYPE IS CTXSYS.CTXCAT

                3  PARAMETERS ('index set auction_iset')

                4  /

               

              Index created.

               

              SCOTT@orcl12c> COLUMN title FORMAT A30

              SCOTT@orcl12c> SELECT title, price FROM auction

                2  WHERE  CATSEARCH(title, 'CAMERA', 'order by price')> 0

                3  /

               

              TITLE                               PRICE

              ------------------------------ ----------

              PENTAX CAMERA                         200

              CANON CAMERA                          250

              OLYMPUS CAMERA                        300

              NIKON CAMERA                          400

               

              4 rows selected.

               

              SCOTT@orcl12c>