This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Aug 24, 2012 4:13 PM by AshPatel2 RSS

Newbie to Oracle Text

AshPatel2 Newbie
Currently Being Moderated
I want to create a google like single field search so users can enter a persons name or address and do a search.

There are 3 tables
Customer table - which hold customer_id, firstname, middle name and surname
Address_link table - which holds customer id, property_id
Properties table - which holds address and propert_id

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

Can anyone help on how to do this, in the search field I want the user to be able to enter a partial name or address.

Thanks

Ash
  • 1. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    If you'd like to give us the full definitions of the three tables - preferably with a few lines of sample data, someone can probably knock up a suggested solution for you.

    It will likely involve a USER_DATASTORE procedure which can fetch data from multiple tables, and index that as a single "virtual document".

    A couple of my blog posts may help you if you want to do this yourself:

    Indexing Data from Multiple Tables:
    https://blogs.oracle.com/searchtech/entry/indexing_data_from_multiple_tables

    and (for a robust Google-like search)
    Oracle Text Query Parser:
    https://blogs.oracle.com/searchtech/entry/oracle_text_query_parser
  • 2. Re: Newbie to Oracle Text
    AshPatel2 Newbie
    Currently Being Moderated
    Hi thanks for your help so far, below are the table definitions and some data, if someone could knock something up for me based on this thats would be great as I am really struggling to understand this.

    CREATE TABLE CUSTOMERS
    ( ID NUMBER(8) NOT NULL,
    FNAME VARCHAR2(20) NOT NULL,
    SNAME VARCHAR2(65) NOT NULL,
    MNAME VARCHAR2(20),
    TITLE VARCHAR2(8),
    DOB DATE);

    CREATE TABLE ADDRESS_LINK
    ( ID NUMBER(8) NOT NULL,
    CUS_ID NUMBER(8) NOT NULL,
    PROP_ID NUMBER(8) NOT NULL,
    DATE_FROM DATE NOT NULL,
    DATE_TO DATE);


    CREATE TABLE PROPERTY
    ( ID NUMBER(8) NOT NULL,
    HOUSE_NO VARCHAR2(5),
    STREET VARCHAR2(100),
    POSTCODE VARCHAR2(12),
    ADDRESS_LABEL VARCHAR2(750));

    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME, MNAME, TITLE)
    Values
    (6, 'ASH', 'NATHU', 'MUMBAI', 'DR');
    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME, MNAME, TITLE)
    Values
    (7, 'COOKIE', 'MONSTER', 'EATER', 'MR');
    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME, MNAME, TITLE,
    DOB)
    Values
    (1, 'MINI', 'MOUSE', 'MICKEY', 'MRS',
    TO_DATE('07/31/1933 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME, TITLE, DOB)
    Values
    (3, 'FROGGLE', 'REED', 'MR', TO_DATE('06/25/1986 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME, TITLE, DOB)
    Values
    (4, 'LEENA', 'RIPPLE', 'MRS', TO_DATE('03/23/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME, MNAME, TITLE,
    DOB)
    Values
    (5, 'KERRY', 'SINGLETON', 'PARIS', 'MISS',
    TO_DATE('01/08/1991 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.CUSTOMERS
    (ID, FNAME, SNAME)
    Values
    (2, 'CLARK', 'KENT');
    COMMIT;

    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM)
    Values
    (902, 6, 7000, TO_DATE('06/21/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM)
    Values
    (903, 7, 4000, TO_DATE('08/23/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (100, 1, 1000, TO_DATE('02/01/2000 08:29:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/02/2010 12:29:16', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (300, 3, 3000, TO_DATE('09/08/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (600, 4, 6000, TO_DATE('08/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (700, 4, 7000, TO_DATE('12/04/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM)
    Values
    (800, 4, 8000, TO_DATE('01/23/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (900, 5, 9000, TO_DATE('08/18/1971 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/02/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (901, 5, 10000, TO_DATE('08/02/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/03/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM)
    Values
    (200, 2, 2000, TO_DATE('11/14/2001 18:10:07', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
    Values
    (400, 3, 4000, TO_DATE('04/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into OSS.ADDRESS_LINK
    (ID, CUS_ID, PROP_ID, DATE_FROM)
    Values
    (500, 3, 5000, TO_DATE('11/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    COMMIT;

    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (1000, '12', 'ELM STREET', 'LS1 2EW', '12, ELM STREET, LS1 2EW');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (2000, '122', 'HIGH ROAD', 'EW8 9RR', '122, HIGH ROAD, EW8 9RR');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (3000, '3', 'PARK AVENUE', 'M10 4RT', '3, PARK AVENU, M10 4RT');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (4000, '99', 'KING STREET', 'LE3 5TR', '99, KING STREET, LE3 5TR');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (5000, '88', 'URBAN CLOSE', 'P12 3ER', '88, URBAN CLOSE, P12 3ER');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (6000, '50', 'TWIN STREET', 'K11 6TY', '50, TWIN STREET, K11 6TY');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (7000, '34', 'DAIL AVENUE', 'D9 3DD', '34, DAIL AVENUE, D9 3DD');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (8000, '45', 'ELM STREET', 'LS1 2EW', '45, ELM STREET, LS1 2EW');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (9000, '25', 'PARK AVENUE', 'M10 4RT', '25, PARK AVENU, M10 4RT');
    Insert into OSS.PROPERTY
    (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
    Values
    (10000, '33', 'BRICK LANE', 'B12 2WQ', '33, BRICK LANE, B12 2WQ');
    COMMIT;


    thanks
    Ash
  • 3. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    OK, here you go. This creates a user datastore which combines the data from the various tables in a "virtual document" looking something like

    <fullname>Miss Kerry Singleton Paris</fullname> <dob>12-DEC-1996 </dob>
    <address>33 Brick Lane B12 2WQ</address>
    <address>25 Park Avenue M10 4RT</address>

    An index is then created on customer.fname which calls this procedure. The sample queries show how you can discriminate between name and address, but if you really want a simple Google-like search you can just combine all the terms with AND (or ACCUM) and put that in the CONTAINS clause.
    connect system/systempassword
    
    drop user sampleuser cascade;
    
    create user sampleuser identified by sampleuser default tablespace users temporary tablespace temp;
    
    grant connect,resource,ctxapp to sampleuser;
    
    alter user sampleuser quota unlimited on users;
    
    connect sampleuser/sampleuser
    
    CREATE TABLE CUSTOMERS
    ( ID                  NUMBER(8)            NOT NULL,
      FNAME               VARCHAR2(20)         NOT NULL,
      SNAME               VARCHAR2(65)         NOT NULL,
      MNAME               VARCHAR2(20),
      TITLE               VARCHAR2(8),
      DOB                 DATE);
    
    CREATE TABLE ADDRESS_LINK
    ( ID                   NUMBER(8)                 NOT NULL,
      CUS_ID               NUMBER(8)                NOT NULL,
      PROP_ID              NUMBER(8)                NOT NULL,
      DATE_FROM            DATE                     NOT NULL,
      DATE_TO              DATE);
      
    
    CREATE TABLE PROPERTY
    ( ID                        NUMBER(8)           NOT NULL,
      HOUSE_NO                  VARCHAR2(5),
      STREET                    VARCHAR2(100),
      POSTCODE                  VARCHAR2(12),
      ADDRESS_LABEL             VARCHAR2(750));
    
    Insert into CUSTOMERS
       (ID, FNAME, SNAME, MNAME, TITLE)
     Values
       (6, 'ASH', 'NATHU', 'MUMBAI', 'DR');
    Insert into CUSTOMERS
       (ID, FNAME, SNAME, MNAME, TITLE)
     Values
       (7, 'COOKIE', 'MONSTER', 'EATER', 'MR');
    Insert into CUSTOMERS
       (ID, FNAME, SNAME, MNAME, TITLE, 
        DOB)
     Values
       (1, 'MINI', 'MOUSE', 'MICKEY', 'MRS', 
        TO_DATE('07/31/1933 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CUSTOMERS
       (ID, FNAME, SNAME, TITLE, DOB)
     Values
       (3, 'FROGGLE', 'REED', 'MR', TO_DATE('06/25/1986 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CUSTOMERS
       (ID, FNAME, SNAME, TITLE, DOB)
     Values
       (4, 'LEENA', 'RIPPLE', 'MRS', TO_DATE('03/23/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CUSTOMERS
       (ID, FNAME, SNAME, MNAME, TITLE, 
        DOB)
     Values
       (5, 'KERRY', 'SINGLETON', 'PARIS', 'MISS', 
        TO_DATE('01/08/1991 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CUSTOMERS
       (ID, FNAME, SNAME)
     Values
       (2, 'CLARK', 'KENT');
    COMMIT;
    
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM)
     Values
       (902, 6, 7000, TO_DATE('06/21/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM)
     Values
       (903, 7, 4000, TO_DATE('08/23/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (100, 1, 1000, TO_DATE('02/01/2000 08:29:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/02/2010 12:29:16', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (300, 3, 3000, TO_DATE('09/08/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (600, 4, 6000, TO_DATE('08/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (700, 4, 7000, TO_DATE('12/04/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM)
     Values
       (800, 4, 8000, TO_DATE('01/23/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (900, 5, 9000, TO_DATE('08/18/1971 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/02/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (901, 5, 10000, TO_DATE('08/02/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/03/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM)
     Values
       (200, 2, 2000, TO_DATE('11/14/2001 18:10:07', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM, DATE_TO)
     Values
       (400, 3, 4000, TO_DATE('04/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/20/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into ADDRESS_LINK
       (ID, CUS_ID, PROP_ID, DATE_FROM)
     Values
       (500, 3, 5000, TO_DATE('11/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    COMMIT;
    
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (1000, '12', 'ELM STREET', 'LS1 2EW', '12, ELM STREET, LS1 2EW');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (2000, '122', 'HIGH ROAD', 'EW8 9RR', '122, HIGH ROAD, EW8 9RR');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (3000, '3', 'PARK AVENUE', 'M10 4RT', '3, PARK AVENU, M10 4RT');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (4000, '99', 'KING STREET', 'LE3 5TR', '99, KING STREET, LE3 5TR');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (5000, '88', 'URBAN CLOSE', 'P12 3ER', '88, URBAN CLOSE, P12 3ER');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (6000, '50', 'TWIN STREET', 'K11 6TY', '50, TWIN STREET, K11 6TY');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (7000, '34', 'DAIL AVENUE', 'D9 3DD', '34, DAIL AVENUE, D9 3DD');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (8000, '45', 'ELM STREET', 'LS1 2EW', '45, ELM STREET, LS1 2EW');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (9000, '25', 'PARK AVENUE', 'M10 4RT', '25, PARK AVENU, M10 4RT');
    Insert into PROPERTY
       (ID, HOUSE_NO, STREET, POSTCODE, ADDRESS_LABEL)
     Values
       (10000, '33', 'BRICK LANE', 'B12 2WQ', '33, BRICK LANE, B12 2WQ');
    COMMIT;
    
    
    -- create the user datastore procedure 
    -- this collects customer name into a <fullname> field and multiple addresses into
    -- individual <address> fields
    
    create or replace procedure cust_datastore (rid in rowid, rclob in out nocopy clob) is
    begin
    
      rclob := '';
    
      -- get the customer details first
      -- this "loop" will only execute once but it's simpler to code this way:
      for c in (
        select c.title ||' '|| c.fname ||' '|| c.mname ||' '|| c.sname as fullname,
               to_char( dob, 'DD-MON-YYYY') as dob
        from   customers    c
        where  c.rowid = rid
      ) loop
        rclob := rclob || '<fullname>' || c.fullname || '</fullname>' || chr(10);
      end loop;
    
      -- second loop gets property details - there may be more than one of these per customer
      for c in (
        select p.house_no||' '||p.street||' '||p.postcode||'  '||p.address_label as address
        from   customers    c,
               address_link a,
               property     p
        where  c.rowid   = rid
        and    a.cus_id  = c.id
        and    a.prop_id = p.id 
      ) loop
        rclob := rclob || '<address>' || c.address || '</address>' || chr(10);
      end loop;
    
    end cust_datastore;
    / 
    list
    show errors
    
    exec ctx_ddl.create_preference('cust_ds', 'USER_DATASTORE')
    exec ctx_ddl.set_attribute('cust_ds', 'PROCEDURE', 'cust_datastore')
    
    exec ctx_ddl.create_section_group('cust_sg', 'AUTO_SECTION_GROUP')
    
    create index cust_index on customers(fname) 
    indextype is ctxsys.context
    parameters ('datastore cust_ds section group cust_sg memory 100M')
    /
    
    -- We need triggers to make sure the index gets updated if 
    -- any of the data referenced in the user datastore changes 
    -- (don't need insert trigger on customers, since indexing will
    --  always happen for new rows in the indexed table)
    
    create or replace trigger cust_update_trigger
    after update 
      on customers
      for each row
    begin
      update customers c set c.fname = c.fname
      where  c.id = :new.id;
    end;
    /
    show err
    
    create or replace trigger addr_update_trigger
    after insert or update 
      on address_link
      for each row
    begin
      update customers c set c.fname = c.fname
      where  c.id = :new.cus_id;
    end;
    /
    show err
    
    create or replace trigger prop_update_trigger
    after insert or update 
      on property
      for each row
    begin
      update customers c set c.fname = c.fname
      where c.id in (
        select cust.id
        from   customers    cust,
               address_link a,
               property     p
        where p.id = :new.id
        and   p.id = a.prop_id
        and   c.id = a.cus_id );
    end;
    /
    show err
    
    -- check for errors from the user datastore
    
    select * from ctx_user_index_errors;
    
    -- try some queries
    
    -- simple search in name
    select * from customers
    where contains (fname, 'nathu WITHIN fullname') > 0;
    
    -- AND search in name
    select * from customers 
    where contains (fname, '(dr AND ash) WITHIN fullname') > 0;
    
    -- anywhere search
    select * from customers 
    where contains (fname, 'mumbai') > 0;
    
    -- phrase search in address
    select * from customers
    where contains (fname, '(brick lane) WITHIN address') > 0;
    Edited by: Roger Ford on Aug 23, 2012 2:17 AM
  • 4. Re: Newbie to Oracle Text
    AshPatel2 Newbie
    Currently Being Moderated
    Thank you so much I will give this a try and get back to you. Really appreciate the help.

    Regards
    Ash
  • 5. Re: Newbie to Oracle Text
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    Roger,

    I think the index needs to be on the address_link table, as there can be multiple properties per customer and multiple customers per property. While you were posting your example, I was putting together the following minimal example.
    SCOTT@orcl_11gR2> COLUMN fname FORMAT A10
    SCOTT@orcl_11gR2> COLUMN sname FORMAT A10
    SCOTT@orcl_11gR2> COLUMN mname FORMAT A10
    SCOTT@orcl_11gR2> SELECT * FROM customers
      2  /
    
            ID FNAME      SNAME      MNAME      TITLE    DOB
    ---------- ---------- ---------- ---------- -------- ---------
             6 ASH        NATHU      MUMBAI     DR
             7 COOKIE     MONSTER    EATER      MR
             1 MINI       MOUSE      MICKEY     MRS      31-JUL-33
             3 FROGGLE    REED                  MR       25-JUN-86
             4 LEENA      RIPPLE                MRS      23-MAR-70
             5 KERRY      SINGLETON  PARIS      MISS     08-JAN-91
             2 CLARK      KENT
    
    7 rows selected.
    
    SCOTT@orcl_11gR2> SELECT * FROM address_link
      2  /
    
            ID     CUS_ID    PROP_ID DATE_FROM DATE_TO
    ---------- ---------- ---------- --------- ---------
           902          6       7000 21-JUN-06
           903          7       4000 23-AUG-07
           100          1       1000 01-FEB-00 02-JAN-10
           300          3       3000 08-SEP-05 20-JUN-06
           600          4       6000 01-AUG-00 03-DEC-00
           700          4       7000 04-DEC-00 20-JUN-06
           800          4       8000 23-JAN-06
           900          5       9000 18-AUG-71 02-AUG-04
           901          5      10000 02-AUG-04 03-JUN-07
           200          2       2000 14-NOV-01
           400          3       4000 01-APR-04 20-JUN-06
           500          3       5000 07-NOV-05
    
    12 rows selected.
    
    SCOTT@orcl_11gR2> COLUMN street        FORMAT A15
    SCOTT@orcl_11gR2> COLUMN address_label FORMAT A30
    SCOTT@orcl_11gR2> SELECT * FROM property
      2  /
    
            ID HOUSE STREET          POSTCODE     ADDRESS_LABEL
    ---------- ----- --------------- ------------ ------------------------------
          1000 12    ELM STREET      LS1 2EW      12, ELM STREET, LS1 2EW
          2000 122   HIGH ROAD       EW8 9RR      122, HIGH ROAD, EW8 9RR
          3000 3     PARK AVENUE     M10 4RT      3, PARK AVENU, M10 4RT
          4000 99    KING STREET     LE3 5TR      99, KING STREET, LE3 5TR
          5000 88    URBAN CLOSE     P12 3ER      88, URBAN CLOSE, P12 3ER
          6000 50    TWIN STREET     K11 6TY      50, TWIN STREET, K11 6TY
          7000 34    DAIL AVENUE     D9 3DD       34, DAIL AVENUE, D9 3DD
          8000 45    ELM STREET      LS1 2EW      45, ELM STREET, LS1 2EW
          9000 25    PARK AVENUE     M10 4RT      25, PARK AVENU, M10 4RT
         10000 33    BRICK LANE      B12 2WQ      33, BRICK LANE, B12 2WQ
    
    10 rows selected.
    
    SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE user_ds_procedure
      2    (p_rowid IN           ROWID,
      3       p_clob     IN OUT NOCOPY CLOB)
      4  AS
      5  BEGIN
      6    FOR r IN
      7        (SELECT c.fname || ' ' || c.mname || ' ' || c.sname || ' '
      8             || p.address_label || ' ' data
      9         FROM      customers c, address_link al, property p
     10         WHERE  al.ROWID = p_rowid
     11         AND      al.prop_id = p.id
     12         AND      al.cus_id = c.id)
     13    LOOP
     14        DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r.data), r.data);
     15    END LOOP;
     16  END user_ds_procedure;
     17  /
    
    Procedure created.
    
    SCOTT@orcl_11gR2> SHOW ERRORS
    No errors.
    SCOTT@orcl_11gR2> BEGIN
      2    CTX_DDL.CREATE_PREFERENCE ('my_uds', 'USER_DATASTORE');
      3    CTX_DDL.SET_ATTRIBUTE ('my_uds', 'PROCEDURE', 'user_ds_procedure');
      4  END;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> ALTER TABLE address_link ADD (name_and_address  VARCHAR2(1))
      2  /
    
    Table altered.
    
    SCOTT@orcl_11gR2> CREATE INDEX text_index ON address_link (name_and_address)
      2  INDEXTYPE IS CTXSYS.CONTEXT
      3  PARAMETERS ('DATASTORE my_uds')
      4  /
    
    Index created.
    
    SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2(100)
    SCOTT@orcl_11gR2> EXEC :search_string := 'ripple'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> COLUMN name      FORMAT A30
    SCOTT@orcl_11gR2> COLUMN address FORMAT A30
    SCOTT@orcl_11gR2> SELECT c.title || ' ' || c.fname || ' ' || c.mname || ' ' || c.sname name,
      2           p.address_label address
      3  FROM   customers c, address_link al, property p
      4  WHERE  CONTAINS (al.name_and_address, parser.simplesearch (:search_string)) > 0
      5  AND    al.prop_id = p.id
      6  AND    al.cus_id = c.id
      7  /
    
    NAME                           ADDRESS
    ------------------------------ ------------------------------
    MRS LEENA  RIPPLE              50, TWIN STREET, K11 6TY
    MRS LEENA  RIPPLE              34, DAIL AVENUE, D9 3DD
    MRS LEENA  RIPPLE              45, ELM STREET, LS1 2EW
    
    3 rows selected.
    
    SCOTT@orcl_11gR2> EXEC :search_string := 'elm'
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> /
    
    NAME                           ADDRESS
    ------------------------------ ------------------------------
    MRS MINI MICKEY MOUSE          12, ELM STREET, LS1 2EW
    MRS LEENA  RIPPLE              45, ELM STREET, LS1 2EW
    
    2 rows selected.
  • 6. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    Yes, that makes sense. I hadn't considered multiple people at one address.
  • 7. Re: Newbie to Oracle Text
    AshPatel2 Newbie
    Currently Being Moderated
    Hi thanks so far for your help feel a bit dumb I have followed what Barbara suggested and when I run the query I get the following not sure which steps I have missed or what I am doing wrong. Also there are no triggers created on any of the tables. Can you help.

    SELECT c.title || ' ' || c.fname || ' ' || c.mname || ' ' || c.sname name,
    p.address_label address
    FROM customers c, address_link al, property p
    WHERE CONTAINS (al.name_and_address, parser.simplesearch ('RIPPLE')) > 0
    AND al.prop_id = p.id
    AND al.cus_id = c.id


    ORA-29900: operator binding does not exist
    ORA-06553: PLS-307: too many declarations of 'CONTAINS' match this call
  • 8. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    Hmm, are you using 10g?

    In 10g you couldn't use a function in the contains clause, because the kernel wasn't sure where to treat the return value as a VARCHAR, CLOB or LONG (or something like that). It should work OK in 11, or at least 11.2.

    The workaround is to put the output of "parser.simplesearch" into a known variable type, eg in SQL*Plus you can do:
    variable querystring varchar2(4000)
    
    execute :querystring := parser.simplesearch('RIPPLE')
    
    SELECT c.title || ' ' || c.fname || ' ' || c.mname || ' ' || c.sname name,
    p.address_label address
    FROM customers c, address_link al, property p
    WHERE CONTAINS (al.name_and_address, :querystring) > 0
    AND al.prop_id = p.id
    AND al.cus_id = c.id
    I'm assuming you have actually compiled the "parser" package before running this.
  • 9. Re: Newbie to Oracle Text
    AshPatel2 Newbie
    Currently Being Moderated
    Hi Roger yes using 10g at moment but your workaround did work so thank you. Just some more questions really, if I enter 'LEENA' as my search it works perfectly, if I enter 'LEENA RIPPLE' works perfect but if I enter 'LEENA R' it does not bring back any result. Do wild cards come into play at some point for true google search. I have also noticed if I enter 'LEENA K11 6TY' it brings back all 3 addresses for Leena instead of one record with that postcode, its like it ignores the postcode.


    Regards
    Ash

    Edited by: AshPatel2 on Aug 23, 2012 3:14 PM
  • 10. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    Yes, if you want partial matches you will need to use wildcards - for parser.simplesearch the default wildcard is "*". If you're using the CONTAINS clause directly it's %.

    It's worth noting that Google doesn't allow wild cards at all!
  • 11. Re: Newbie to Oracle Text
    AshPatel2 Newbie
    Currently Being Moderated
    True google does not use wildcards. I have also noticed if I enter 'LEENA K11 6TY' it brings back all 3 addresses for Leena instead of one record with that postcode, its like it ignores the postcode, is that what is suppose to happen. Also whichever postcode I enter for Leena e.g. 'LEENA D9 3DD' or 'LEENA K11 6TY' , the result doesnt order it by relevance of what I have entered as it orders the result the same everytime, I was expecting if I enterd 'LEENA D9 3DD' the first record would be the address with the postcode D9 3DD but instead the order is always the same. I think I might be missing something.


    Thanks

    Regards
    Ash
  • 12. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    You'll need to order by score to get the best matches first:
    SELECT c.title || ' ' || c.fname || ' ' || c.mname || ' ' || c.sname name,
    p.address_label address
    FROM customers c, address_link al, property p
    WHERE CONTAINS (al.name_and_address, :querystring, 42) > 0
    AND al.prop_id = p.id
    AND al.cus_id = c.id
    ORDER BY score(42) DESC
    Note we've added an extra argument 42 to the contains clause, and referenced that in the score (it can be any number - it's conventional to use 1 - it's just there so the SCORE and the CONTAINS match up if there is more than 1 CONTAINS clause).

    You can also include score(1) in the select list if you want to see the values.
  • 13. Re: Newbie to Oracle Text
    AshPatel2 Newbie
    Currently Being Moderated
    Thanks Roger its working perfectly now. One last thing, I have put the index on the address link as suggested by Barbara and you agreed. The only thing I am missing is when I enter a new record or update a record I cannot seem to find the record. Users will be inserting and updating the records and will also need to search on them straight after inserting or updating the records. Is this possible.

    Thanks

    Regards
    Ash
  • 14. Re: Newbie to Oracle Text
    Roger Ford Expert
    Currently Being Moderated
    Are you aware that you need to SYNC the index? You can do that using ctx_ddl.sync_index('indexname') or you can use a SYNC parameter in the create index. You can use SYNC(ON COMMIT) but that will cause fragmentation of the index if you do lots of updates - you'll need to use ctx_ddl.optimize_index to fix it.

    +"Users will be inserting and updating the records and will also need to search on them straight after inserting or updating the records"+

    Do you mean straight after inserting or updating, or straight after committing their changes. If they expect to be able to do a text search before committing, you will have to use the TRANSACTIONAL keyword. This is not recommended, as it forces a scan on any unsync'd documents. Use TRANSACTIONAL (if you must) ONLY with SYNC(ON COMMIT) or things will get very slow.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points