1 2 3 Previous Next 33 Replies Latest reply: Jul 11, 2007 4:59 PM by Barbara Boehmer RSS

    Loading an Oracle Thesaurus:

    429085
      Hi,
      We are about to start using a thesaurus for our website.
      I have created the Oracle search functionality and have setup and loaded a thesaurus using ctxload.
      All works wonderfully.
      The only things that I am not happy with is the thesaurus refresh process.
      Currently we have an administration screen that allows admin users to add thesaurus records to a table: 'table_thesaurus' which has 2 columns search_synonym and term. The table looks like this:

      SEARCH_SYNONYM     / TERM
      accesarys     accessory
      accesories     accessory
      accesory     accessory
      accesorys     accessory
      accessoarys accessory
      accessori     accessory
      accessorie     accessory
      accessories accessory
      accessorys accessory

      Once this table is updated by the admin user I run the following:

      set pagesize 0 feedback off trimspool on
      col thes_word noprint
      col seq noprint
      conn thesaurus/<password>
      spool sfdthes.txt
      select max(term) term, 1 seq, max(term) "word"
      from table_thesaurus
      group by term
      having count(*) > 1
      union
      select term, 2 seq, ' SYN '||search_synonym "synonym"
      from table_thesaurus
      where thes_word <> search_synonym
      order by 1,2,3
      /
      spool off
      exit

      This creates a file formatted like this:

      accessory
      SYN acc
      SYN accesarys
      SYN accesories
      SYN accesory
      SYN accesorys
      SYN accessoarys
      SYN accessori
      SYN accessorie
      SYN accessories
      SYN accessorys
      SYN accs
      SYN accsessory
      SYN acesori
      SYN acesories
      SYN acesory
      SYN acessories
      SYN acessory
      SYN acsesory
      SYN acssesory
      SYN acssori
      SYN acident

      I then drop the current thesarus:

      execute ctx_thes.drop_thesaurus('thes');


      I then recreate the thesaurus using the file I have created:

      ctxload -user ctxsys/<passwd> -thes -name sfdthes -file thes.txt

      Here in lies my question...

      Does anybody know of a way to do the thesaurus load directly from the table_thesaurus without all this manual intervention?
        • 1. Re: Loading an Oracle Thesaurus:
          Barbara Boehmer
          You can create a procedure to do the update process. You will need to have execute on ctx_thes granted directly to the creator of the procedure, not through a role. Then you can just execute the procedure or schedule it using dbms_job.
          CREATE OR REPLACE PROCEDURE update_thesaurus
          AS
          BEGIN
            FOR r IN (SELECT * FROM table_thesaurus) LOOP
              CTX_THES.CREATE_RELATION
                ('your_thesaurus',
                 r.search_synonym,
                 'SYN',
                 r.term);
            END LOOP;
          END update_thesaurus;
          /

          EXECUTE update_thesaurus
          • 2. Re: Loading an Oracle Thesaurus:
            429085
            Thank you Barbara.

            I've installed the package as user1

            As ctxsys user I ran: grant all on ctx_thes to user1

            I then run the package, but get the following error:

            SQL> exec update_thesaurus
            BEGIN update_thesaurus; END;

            *
            ERROR at line 1:
            ORA-20000: Oracle Text error:
            DRG-10016: you must be the owner to modify this object
            ORA-06512: at "CTXSYS.DRUE", line 157
            ORA-06512: at "CTXSYS.CTX_THES", line 345
            ORA-06512: at "CATALOG1.UPDATE_THESAURUS", line 5
            ORA-06512: at line 1

            I'm having a stupid Friday - Do you know what I am missing?
            • 3. Re: Loading an Oracle Thesaurus:
              429085
              The update_thesaurus package only compiles when I log in as ctxsys and grant execute on ctx_thes to the user that ownes the procedure - But I still cannot run the procedure...?
              • 4. Re: Loading an Oracle Thesaurus:
                Barbara Boehmer
                You say you granted all on ctx_thes to user1, but your error message shows that the procedure belongs to catalog1, not user1. Whatever user creates the procedure must have appropriate privileges on all objects used in the procedure: execute on ctx_thes, select on table_thesaurus and appropriate privileges or ownership on the thesaurus to be modified. It looks like the error message is saying that you must be the owner of the thesaurus to modify it, so you may need to create the procedure in whatever schema owns the thesaurus, then grant execute on the update_thesaurus procedure to whoever needs to execute it.

                Message was edited by:
                Barbara Boehmer
                • 5. Re: Loading an Oracle Thesaurus:
                  429085
                  Hi Barbara,

                  I have follwed this process and I am still unable to run the package - I cannot see all objects that are updated as part of ctxsys package as the body is encrypted.

                  As user1: grant select on scs_thesaurus to thesaurus

                  AS ctxsys: grant execute on ctx_thes to thesaurus

                  As thesaurus:

                  CREATE OR REPLACE PROCEDURE update_thes
                  AS
                  BEGIN
                  FOR r IN (SELECT * FROM scs_thesaurus)
                  LOOP CTX_THES.CREATE_RELATION
                  ('sfdthes', r.search_synonym,'SYN', r.term);
                  END LOOP;
                  END update_thes;
                  /


                  exec up-date_thes;

                  ERROR at line 1:
                  ORA-20000: Oracle Text error:
                  DRG-10016: you must be the owner to modify this object
                  ORA-06512: at "CTXSYS.DRUE", line 157
                  ORA-06512: at "CTXSYS.CTX_THES", line 345
                  ORA-06512: at "THESAURUS.UPDATE_THES", line 5
                  ORA-06512: at line 1

                  AS ctxsys: grant execute on drue to thesaurus

                  AS thesaurus:

                  ERROR at line 1:
                  ORA-20000: Oracle Text error:
                  DRG-10016: you must be the owner to modify this object
                  ORA-06512: at "CTXSYS.DRUE", line 157
                  ORA-06512: at "CTXSYS.CTX_THES", line 345
                  ORA-06512: at "THESAURUS.UPDATE_THES", line 5
                  ORA-06512: at line 1

                  Would you know the grants I need to make based on using the thesaurus user?
                  • 6. Re: Loading an Oracle Thesaurus:
                    Barbara Boehmer
                    Based on your initial post, it appears that ctxsys is the owner of your thesaurus. So, you must either create the procedure in the ctxsys schema or have ctxsys drop the thesaurus, then recreate the thesaurus in the thesaurus schema, where you can then create the procedure.
                    • 7. Re: Loading an Oracle Thesaurus:
                      429085
                      Hi Barbara,
                      Do you want my job?
                      I've done the following:
                      Created the update_thes proc as ctxsys
                      Granted right on user1.scs_thesarus table to ctxsys
                      Inserted an update into the table
                      Run the procedure - Completes ok! - Brilliant!
                      But...
                      When I do a search based on the new synonym no data is returned...
                      Do you know where I need to look to find the updates in my thesaurus to make sure they have been applied correctly by ctx_thes?
                      • 8. Re: Loading an Oracle Thesaurus:
                        429085
                        Barbara,
                        I ran this:
                        set serveroutput on;
                        declare
                        xtab ctx_thes.exp_tab;
                        begin
                        ctx_thes.syn(xtab, 'testword', 'sfdthes');
                        for i in 1..xtab.count loop
                        dbms_output.put_line(lpad(' ', 2*xtab(i).xlevel) ||
                        xtab(i).xrel || ' ' || xtab(i).xphrase);
                        end loop;
                        end;
                        /
                        I can see my entry in the thesaurus for the newly updated synonym but when I run q query which relies on this data nothing is returned - Do I need to run something to update indexes or similar after the update?
                        Best Regards.
                        • 9. Re: Loading an Oracle Thesaurus:
                          Barbara Boehmer
                          You do not need to do anything to your index after the thesaurus is updated, but you do need to either resynchronize or rebuild or drop and recreate your index after data is inserted into the table that the index is on. I know that your scs_thesaurus table is in the user1 schema and your sdfthes thesaurus is owned by ctxsys and your update_thes procedure is now in your ctxsys schema, but I do not know what table you are trying to query and who owns it, how and when your index was created and how it is synchronized, what query you are using or who is running the query. I have provided an example below, in which a user named you is created, has a table, index, and data, and runs a contains query that searches for synonyms, that initially returns nothing, because the index was not synchronized after data insertion. Then, after synchronization, rows are returned, but no synonyms, because the thesaurus is empty. Then, after executing the procedure to update the thesaurus, the same query also returns synonyms, without doing anything additional to the index. I have included alll necessary privileges and how to display words in the thesaurus and words in the index in the demonstration. If this does not solve your problem, then please provide more specifics; A copy and paste of a full run of code, complete with notes as to what schema what was run from, somethng like what I have provided below, is best.
                          scott@ORA92> -- connect as ctxsys, create an empty thesaurus,
                          scott@ORA92> -- create users you and user1, and grant privileges:
                          scott@ORA92> CONNECT CTXSYS/CTXSYS_PASSWORD
                          Connected.
                          scott@ORA92> @ LOGIN
                          scott@ORA92> SET ECHO OFF

                          GLOBAL_NAME
                          ----------------------------------------------------------------------------------------------------
                          ctxsys@ORA92

                          ctxsys@ORA92> EXEC CTX_THES.DROP_THESAURUS ('sdfthes')

                          PL/SQL procedure successfully completed.

                          ctxsys@ORA92> EXEC CTX_THES.CREATE_THESAURUS ('sdfthes')

                          PL/SQL procedure successfully completed.

                          ctxsys@ORA92> CREATE USER you IDENTIFIED BY you
                            2  /

                          User created.

                          ctxsys@ORA92> GRANT CONNECT, RESOURCE TO you
                            2  /

                          Grant succeeded.

                          ctxsys@ORA92> GRANT EXECUTE ON ctx_ddl TO you
                            2  /

                          Grant succeeded.

                          ctxsys@ORA92> GRANT EXECUTE ON ctx_thes TO you
                            2  /

                          Grant succeeded.

                          ctxsys@ORA92> CREATE USER user1 IDENTIFIED BY user1
                            2  /

                          User created.

                          ctxsys@ORA92> GRANT CONNECT, RESOURCE TO user1
                            2  /

                          Grant succeeded.

                          ctxsys@ORA92> -- connect as user1, create table, insert synonyms, and grant privileges:
                          ctxsys@ORA92> CONNECT user1/user1
                          Connected.
                          ctxsys@ORA92> @ LOGIN
                          ctxsys@ORA92> SET ECHO OFF

                          GLOBAL_NAME
                          ----------------------------------------------------------------------------------------------------
                          user1@ORA92

                          user1@ORA92> CREATE TABLE scs_thesaurus
                            2    (search_synonym VARCHAR2(30),
                            3       term            VARCHAR2(30))
                            4  /

                          Table created.

                          user1@ORA92> INSERT INTO scs_thesaurus VALUES ('accesarys',  'accessory')
                            2  /

                          1 row created.

                          user1@ORA92> GRANT SELECT ON scs_thesaurus TO CTXSYS
                            2  /

                          Grant succeeded.

                          user1@ORA92> -- connect as ctxsys, create the procedure, and grant privileges:
                          user1@ORA92> CONNECT ctxsys/ctxsys_password
                          Connected.
                          user1@ORA92> @ LOGIN
                          user1@ORA92> SET ECHO OFF

                          GLOBAL_NAME
                          ----------------------------------------------------------------------------------------------------
                          ctxsys@ORA92

                          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  /

                          Procedure created.

                          ctxsys@ORA92> SHOW ERRORS
                          No errors.
                          ctxsys@ORA92> GRANT EXECUTE ON update_thes TO you
                            2  /

                          Grant succeeded.

                          ctxsys@ORA92> -- connect as you, create a table and an index, then insert data:
                          ctxsys@ORA92> @ CONNECT you/you
                          ctxsys@ORA92> set termout off
                          ctxsys@ORA92> connect &1
                          Connected.
                          ctxsys@ORA92> @login
                          ctxsys@ORA92> SET ECHO OFF

                          GLOBAL_NAME
                          ----------------------------------------------------------------------------------------------------
                          you@ORA92

                          you@ORA92> set termout on
                          you@ORA92> @ LOGIN
                          you@ORA92> SET ECHO OFF

                          GLOBAL_NAME
                          ----------------------------------------------------------------------------------------------------
                          you@ORA92

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

                          Table created.

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

                          Index created.

                          you@ORA92> INSERT ALL
                            2  INTO test_tab VALUES ('accessory')
                            3  INTO test_tab VALUES ('accesarys')
                            4  SELECT * FROM DUAL
                            5  /

                          2 rows created.

                          you@ORA92> -- since the index has not been synchronized
                          you@ORA92> -- or rebuilt or dropped and recreated,
                          you@ORA92> -- the inserted data has not been indexed:
                          you@ORA92> SELECT token_text FROM dr$test_idx$i
                            2  /

                          no rows selected

                          you@ORA92> -- so a contains query returns no rows:
                          you@ORA92> SELECT * FROM test_tab
                            2  WHERE  CONTAINS (test_col, 'SYN (accessory, sdfthes)') > 0
                            3  /

                          no rows selected

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

                          no rows selected

                          you@ORA92> -- synchronize the index:
                          you@ORA92> EXEC CTX_DDL.SYNC_INDEX ('test_idx')

                          PL/SQL procedure successfully completed.

                          you@ORA92> -- the thesaurus is still empty:
                          you@ORA92> SELECT * FROM ctx_thesauri WHERE ths_name = 'SDFTHES'
                            2  /

                          THS_OWNER                      THS_NAME
                          ------------------------------ ------------------------------
                          CTXSYS                         SDFTHES

                          you@ORA92> SELECT thp_phrase FROM ctx_thes_phrases WHERE thp_thesaurus = 'SDFTHES'
                            2  /

                          no rows selected

                          you@ORA92> -- the inserted data has now been indexed:
                          you@ORA92> SELECT token_text FROM dr$test_idx$i
                            2  /

                          TOKEN_TEXT
                          ----------------------------------------------------------------
                          ACCESARYS
                          ACCESSORY

                          you@ORA92> -- so a contains query returns indexed words, but not synonyms:
                          you@ORA92> SELECT * FROM test_tab
                            2  WHERE  CONTAINS (test_col, 'SYN (accessory, sdfthes)') > 0
                            3  /

                          TEST_COL
                          ------------------------------
                          accessory

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

                          TEST_COL
                          ------------------------------
                          accesarys

                          you@ORA92> -- execute the procedure:
                          you@ORA92> EXECUTE CTXSYS.update_thes

                          PL/SQL procedure successfully completed.

                          you@ORA92> -- the thesaurus now contains synonyms:
                          you@ORA92> SELECT * FROM ctx_thesauri WHERE ths_name = 'SDFTHES'
                            2  /

                          THS_OWNER                      THS_NAME
                          ------------------------------ ------------------------------
                          CTXSYS                         SDFTHES

                          you@ORA92> SELECT thp_phrase FROM ctx_thes_phrases WHERE thp_thesaurus = 'SDFTHES'
                            2  /

                          THP_PHRASE
                          ----------------------------------------------------------------------------------------------------
                          ACCESARYS
                          ACCESSORY

                          you@ORA92> -- and a contains query now returns synonyms:
                          you@ORA92> SELECT * FROM test_tab
                            2  WHERE  CONTAINS (test_col, 'SYN (accessory, sdfthes)') > 0
                            3  /

                          TEST_COL
                          ------------------------------
                          accesarys
                          accessory

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

                          TEST_COL
                          ------------------------------
                          accesarys
                          accessory

                          you@ORA92>
                          • 10. Re: Loading an Oracle Thesaurus:
                            429085
                            Hi Barbara,
                            Thank you so much for taking the time to send that info.

                            Since looking at this I have discovered that the problem was with my search function not the thesaurus load you showed me. The load works perfectly well - Thank you (Now that I am running it as ctxsys as the owner of the procedure).

                            The problem I am having with the search function is really annoying. I wonder if you might be

                            able to comment on this?

                            I have a product website which I need to create a search function for. There was originally a simple search function that made use of Oracle Text indexing to enable use of CONTAINS syntax. We used java to pass the search string from the site to the function - this string included stemming/wildcards and 'ANDS' ($word% and $another%).

                            We decided that we wanted to use a thesaurus to improve the search. The remit was to deliver functionality that would allow for the use of a thesarus to be switchable so that we could turn off thesaurus use as required.

                            I took a look at the function that already existed and came up with the idea of using a simple %FOUND type query that would use the thesaurus, if nothing was found in the normal search then the thesaurus query would be used to try and find a hit. To enable switching I created a package with 2 functions, one including the thesaurus and one normal as before.

                            What I have since found is that the SYN function is not working for me as hoped.

                            Basically what I want is a function that will return the correct spelling for an incorretly spelt word/phrase. I then need to be able to take this work/phrase and apply stemming to make sure that I am not mising words that might not be in my thesaurus.

                            For example:

                            The user types in 'piant' (they meant to type in paint)
                            I want to be able to use an Oracle function to find the correct spelling:
                            So I return 'paint'
                            I then want to be able to search for $paint%
                            This should return paint; paints; paintings etc

                            Can you think of a way to do this?

                            Do you think that something like 'TT' (Top term) might be what I need?)

                            Any help truely appreciated.
                            • 11. Re: Loading an Oracle Thesaurus:
                              429085
                              To sum up - I need a method that will only return top term from bottom term.
                              e.g.
                              samsung
                              --samsing
                              --samsong
                              --samsnug
                              If I type in a mis-spell I only want to see samsung.
                              • 12. Re: Loading an Oracle Thesaurus:
                                Barbara Boehmer
                                The biggest part of the problem seems to be combining the correction of spelling, stemming, and wildcard all together in the proper order. You need to first fix the spelling, then use stemming, then add the wildcard, and you need to be able to do this for each word within a string containing multiple words. You can use a thesaurus and TT or BT to fix the spellings based on your table of commonly misspelled words, which is probably more complete than what a fuzzy search might find. You can combine that with stemming, but the result of stemming is incomplete. Unfortunately, it seems that you cannot combine the wildcard after the thesaurus, and using it before the thesaurus yields incorrect results. My inclination would be to create a function to correct the spellings within the search string, by selecting directly from your scs_thesaurus table (with an index) without using a thesaurus, then apply both stemming and wildcard. I have demonstrated two methods below, the first using the thesaurus and stemming with limited results, and the second using the function and stemming and the wildcard, which finds everything expected. I have ensured that the code searches for multiple words and allows for words that are not in the scs_thesaurus table. One and only one space between words in the search string is expected, but you can easily remove any excess spaces from your search string if that is not the case, either before passing it to the funcction or within the function. You may not necessarily want to keep everything in the same schemas that I have used. I am just trying to demonstrate the functionality. I follow one of Tom Kyte's mantras, that if you can do something in sql then do so, if not then if you can do it in pl/sql then do so, if not then if you can do it in java do so. In other words, if it can be handled completely with mostly sql and one pl/sql function, then eliminate the java, just pass the string, and let sql and pl/sql handle it. Whoever wrote the original code probably used java instead of pl/sql because that was what they were more familiar, comfortable, and experienced with.
                                scott@ORA92> cONNECT CTXSYS/CTXSYS_PASSWORD
                                Connected.
                                scott@ORA92> @ LOGIN
                                scott@ORA92> SET ECHO OFF

                                GLOBAL_NAME
                                ----------------------------------------------------------------------------------------------------
                                ctxsys@ORA92

                                ctxsys@ORA92> EXEC CTX_THES.DROP_THESAURUS ('sdfthes')

                                PL/SQL procedure successfully completed.

                                ctxsys@ORA92> EXEC CTX_THES.CREATE_THESAURUS ('sdfthes')

                                PL/SQL procedure successfully completed.

                                ctxsys@ORA92> CREATE USER you IDENTIFIED BY you
                                  2  /

                                User created.

                                ctxsys@ORA92> GRANT CONNECT, RESOURCE TO you
                                  2  /

                                Grant succeeded.

                                ctxsys@ORA92> GRANT EXECUTE ON ctx_ddl TO you
                                  2  /

                                Grant succeeded.

                                ctxsys@ORA92> GRANT EXECUTE ON ctx_thes TO you
                                  2  /

                                Grant succeeded.

                                ctxsys@ORA92> CREATE USER user1 IDENTIFIED BY user1
                                  2  /

                                User created.

                                ctxsys@ORA92> GRANT CONNECT, RESOURCE TO user1
                                  2  /

                                Grant succeeded.

                                ctxsys@ORA92> CONNECT user1/user1
                                Connected.
                                ctxsys@ORA92> @ LOGIN
                                ctxsys@ORA92> SET ECHO OFF

                                GLOBAL_NAME
                                ----------------------------------------------------------------------------------------------------
                                user1@ORA92

                                user1@ORA92> CREATE TABLE scs_thesaurus
                                  2    (search_synonym VARCHAR2(30),
                                  3       term            VARCHAR2(30))
                                  4  /

                                Table created.

                                user1@ORA92> INSERT INTO scs_thesaurus VALUES ('piant',  'paint')
                                  2  /

                                1 row created.

                                user1@ORA92> INSERT INTO scs_thesaurus VALUES ('samsing',  'samsung')
                                  2  /

                                1 row created.

                                user1@ORA92> INSERT INTO scs_thesaurus VALUES ('samsong',  'samsung')
                                  2  /

                                1 row created.

                                user1@ORA92> INSERT INTO scs_thesaurus VALUES ('samsnug',  'samsung')
                                  2  /

                                1 row created.

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

                                Index created.

                                user1@ORA92> GRANT SELECT ON scs_thesaurus TO CTXSYS
                                  2  /

                                Grant succeeded.

                                user1@ORA92> GRANT SELECT ON scs_thesaurus TO you
                                  2  /

                                Grant succeeded.

                                user1@ORA92> CONNECT ctxsys/ctxsys_password
                                Connected.
                                user1@ORA92> @ LOGIN
                                user1@ORA92> SET ECHO OFF

                                GLOBAL_NAME
                                ----------------------------------------------------------------------------------------------------
                                ctxsys@ORA92

                                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;
                                13  /

                                Procedure created.

                                ctxsys@ORA92> SHOW ERRORS
                                No errors.
                                ctxsys@ORA92> GRANT EXECUTE ON update_thes TO you
                                  2  /

                                Grant succeeded.

                                ctxsys@ORA92> @ CONNECT you/you
                                ctxsys@ORA92> set termout off
                                ctxsys@ORA92> connect &1
                                Connected.
                                ctxsys@ORA92> @login
                                ctxsys@ORA92> SET ECHO OFF

                                GLOBAL_NAME
                                ----------------------------------------------------------------------------------------------------
                                you@ORA92

                                you@ORA92> set termout on
                                you@ORA92> @ LOGIN
                                you@ORA92> SET ECHO OFF

                                GLOBAL_NAME
                                ----------------------------------------------------------------------------------------------------
                                you@ORA92

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

                                Table created.

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

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

                                Index created.

                                you@ORA92> EXECUTE CTXSYS.update_thes

                                PL/SQL procedure successfully completed.

                                you@ORA92> -- using thesaurus and stemming, no wildcard:
                                you@ORA92> VARIABLE g_search_terms VARCHAR2(60)
                                you@ORA92> EXECUTE :g_search_terms := 'piant'

                                PL/SQL procedure successfully completed.

                                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  /

                                TEST_COL
                                ------------------------------
                                samsung painting
                                painting
                                paints
                                paint

                                you@ORA92> EXECUTE :g_search_terms := 'samsing'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'samsong'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'samsnug'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'samsing piant'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'kittycat'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

                                TEST_COL
                                ------------------------------
                                kittycat

                                you@ORA92> -- using table directly (without thesaurus) with stemming and with wildcard:
                                you@ORA92> CREATE OR REPLACE FUNCTION correct_spelling
                                  2    (p_search_terms IN VARCHAR2)
                                  3    RETURN            VARCHAR2
                                  4  AS
                                  5    v_search_terms       VARCHAR2(2000) := p_search_terms || ' ';
                                  6    v_correct_spell       VARCHAR2(2000);
                                  7  BEGIN
                                  8    FOR i IN 1 .. LENGTH (v_search_terms) - LENGTH (REPLACE (v_search_terms, ' ', ''))
                                  9    LOOP
                                10        FOR r IN
                                11          (SELECT term
                                12           FROM   user1.scs_thesaurus
                                13           WHERE  search_synonym =
                                14               SUBSTR (v_search_terms, 1, INSTR (v_search_terms, ' ') - 1)
                                15           UNION ALL
                                16           SELECT SUBSTR (v_search_terms, 1, INSTR (v_search_terms, ' ') - 1) AS term
                                17           FROM   DUAL
                                18           WHERE  NOT EXISTS
                                19               (SELECT *
                                20                FROM   user1.scs_thesaurus
                                21                WHERE  search_synonym =
                                22                    SUBSTR (v_search_terms, 1, INSTR (v_search_terms, ' ') - 1)))
                                23        LOOP
                                24          v_correct_spell := v_correct_spell || ' ' || r.term;
                                25          v_search_terms := SUBSTR (v_search_terms, INSTR (v_search_terms, ' ') + 1);
                                26        END LOOP;
                                27    END LOOP;
                                28    RETURN LTRIM (v_correct_spell, ' ');
                                29  END correct_spelling;
                                30  /

                                Function created.

                                you@ORA92> SHOW ERRORS
                                No errors.
                                you@ORA92> EXECUTE :g_search_terms := 'piant'

                                PL/SQL procedure successfully completed.

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

                                TEST_COL
                                ------------------------------
                                samsung painting
                                paintings
                                painting
                                painters
                                painter
                                paints
                                paint

                                7 rows selected.

                                you@ORA92> EXECUTE :g_search_terms := 'samsing'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'samsong'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'samsnug'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'samsing piant'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

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

                                you@ORA92> EXECUTE :g_search_terms := 'kittycat'

                                PL/SQL procedure successfully completed.

                                you@ORA92> /

                                TEST_COL
                                ------------------------------
                                kittycat

                                you@ORA92>
                                • 13. Re: Loading an Oracle Thesaurus:
                                  Barbara Boehmer
                                  I would test with and without the spell-checking and see how much the spell-checking reduces performance, then decide whether you need to offer an option with spell-checking and an option without. How are you returning the results to your website? Are you using a function that opens a ref cursor using the contains query, then returns the result set in that ref cursor to the website / java?
                                  • 14. Re: Loading an Oracle Thesaurus:
                                    429085
                                    Wow that looks like the exact think I am looking for - I think that the correct spelling function is going to work for us - I will start setup and testing now.

                                    The results are returned to the java by use of ref_cursor:

                                    CREATE OR REPLACE PACKAGE TYPES
                                    AS
                                    TYPE ref_cursor IS REF CURSOR;
                                    END;
                                    /

                                    Best regards - thank you very much!
                                    1 2 3 Previous Next