9 Replies Latest reply: Jun 26, 2012 3:00 PM by amin_adatia RSS

    Equivalence Operator - Special Characters

    849858
      I am facing the same problem as mentioned in the thread below:

      Equivalence Operator - Oracle Text Search

      Can anybody help?

      Thanks
      -G
        • 1. Re: Equivalence Operator - Special Characters
          Barbara Boehmer
          By default, the @ sign is treated as whitespace and therefore splits the token, so "wa@ter" becomes "wa" and "ter" and the @ sign is not indexed. If you want to allow searching for the @ sign or other such characters, then you can set them as printjoins in a lexer and use that lexer in your index creation. If you want to ignore the @ sign, then you can set them as skipjoins in a lexer and use that lexer in your index creation. Please see the reproduction of problem below, followed by the two solutions mentioned.
          SCOTT@orcl_11gR2> -- test environment:
          SCOTT@orcl_11gR2> CREATE TABLE test_clob
            2    (clob_col  CLOB)
            3  /
          
          Table created.
          
          SCOTT@orcl_11gR2> INSERT ALL
            2  INTO test_clob VALUES ('AIR')
            3  INTO test_clob VALUES ('WATER')
            4  INTO test_clob VALUES ('WA@TER')
            5  INTO test_clob VALUES ('FIRE')
            6  SELECT * FROM DUAL
            7  /
          
          4 rows created.
          
          SCOTT@orcl_11gR2> -- reproduction of problem:
          SCOTT@orcl_11gR2> CREATE INDEX test_idx ON test_clob (clob_col)
            2  INDEXTYPE IS CTXSYS.CONTEXT
            3  /
          
          Index created.
          
          SCOTT@orcl_11gR2> SELECT token_text FROM dr$test_idx$i
            2  /
          
          TOKEN_TEXT
          ----------------------------------------------------------------
          AIR
          FIRE
          TER
          WA
          WATER
          
          5 rows selected.
          
          SCOTT@orcl_11gR2> COLUMN clob_col FORMAT A30
          SCOTT@orcl_11gR2> SELECT SCORE (1), CLOB_COL
            2  FROM   TEST_CLOB
            3  WHERE  CONTAINS (CLOB_COL, '({AIR}={WA@TER})', 1) > 0
            4  /
          SELECT SCORE (1), CLOB_COL
          *
          ERROR at line 1:
          ORA-29902: error in executing ODCIIndexStart() routine
          ORA-20000: Oracle Text error:
          DRG-50921: EQUIV operand not a word or another EQUIV expression
          
          
          SCOTT@orcl_11gR2> -- use printjoins to allowing searching for special characters:
          SCOTT@orcl_11gR2> DROP INDEX test_idx
            2  /
          
          Index dropped.
          
          SCOTT@orcl_11gR2> BEGIN
            2    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
            3    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '@');
            4  END;
            5  /
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> CREATE INDEX test_idx ON test_clob (clob_col)
            2  INDEXTYPE IS CTXSYS.CONTEXT
            3  PARAMETERS ('LEXER test_lex')
            4  /
          
          Index created.
          
          SCOTT@orcl_11gR2> SELECT token_text FROM dr$test_idx$i
            2  /
          
          TOKEN_TEXT
          ----------------------------------------------------------------
          AIR
          FIRE
          WA@TER
          WATER
          
          4 rows selected.
          
          SCOTT@orcl_11gR2> SELECT SCORE (1), CLOB_COL
            2  FROM   TEST_CLOB
            3  WHERE  CONTAINS (CLOB_COL, '({AIR}={WA@TER})', 1) > 0
            4  /
          
            SCORE(1) CLOB_COL
          ---------- ------------------------------
                   5 AIR
                   5 WA@TER
          
          2 rows selected.
          
          SCOTT@orcl_11gR2> -- use skipjoins to ignore special characters:
          SCOTT@orcl_11gR2> DROP INDEX test_idx
            2  /
          
          Index dropped.
          
          SCOTT@orcl_11gR2> BEGIN
            2    CTX_DDL.DROP_PREFERENCE ('test_lex');
            3    CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
            4    CTX_DDL.SET_ATTRIBUTE ('test_lex', 'SKIPJOINS', '@');
            5  END;
            6  /
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> CREATE INDEX test_idx ON test_clob (clob_col)
            2  INDEXTYPE IS CTXSYS.CONTEXT
            3  PARAMETERS ('LEXER test_lex')
            4  /
          
          Index created.
          
          SCOTT@orcl_11gR2> SELECT token_text FROM dr$test_idx$i
            2  /
          
          TOKEN_TEXT
          ----------------------------------------------------------------
          AIR
          FIRE
          WATER
          
          3 rows selected.
          
          SCOTT@orcl_11gR2> SELECT SCORE (1), CLOB_COL
            2  FROM   TEST_CLOB
            3  WHERE  CONTAINS (CLOB_COL, '({AIR}={WA@TER})', 1) > 0
            4  /
          
            SCORE(1) CLOB_COL
          ---------- ------------------------------
                   4 AIR
                   4 WATER
                   4 WA@TER
          
          3 rows selected.
          
          SCOTT@orcl_11gR2> 
          • 2. Re: Equivalence Operator - Special Characters
            849858
            Thanks Barbara! That helps!

            My real question was that how can i escape the special characters. According to Oracle documentation we can use {} for escaping a char sequence. But i am not able to get it work for = operator.
            e.g

            (?LANDING={LA/NDG}=LDG=LNDNG)

            Thanks
            -G

            Edited by: user13733193 on Jul 27, 2011 2:54 PM
            • 3. Re: Equivalence Operator - Special Characters
              Barbara Boehmer
              That is how you are supposed to be able to escape special characters and it works in most situations. It should not produce an error even without the printjoins or skipjoins, but it does. I call that a bug, but I don't know what Oracle's position on it is. What I provided are just workarounds that should work for any of the special characters.
              • 4. Re: Equivalence Operator - Special Characters
                849858
                It actually worked as:

                (?LANDING=LA{}NDG=LDG=LNDNG)

                Marking question as unanswered :) sorry.
                • 5. Re: Equivalence Operator - Special Characters
                  849858
                  I want to use something like:

                  ?(PASSENGER CONTROL UNIT) = PCU

                  but it doesn't work and throws an exception. But if I use

                  ?(PASSENGER CONTROL UNIT) | PCU

                  it works.

                  Any thoughts on how to use EQUIValence operator in case your search word is a string of words?

                  Edited by: user13733193 on Jul 28, 2011 10:48 AM
                  • 6. Re: Equivalence Operator - Special Characters
                    849858
                    still having a problem in using EQUIValence operator.
                    • 7. Re: Equivalence Operator - Special Characters
                      Barbara Boehmer
                      The online documentation specifically states that the equivalence operator is for providing substitutions for a single term or word, not a phrase.

                      http://download.oracle.com/docs/cd/E11882_01/text.112/e16593/cqoper.htm#CCREF0306
                      • 8. Re: Equivalence Operator - Special Characters
                        Barbara Boehmer
                        When you enclose the slash in curley brackets {} it separates the token at that point and indexes the two parts separately. It would be better to set the / as a printjoin or skipjoin. To set multiple characters as printjoins, just include them like so:

                        ctx_ddl.set_attribute ('your_lexer_name', 'printjoins', '*/');
                        • 9. Re: Equivalence Operator - Special Characters
                          amin_adatia
                          just an FYI ... printjoin does not really apply if the LEXER is anything other than BASIC.