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

Loading an Oracle Thesaurus:

429085 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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