1 2 3 Previous Next 33 Replies Latest reply: Jul 11, 2007 4:59 PM by Barbara Boehmer Go to original post RSS
      • 15. Re: Loading an Oracle Thesaurus:
        429085
        Testing completed.
        This is the bees knees!
        Thank you!
        • 16. Re: Loading an Oracle Thesaurus:
          429085
          Hmmm the ref cursor does not like the RETURN LTRIM (v_correct_spell, ' ');
          • 17. Re: Loading an Oracle Thesaurus:
            429085
            Please ignore my last comment.
            • 18. Re: Loading an Oracle Thesaurus:
              429085
              Just found a tad of a problem with this function...

              The following searches return errors:

              /*Single Wildcard*/
              SQL> select sfd_search.fn_prod_search('%') from dual;
              select sfd_search.fn_prod_search('%') from dual
              *
              ERROR at line 1:
              ORA-29902: error in executing ODCIIndexStart() routine
              ORA-20000: Oracle Text error:
              DRG-51030: wildcard query expansion resulted in too many terms
              ORA-06512: at "CATALOG1.SFD_SEARCH", line 42

              /*Space*/
              SQL> select sfd_search.fn_prod_search(' ') from dual
              2 /
              select sfd_search.fn_prod_search(' ') from dual
              *
              ERROR at line 1:
              ORA-29902: error in executing ODCIIndexStart() routine
              ORA-20000: Oracle Text error:
              DRG-51030: wildcard query expansion resulted in too many terms
              ORA-06512: at "CATALOG1.SFD_SEARCH", line 42

              /*2 spaces together*/
              SQL> select sfd_search.fn_prod_search('brass vent') from dual;
              select sfd_search.fn_prod_search('bras vent') from dual
              *
              ERROR at line 1:
              ORA-29902: error in executing ODCIIndexStart() routine
              ORA-20000: Oracle Text error:
              DRG-51030: wildcard query expansion resulted in too many terms
              ORA-06512: at "CATALOG1.SFD_SEARCH", line 42

              /*2 Wildcards*/
              SQL> select sfd_search.fn_prod_search('%%') from dual;
              select sfd_search.fn_prod_search('%%') from dual
              *
              ERROR at line 1:
              ORA-29902: error in executing ODCIIndexStart() routine
              ORA-20000: Oracle Text error:
              DRG-51030: wildcard query expansion resulted in too many terms
              ORA-06512: at "CATALOG1.SFD_SEARCH", line 42

              Is there an easy way to deal with these within the function?
              • 19. Re: Loading an Oracle Thesaurus:
                429085
                Seems the CONTAINS clause does not like spaces,special characters,
                • 20. Re: Loading an Oracle Thesaurus:
                  Barbara Boehmer
                  You need to post the actual function that you are using and the actual query that you are running. The error that you posted comes from a contains query, but the function that I provided just corrected the spelling and did not use contains, so I can only guess that you have either modified the function or are calling it from within another function. Last I heard you were returning your result set in a ref cursor, but you have tested by selecting from dual, which cannot return a ref cursor, so I can only guess that you are just returning one row or using a pipelined function or something instead. I did warn you about the spaces. Any extra spaces and special characters can be easily removed using the Oracle built-in replace function. You will need to decide what results you want to return in the event that a search string contains nothing but spaces and wild cards. I have provided an example below, where one function determines what to return in that event and the other function removes the extra spaces and characters from otherwise legitimate searches. If this does not help, then please post the details of what you are currently doing and what results you want.
                  scott@ORA92> CREATE TABLE scs_thesaurus
                    2    (search_synonym VARCHAR2(30),
                    3       term         VARCHAR2(30))
                    4  /

                  Table created.

                  scott@ORA92> INSERT ALL
                    2  INTO scs_thesaurus VALUES ('piant',  'paint')
                    3  INTO scs_thesaurus VALUES ('samsing',  'samsung')
                    4  INTO scs_thesaurus VALUES ('samsong',  'samsung')
                    5  INTO scs_thesaurus VALUES ('samsnug',  'samsung')
                    6  SELECT * FROM DUAL
                    7  /

                  4 rows created.

                  scott@ORA92> CREATE INDEX scs_thes_search_syn_idx
                    2  ON scs_thesaurus (search_synonym)
                    3  /

                  Index created.

                  scott@ORA92> CREATE TABLE test_tab (test_col VARCHAR2(30))
                    2  /

                  Table created.

                  scott@ORA92> INSERT ALL
                    2  INTO test_tab VALUES ('paint')
                    3  INTO test_tab VALUES ('paints')
                    4  INTO test_tab VALUES ('painter')
                    5  INTO test_tab VALUES ('painters')
                    6  INTO test_tab VALUES ('painting')
                    7  INTO test_tab VALUES ('paintings')
                    8  INTO test_tab VALUES ('samsung')
                    9  INTO test_tab VALUES ('samsung painting')
                  10  INTO test_tab VALUES ('kittycat')
                  11  SELECT * FROM DUAL
                  12  /

                  9 rows created.

                  scott@ORA92> CREATE INDEX test_idx ON test_tab (test_col)
                    2  INDEXTYPE IS CTXSYS.CONTEXT
                    3  /

                  Index created.

                  scott@ORA92> CREATE OR REPLACE PACKAGE sfd_search
                    2  AS
                    3    FUNCTION correct_spelling
                    4        (p_search_terms IN VARCHAR2)
                    5         RETURN         VARCHAR2;
                    6       FUNCTION fn_prod_search
                    7        (p_search_terms IN VARCHAR2)
                    8        RETURN          SYS_REFCURSOR;
                    9  END sfd_search;
                  10  /

                  Package created.

                  scott@ORA92> SHOW ERRORS
                  No errors.
                  scott@ORA92> CREATE OR REPLACE PACKAGE BODY sfd_search
                    2  AS
                    3    FUNCTION correct_spelling
                    4        (p_search_terms IN VARCHAR2)
                    5         RETURN         VARCHAR2
                    6    IS
                    7        v_search_terms        VARCHAR2(2000) := p_search_terms;
                    8        v_correct_spell       VARCHAR2(2000);
                    9    BEGIN
                  10        v_search_terms := LTRIM (RTRIM (p_search_terms)) || ' ';
                  11        WHILE INSTR (v_search_terms, '  ') > 0 OR INSTR (v_search_terms, '%') > 0
                  12        LOOP
                  13          v_search_terms := REPLACE (REPLACE (v_search_terms, '  ', ' '), '%', '');
                  14        END LOOP;
                  15        FOR i IN 1 .. LENGTH (v_search_terms) - LENGTH (REPLACE (v_search_terms, ' ', ''))
                  16        LOOP
                  17          FOR r IN
                  18            (SELECT term
                  19             FROM   scs_thesaurus
                  20             WHERE  search_synonym =
                  21                 SUBSTR (v_search_terms, 1, INSTR (v_search_terms, ' ') - 1)
                  22             UNION ALL
                  23             SELECT SUBSTR (v_search_terms, 1, INSTR (v_search_terms, ' ') - 1) AS term
                  24             FROM   DUAL
                  25             WHERE  NOT EXISTS
                  26                 (SELECT *
                  27                  FROM   scs_thesaurus
                  28                  WHERE  search_synonym =
                  29                  SUBSTR (v_search_terms, 1, INSTR (v_search_terms, ' ') - 1)))
                  30          LOOP
                  31            v_correct_spell := v_correct_spell || ' ' || r.term;
                  32            v_search_terms := SUBSTR (v_search_terms, INSTR (v_search_terms, ' ') + 1);
                  33          END LOOP;
                  34        END LOOP;
                  35        RETURN LTRIM (v_correct_spell, ' ');
                  36    END correct_spelling;
                  37    FUNCTION fn_prod_search
                  38        (p_search_terms IN VARCHAR2)
                  39        RETURN          SYS_REFCURSOR
                  40    IS
                  41        v_refcursor         SYS_REFCURSOR;
                  42    BEGIN
                  43        IF REPLACE (REPLACE (p_search_terms, ' ', ''), '%', '') IS NULL THEN
                  44          OPEN v_refcursor FOR SELECT NULL FROM DUAL WHERE 1 = 2;
                  45        ELSE
                  46          OPEN v_refcursor FOR
                  47          SELECT * FROM test_tab
                  48          WHERE  CONTAINS
                  49              (test_col, '$' ||
                  50                REPLACE (correct_spelling (p_search_terms), ' ', '% AND $')
                  51                || '%') > 0;
                  52        END IF;
                  53        RETURN v_refcursor;
                  54    END fn_prod_search;
                  55  END sfd_search;
                  56  /

                  Package body created.

                  scott@ORA92> SHOW ERRORS
                  No errors.
                  scott@ORA92> VARIABLE g_refcur REFCURSOR
                  scott@ORA92> VARIABLE g_search_terms VARCHAR2(60)
                  scott@ORA92> --Single Wildcard
                  scott@ORA92> EXECUTE :g_search_terms := '%'

                  PL/SQL procedure successfully completed.

                  scott@ORA92> EXECUTE :g_refcur := sfd_search.fn_prod_search (:g_search_terms)

                  PL/SQL procedure successfully completed.

                  scott@ORA92> PRINT g_refcur

                  no rows selected

                  scott@ORA92> --Space
                  scott@ORA92> EXECUTE :g_search_terms := ' '

                  PL/SQL procedure successfully completed.

                  scott@ORA92> EXECUTE :g_refcur := sfd_search.fn_prod_search (:g_search_terms)

                  PL/SQL procedure successfully completed.

                  scott@ORA92> PRINT g_refcur

                  no rows selected

                  scott@ORA92> --2 spaces together
                  scott@ORA92> EXECUTE :g_search_terms := 'brass     vent'

                  PL/SQL procedure successfully completed.

                  scott@ORA92> EXECUTE :g_refcur := sfd_search.fn_prod_search (:g_search_terms)

                  PL/SQL procedure successfully completed.

                  scott@ORA92> PRINT g_refcur

                  no rows selected

                  scott@ORA92> --2 Wildcards
                  scott@ORA92> EXECUTE :g_search_terms := '%%'

                  PL/SQL procedure successfully completed.

                  scott@ORA92> EXECUTE :g_refcur := sfd_search.fn_prod_search (:g_search_terms)

                  PL/SQL procedure successfully completed.

                  scott@ORA92> PRINT g_refcur

                  no rows selected

                  scott@ORA92> -- legitimate search with 2 spaces together and 2 wildcards together:
                  scott@ORA92> EXECUTE :g_search_terms := '%%samsing%%  %%piant%%'

                  PL/SQL procedure successfully completed.

                  scott@ORA92> EXECUTE :g_refcur := sfd_search.fn_prod_search (:g_search_terms)

                  PL/SQL procedure successfully completed.

                  scott@ORA92> PRINT g_refcur

                  TEST_COL
                  ------------------------------
                  samsung painting

                  scott@ORA92>
                  • 21. Re: Loading an Oracle Thesaurus:
                    507076
                    Amazing Article. Made my Life easier. Just One question.

                    When using the Function update_thes.

                    If I execute it every time a new records are added to it, will it not duplicate the data in thesaurus. I am going to test it but wanted to see if you knew about it already or any logic to update only the new records.

                    Thx
                    Naren
                    • 22. Re: Loading an Oracle Thesaurus:
                      Barbara Boehmer
                      There must be something in ctx_thes.create_relation that already checks for duplicates, because, as you can see by the demonstration below, executing it a second time does not result in duplicates.
                      scott@ORA92> 
                      scott@ORA92> CREATE TABLE your_thes_tab
                        2    (search_synonym     VARCHAR2 (30),
                        3       term          VARCHAR2 (30))
                        4  /

                      Table created.

                      scott@ORA92> INSERT INTO your_thes_tab VALUES ('word1', 'word2')
                        2  /

                      1 row created.

                      scott@ORA92> EXEC CTX_THES.CREATE_THESAURUS ('your_thes')

                      PL/SQL procedure successfully completed.

                      scott@ORA92> CREATE OR REPLACE PROCEDURE update_thes
                        2  AS
                        3  BEGIN
                        4    FOR r IN (SELECT * FROM your_thes_tab ytt)
                        5    LOOP
                        6        CTX_THES.CREATE_RELATION
                        7          ('your_thes',
                        8           r.search_synonym,
                        9           'SYN',
                      10           r.term);
                      11    END LOOP;
                      12  END update_thes;
                      13  /

                      Procedure created.

                      scott@ORA92> SHOW ERRORS
                      No errors.
                      scott@ORA92> EXEC update_thes

                      PL/SQL procedure successfully completed.

                      scott@ORA92> SELECT thp_phrase FROM CTX_THES_PHRASES WHERE THP_THESAURUS = 'YOUR_THES'
                        2  /

                      THP_PHRASE
                      ----------------------------------------------------------------------------------------------------
                      WORD1
                      WORD2

                      2 rows selected.

                      scott@ORA92> SELECT CTX_THES.SYN ('word1', 'your_thes') FROM DUAL
                        2  /

                      CTX_THES.SYN('WORD1','YOUR_THES')
                      ----------------------------------------------------------------------------------------------------
                      {WORD1}|{WORD2}

                      1 row selected.

                      scott@ORA92> EXEC update_thes

                      PL/SQL procedure successfully completed.

                      scott@ORA92> SELECT thp_phrase FROM CTX_THES_PHRASES WHERE THP_THESAURUS = 'YOUR_THES'
                        2  /

                      THP_PHRASE
                      ----------------------------------------------------------------------------------------------------
                      WORD1
                      WORD2

                      2 rows selected.

                      scott@ORA92> SELECT CTX_THES.SYN ('word1', 'your_thes') FROM DUAL
                        2  /

                      CTX_THES.SYN('WORD1','YOUR_THES')
                      ----------------------------------------------------------------------------------------------------
                      {WORD1}|{WORD2}

                      1 row selected.

                      scott@ORA92>
                      • 23. Re: Loading an Oracle Thesaurus:
                        Barbara Boehmer
                        The only documentation about ctx_thes.create_relation says, "If phrase does not exist in the thesaurus, it is created." That sort of implies that if phrase does exist, a duplicate is not created.
                        • 24. Re: Loading an Oracle Thesaurus:
                          507076
                          It behaved same way for me. Thanks a lot.
                          Need one more clarification, If I use your 2 examples on PAGE 1
                          Update_Thes for SYN and BT
                          Can I use both of them at the same time. I mean in the same Thesarus and still use both type of queries. A Combined solution.

                          Refering to your Code:
                          ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                          ctxsys@ORA92> CREATE OR REPLACE PROCEDURE update_thes
                          2 AS
                          3 BEGIN
                          4 FOR r IN (SELECT * FROM user1.scs_thesaurus)
                          5 LOOP
                          6      CTX_THES.CREATE_RELATION
                          7      ('sdfthes',
                          8      r.search_synonym,
                          9      'SYN',
                          10      r.term);
                          11 END LOOP;
                          12 END update_thes;
                          13 /

                          you@ORA92> SELECT * FROM test_tab
                          2 WHERE CONTAINS (test_col, 'SYN (accessory, sdfthes)') > 0
                          3 /

                          ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                          ctxsys@ORA92> CREATE OR REPLACE PROCEDURE update_thes
                          2 AS
                          3 BEGIN
                          4 FOR r IN (SELECT * FROM user1.scs_thesaurus)
                          5 LOOP
                          6      CTX_THES.CREATE_RELATION
                          7      ('sdfthes',
                          8      r.search_synonym,
                          9      'BT',
                          10      r.term);
                          11 END LOOP;
                          12 END update_thes;

                          you@ORA92> SELECT *
                          2 FROM test_tab
                          3 WHERE CONTAINS
                          4      (test_col,
                          5      '$(TT(' || REPLACE (:g_search_terms, ' ', ',sdfthes)) AND $(TT(') || ',sdfthes))')
                          6      > 0
                          7 /

                          ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

                          Thanks for your help.

                          Naren
                          • 25. Re: Loading an Oracle Thesaurus:
                            Barbara Boehmer
                            You can load both synonyms and hierarchical relationships using broader terms and/or narrower terms into the same thesaurus and run queries of each type using the same thesaurus. However, you should load your synonyms and broader terms from separate tables. You don't want to have the same terms incorrectly listed as both synonyms and broader terms.
                            • 26. Re: Loading an Oracle Thesaurus:
                              507076
                              That helps. Thanks a lot. Can you point me to where can I find info on loading XML data into a Column and do some searches with WITHIN clause. I looked around this site and couldn't find grteat help. We want to load a XML string into a Varchar2(4000) column and do TEXT Index.

                              You are a great help to this site.

                              Naren
                              • 27. Re: Loading an Oracle Thesaurus:
                                Barbara Boehmer
                                There are all kinds of ways to do things and all kinds of features available. Without knowing more details, such as where you xml data is coming from and in what format, and examples of types of searches you want to do, it is difficult to suggest what is best. Here is a very general, simple example, that demonstrates, a few features:
                                SCOTT@10gXE> CREATE TABLE your_table
                                  2    (id       NUMBER,
                                  3       xml_data  VARCHAR2 (4000))
                                  4  /

                                Table created.

                                SCOTT@10gXE> INSERT INTO your_table (id, xml_data)
                                  2  SELECT t.deptno,
                                  3           DBMS_XMLGEN.GETXML
                                  4             ('SELECT e.ename, e.job, d.dname
                                  5            FROM   emp e, dept d
                                  6            WHERE  d.deptno = e.deptno (+)
                                  7            AND    d.deptno = ' || t.deptno)
                                  8  FROM   dept t
                                  9  /

                                5 rows created.

                                SCOTT@10gXE> COMMIT
                                  2  /

                                Commit complete.

                                SCOTT@10gXE> COLUMN xml_data FORMAT A50
                                SCOTT@10gXE> SELECT * FROM your_table
                                  2  /

                                        ID XML_DATA
                                ---------- --------------------------------------------------
                                        10 <?xml version="1.0"?>
                                           <ROWSET>
                                            <ROW>
                                             <ENAME>CLARK</ENAME>
                                             <JOB>MANAGER</JOB>
                                             <DNAME>ACCOUNTING</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>KING</ENAME>
                                             <JOB>PRESIDENT</JOB>
                                             <DNAME>ACCOUNTING</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>MILLER</ENAME>
                                             <JOB>CLERK</JOB>
                                             <DNAME>ACCOUNTING</DNAME>
                                            </ROW>
                                           </ROWSET>

                                        20 <?xml version="1.0"?>
                                           <ROWSET>
                                            <ROW>
                                             <ENAME>SMITH</ENAME>
                                             <JOB>CLERK</JOB>
                                             <DNAME>RESEARCH</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>JONES</ENAME>
                                             <JOB>MANAGER</JOB>
                                             <DNAME>RESEARCH</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>SCOTT</ENAME>
                                             <JOB>ANALYST</JOB>
                                             <DNAME>RESEARCH</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>ADAMS</ENAME>
                                             <JOB>CLERK</JOB>
                                             <DNAME>RESEARCH</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>FORD</ENAME>
                                             <JOB>ANALYST</JOB>
                                             <DNAME>RESEARCH</DNAME>
                                            </ROW>
                                           </ROWSET>

                                        30 <?xml version="1.0"?>
                                           <ROWSET>
                                            <ROW>
                                             <ENAME>ALLEN</ENAME>
                                             <JOB>SALESMAN</JOB>
                                             <DNAME>SALES</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>WARD</ENAME>
                                             <JOB>SALESMAN</JOB>
                                             <DNAME>SALES</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>MARTIN</ENAME>
                                             <JOB>SALESMAN</JOB>
                                             <DNAME>SALES</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>BLAKE</ENAME>
                                             <JOB>MANAGER</JOB>
                                             <DNAME>SALES</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>TURNER</ENAME>
                                             <JOB>SALESMAN</JOB>
                                             <DNAME>SALES</DNAME>
                                            </ROW>
                                            <ROW>
                                             <ENAME>JAMES</ENAME>
                                             <JOB>CLERK</JOB>
                                             <DNAME>SALES</DNAME>
                                            </ROW>
                                           </ROWSET>

                                        40 <?xml version="1.0"?>
                                           <ROWSET>
                                            <ROW>
                                             <DNAME>OPERATIONS</DNAME>
                                            </ROW>
                                           </ROWSET>

                                        50 <?xml version="1.0"?>
                                           <ROWSET>
                                            <ROW>
                                             <DNAME>PERSONNEL</DNAME>
                                            </ROW>
                                           </ROWSET>


                                SCOTT@10gXE> CREATE INDEX your_idx ON your_table (xml_data)
                                  2  INDEXTYPE IS CTXSYS.CONTEXT
                                  3  PARAMETERS ('SECTION GROUP CTXSYS.AUTO_SECTION_GROUP')
                                  4  /

                                Index created.

                                SCOTT@10gXE> SELECT SCORE (1), t.* FROM your_table t
                                  2  WHERE  CONTAINS (t.xml_data, 'CL%', 1) > 0
                                  3  /

                                  SCORE(1)         ID XML_DATA
                                ---------- ---------- --------------------------------------------------
                                         7         10 <?xml version="1.0"?>
                                                      <ROWSET>
                                                       <ROW>
                                                        <ENAME>CLARK</ENAME>
                                                        <JOB>MANAGER</JOB>
                                                        <DNAME>ACCOUNTING</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>KING</ENAME>
                                                        <JOB>PRESIDENT</JOB>
                                                        <DNAME>ACCOUNTING</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>MILLER</ENAME>
                                                        <JOB>CLERK</JOB>
                                                        <DNAME>ACCOUNTING</DNAME>
                                                       </ROW>
                                                      </ROWSET>

                                         7         20 <?xml version="1.0"?>
                                                      <ROWSET>
                                                       <ROW>
                                                        <ENAME>SMITH</ENAME>
                                                        <JOB>CLERK</JOB>
                                                        <DNAME>RESEARCH</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>JONES</ENAME>
                                                        <JOB>MANAGER</JOB>
                                                        <DNAME>RESEARCH</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>SCOTT</ENAME>
                                                        <JOB>ANALYST</JOB>
                                                        <DNAME>RESEARCH</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>ADAMS</ENAME>
                                                        <JOB>CLERK</JOB>
                                                        <DNAME>RESEARCH</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>FORD</ENAME>
                                                        <JOB>ANALYST</JOB>
                                                        <DNAME>RESEARCH</DNAME>
                                                       </ROW>
                                                      </ROWSET>

                                         4         30 <?xml version="1.0"?>
                                                      <ROWSET>
                                                       <ROW>
                                                        <ENAME>ALLEN</ENAME>
                                                        <JOB>SALESMAN</JOB>
                                                        <DNAME>SALES</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>WARD</ENAME>
                                                        <JOB>SALESMAN</JOB>
                                                        <DNAME>SALES</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>MARTIN</ENAME>
                                                        <JOB>SALESMAN</JOB>
                                                        <DNAME>SALES</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>BLAKE</ENAME>
                                                        <JOB>MANAGER</JOB>
                                                        <DNAME>SALES</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>TURNER</ENAME>
                                                        <JOB>SALESMAN</JOB>
                                                        <DNAME>SALES</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>JAMES</ENAME>
                                                        <JOB>CLERK</JOB>
                                                        <DNAME>SALES</DNAME>
                                                       </ROW>
                                                      </ROWSET>


                                SCOTT@10gXE> SELECT SCORE (1), t.* FROM your_table t
                                  2  WHERE  CONTAINS (t.xml_data, 'CL% WITHIN ename', 1) > 0
                                  3  /

                                  SCORE(1)         ID XML_DATA
                                ---------- ---------- --------------------------------------------------
                                         4         10 <?xml version="1.0"?>
                                                      <ROWSET>
                                                       <ROW>
                                                        <ENAME>CLARK</ENAME>
                                                        <JOB>MANAGER</JOB>
                                                        <DNAME>ACCOUNTING</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>KING</ENAME>
                                                        <JOB>PRESIDENT</JOB>
                                                        <DNAME>ACCOUNTING</DNAME>
                                                       </ROW>
                                                       <ROW>
                                                        <ENAME>MILLER</ENAME>
                                                        <JOB>CLERK</JOB>
                                                        <DNAME>ACCOUNTING</DNAME>
                                                       </ROW>
                                                      </ROWSET>


                                SCOTT@10gXE>
                                • 28. Re: Loading an Oracle Thesaurus:
                                  507076
                                  Thanks. But this is my problem. I am little lost with that data loading.
                                  I have 2 tables.
                                  Desc DATA_TABLE
                                  Name Null? Type
                                  ----------------------------------------- -------- ----------------------------
                                  ACCOUNT_NAME VARCHAR2(100)
                                  MODETYPE VARCHAR2(100)
                                  SPECIAL_FEATURE_CODE VARCHAR2(100)
                                  REFERENCE_ID VARCHAR2(100)
                                  NAME_FIRST VARCHAR2(100)
                                  NAME_MIDDLE VARCHAR2(100)
                                  NAME_LAST VARCHAR2(100)
                                  YEAR VARCHAR2(100)
                                  MONTH VARCHAR2(100)
                                  DAY VARCHAR2(100)

                                  DESC PRE_ID_VELOCITY
                                  Name Null? Type
                                  ----------------------------------------- -------- ----------------------------
                                  SEVERITYTYPEFIELD NUMBER(38)
                                  MODETYPEFIELD NUMBER(38)
                                  RAWDATA VARCHAR2(4000)
                                  ACCOUNTNAME VARCHAR2(150)
                                  EVENTTIME NOT NULL TIMESTAMP(3)
                                  VELOCITYCHECKTYPEFIELD NOT NULL NUMBER(38)
                                  EVENTREFERENCEID NOT NULL NUMBER(38)
                                  WLS_TEMP NUMBER(38)

                                  Now RAWDATA column in Pre_ID_Velocity is where I want to put the XML of each row in DATA_TABLE.
                                  I did this
                                  insert into
                                  SELECT DBMS_XMLGEN.GETXML
                                  ('SELECT d.account_name,d.modetype
                                  FROM data_table d
                                  WHERE d.rowid = ' || t.rowid)
                                  FROM data_table t


                                  It gives me an error.
                                  [1]: (Error): ORA-19202: Error occurred in XML processing ORA-00904: "AAAE83AAAAAAABEAAA": invalid identifier ORA-06512: at "SYS.DBMS_XMLGEN", line 176 ORA-06512: at line 1
                                  Am I missing something here.

                                  Another Question is:
                                  When you create an Index with
                                  PARAMETERS ('SECTION GROUP CTXSYS.AUTO_SECTION_GROUP')
                                  and if you put in regular TEXT Data and XML Data into that row,
                                  I am able to query the regular text data with out WITHIN Clause.

                                  Question is, how is it indexing it. Is it Indexing with the Sections(XML Sections) or with out.
                                  If it is indexing with the SEctions, then isn't it a overhead or wrong or unncessary.
                                  I tried to look around docs but didn't find any notes related to this.

                                  Thanks for your help.
                                  • 29. Re: Loading an Oracle Thesaurus:
                                    Barbara Boehmer
                                    I have provided a partial example of data loading below. I don't know where you derive some of your column values from. I am assuming that your pre_id_velocity table is initially empty. If not then you need an update statement, instead of an insert statement. I don't quite get what you mean about the index. It allows you to either search an individual tag using within or to search across all tags without using within. That is just how it is. I don't know exactly how it does things internally. You said that you wanted to search using within and it allows you to do that. I demonstrated both types of searches in a prior response.

                                     SCOTT@10gXE> CREATE TABLE DATA_TABLE
                                      2    (ACCOUNT_NAME           VARCHAR2(100),
                                      3       MODETYPE           VARCHAR2(100),
                                      4       SPECIAL_FEATURE_CODE  VARCHAR2(100),
                                      5       REFERENCE_ID           VARCHAR2(100),
                                      6       NAME_FIRST           VARCHAR2(100),
                                      7       NAME_MIDDLE           VARCHAR2(100),
                                      8       NAME_LAST           VARCHAR2(100),
                                      9       YEAR                VARCHAR2(100),
                                    10       MONTH                VARCHAR2(100),
                                    11       DAY                VARCHAR2(100))
                                    12  /

                                    Table created.

                                    SCOTT@10gXE> INSERT ALL
                                      2  INTO data_table VALUES ('acct_name1', '1', 'sfc1', '1', 'first1', 'mid1', 'last1', '2007', 'JAN', '28')
                                      3  INTO data_table VALUES ('acct_name2', '2', 'sfc2', '2', 'first2', 'mid2', 'last2', '2007', 'JAN', '29')
                                      4  INTO data_table VALUES ('acct_name3', '3', 'sfc3', '3', 'first3', 'mid3', 'last3', '2007', 'JAN', '30')
                                      5  SELECT * FROM DUAL
                                      6  /

                                    3 rows created.

                                    SCOTT@10gXE> CREATE TABLE PRE_ID_VELOCITY
                                      2    (SEVERITYTYPEFIELD     NUMBER(38),
                                      3       MODETYPEFIELD          NUMBER(38),
                                      4       RAWDATA           VARCHAR2(4000),
                                      5       ACCOUNTNAME          VARCHAR2(150),
                                      6       EVENTTIME          TIMESTAMP(3),
                                      7       VELOCITYCHECKTYPEFIELD     NUMBER(38),
                                      8       EVENTREFERENCEID     NUMBER(38),
                                      9       WLS_TEMP          NUMBER(38))
                                    10  /

                                    Table created.

                                    SCOTT@10gXE> insert into pre_id_velocity
                                      2    (rawdata
                                      3       , accountname
                                      4       , modetypefield
                                      5       , eventreferenceid
                                      6       -- , and other columns
                                      7    )
                                      8  SELECT DBMS_XMLGEN.GETXML
                                      9             ('SELECT d.account_name, d.modetype
                                    10                   -- , and other columns
                                    11            FROM   data_table d
                                    12            WHERE  d.rowid = ''' || t.rowid || '''')
                                    13          , account_name
                                    14          , modetype
                                    15          , reference_id
                                    16           -- , and other columns
                                    17  FROM   data_table t
                                    18  /

                                    3 rows created.

                                    SCOTT@10gXE> COLUMN rawdata     FORMAT A30
                                    SCOTT@10gXE> COLUMN accountname FORMAT A11
                                    SCOTT@10gXE> SELECT modetypefield, rawdata, accountname, eventreferenceid FROM pre_id_velocity
                                      2  /

                                    MODETYPEFIELD RAWDATA                        ACCOUNTNAME EVENTREFERENCEID
                                    ------------- ------------------------------ ----------- ----------------
                                                1 <?xml version="1.0"?>          acct_name1                 1
                                                  <ROWSET>
                                                   <ROW>
                                                    <ACCOUNT_NAME>acct_name1</AC
                                                  COUNT_NAME>
                                                    <MODETYPE>1</MODETYPE>
                                                   </ROW>
                                                  </ROWSET>

                                                2 <?xml version="1.0"?>          acct_name2                 2
                                                  <ROWSET>
                                                   <ROW>
                                                    <ACCOUNT_NAME>acct_name2</AC
                                                  COUNT_NAME>
                                                    <MODETYPE>2</MODETYPE>
                                                   </ROW>
                                                  </ROWSET>

                                                3 <?xml version="1.0"?>          acct_name3                 3
                                                  <ROWSET>
                                                   <ROW>
                                                    <ACCOUNT_NAME>acct_name3</AC
                                                  COUNT_NAME>
                                                    <MODETYPE>3</MODETYPE>
                                                   </ROW>
                                                  </ROWSET>


                                    SCOTT@10gXE>