1 2 Previous Next 17 Replies Latest reply: Aug 1, 2014 5:18 PM by n_shah18 RSS

    Index empty-Name searching logic.

    n_shah18

      Hi All

      Using oracle 11gR2

      I am trying to implement search filed in my app which is dependent  2 table for now and eventually 4-5 .

      Basically search the DB for matches on name , DOB, Phone , address

       

      sample data

       

      Craig T Boker 26-OCT-60 12 N. York    Altus Oklahoma 73521 Jackson 500 379-4575
      Dale Bark 07-JUN-62 125 W. Vine    Poplar Bluff Minnesota 63901 Butler 5736864800
      Linny Eugene Newberry 02-FEB-61 61 Hickory   Independence Kansas 67301 Montgomery 620-988-0101
      Phillip Enbe Caroll 10-JAN-57 531 State Hwy 165   Branson Missouri 65616 Taney
      Bri Eugene Ashley 07-JAN-78 72 Bateswood #7   Houston Texas 77079
      Edwardo asillas 20-SEP-74 152 A Street    Floresville Texas 78114 Wilson
      Donald Bernard Sanfrd 16-JUL-78 Rt 2, Box 80-A   Clarksburg West Virginia 26301
      Paul Hoard Smith 17-SEP-43 298 E. Gate Dr.   Seminole Oklahoma 74868 Seminole

      This is derived data from two table as follows

       

      SELECT sot.FIRST_NAME,

        sot.MIDDLE_NAME,

        sot.LAST_NAME,

        sot.DATE_OF_BIRTH,

        slt.ADDRESS1 ||' '|| nvl( slt.ADDRESS2,' ')|| ' '||  slt.CITY|| ' '||  (SELECT sc.DESCRIPTION FROM sor_code sc WHERE sc.CODE_ID = slt.STATE  )  ||' '||   slt.ZIP|| ' '||  slt.COUNTY as address,

      nvl( slt.PHONE_NUMBER,' ')

      FROM sor_offen_text sot,

        sor_location_text slt

      WHERE sot.OFFEN_ID  = slt.OFFEN_ID

      AND (upper(slt.STATUS) = 'VERIFIED')

       

      I followed the example on Using Oracle Text Name Search

      This Is what I did but it returns no data.. any help is appreciated.



      create or replace procedure offen_text_proc
         (rid in rowid, tlob in out nocopy clob) is
           tag varchar2(30);
           phone varchar2(30);
      begin
        for c1 in (SELECT sot.FIRST_NAME,
                          sot.MIDDLE_NAME,
                          sot.LAST_NAME,
                          sot.DATE_OF_BIRTH,
                          slt.ADDRESS1 ||' '|| nvl( slt.ADDRESS2,' ')|| ' '||  slt.CITY|| ' '||  (SELECT sc.DESCRIPTION FROM sor_code sc WHERE sc.CODE_ID = slt.STATE  )  ||' '||   slt.ZIP|| ' '||  slt.COUNTY as address,
                          nvl( slt.PHONE_NUMBER,' ')
                          FROM sor_offen_text sot,
                            sor_location_text slt
                          WHERE sot.OFFEN_ID  = slt.OFFEN_ID
                          AND (upper(slt.STATUS) = 'VERIFIED'))
        loop
         tag :='<fullname>';
           dbms_lob.writeappend(tlob, length(tag), tag);
           if (c1.FIRST_NAME is not null) then
             dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);
             dbms_lob.writeappend(tlob, length(' '), ' ');
           end if;
           if (c1.MIDDLE_NAME is not null) then
             dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);
             dbms_lob.writeappend(tlob, length(' '), ' ');
           end if;
           if (c1.LAST_NAME is not null) then
             dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);
           end if;
           tag :='</fullname>';
           dbms_lob.writeappend(tlob, length(tag), tag);
        
         tag :='<dob>';
           dbms_lob.writeappend(tlob, length(tag), tag);
           if (c1.DATE_OF_BIRTH is not null) then
             DATE_OF_BIRTH := nvl(REGEXP_SUBSTR(c1.DATE_OF_BIRTH, '\d\d\d\d($|\s)'), ' ');
             dbms_lob.writeappend(tlob, length(DATE_OF_BIRTH), phone);
           end if;
           tag :='</dob>';
           dbms_lob.writeappend(tlob, length(tag), tag);
          
          tag :='<address>';
           dbms_lob.writeappend(tlob, length(tag), tag);
           if (c1.address is not null) then
             address := nvl(REGEXP_SUBSTR(c1.address, '\d\d\d\d($|\s)'), ' ');
             dbms_lob.writeappend(tlob, length(address), address);
           end if;
           tag :='</address>';
           dbms_lob.writeappend(tlob, length(tag), tag);
          
          
           tag :='<phone>';
           dbms_lob.writeappend(tlob, length(tag), tag);
             
           if (c1.PHONE is not null) then
             phone := nvl(REGEXP_SUBSTR(c1.PHONE, '\d\d\d\d($|\s)'), ' ');
             dbms_lob.writeappend(tlob, length(phone), phone);
           end if;
           tag :='</phone>';
           dbms_lob.writeappend(tlob, length(tag), tag);
         
         end loop;
        end;
        /
      
        exec ctx_ddl.drop_preference('offen_text_proc');
      begin
        ctx_ddl.create_preference('offen_text_proc', 'user_datastore');
        ctx_ddl.set_attribute('offen_text_proc', 'procedure', 'offen_text_proc');
        ctx_ddl.set_attribute('offen_text_proc', 'output_type', 'CLOB');
      end;
      /
      
      exec ctx_ddl.drop_section_group('namegroup');
      begin
        ctx_ddl.create_section_group('namegroup', 'BASIC_SECTION_GROUP');
        ctx_ddl.add_ndata_section('namegroup', 'fullname', 'fullname');
        ctx_ddl.add_ndata_section('namegroup', 'phone', 'phone');
        ctx_ddl.add_ndata_section('namegroup', 'address', 'address');
        ctx_ddl.add_ndata_section('namegroup', 'dob', 'dob');
      end;
      /
      
      
      
      
      
      exec ctx_ddl.drop_preference('ndata_wl');
      begin
         ctx_ddl.create_preference('NDATA_WL', 'BASIC_WORDLIST');
         ctx_ddl.set_attribute('NDATA_WL', 'NDATA_ALTERNATE_SPELLING', 'FALSE');
         ctx_ddl.set_attribute('NDATA_WL', 'NDATA_BASE_LETTER', 'TRUE');
          ctx_ddl.set_attribute('NDATA_WL', 'NDATA_JOIN_PARTICLES',
          'de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');
      end;
      /
      
      
      create index name_idx on sor_offender_text(first_name) indextype is ctxsys.context
      parameters ('datastore offen_text_proc section group namegroup wordlist ndata_wl  memory 500M');
      /
      
      select first_name, middle_name, last_name from
         (select /*+ FIRST_ROWS */
                first_name, middle_name, last_name,  score(1) scr
      FROM sor_offen_text sot,
        sor_location_text slt
      WHERE sot.OFFEN_ID  = slt.OFFEN_ID
      AND (upper(slt.STATUS) = 'VERIFIED') and contains(first_name,
                ' ndata(address,'||:name||') OR
                  ndata(fullname, '||:name||') ',1)>0
          order by score(1) desc
         ) where rownum <= 10;
        
        
        
        
                          
        
        
      
      

      ore but

        • 1. Re: Index empty-Name searching logic.
          Barbara Boehmer

          Your procedure has errors.  Please see the demonstration with the corrected procedure below.  Also, your code seems to imply that your date_of_birth is varchar2 not date.  If it is date, then you need to apply to_char.  You did not include your data structure, so I had to guess.

           

           

          SCOTT@orcl12c> -- reverse engineered tables and data:

          SCOTT@orcl12c> CREATE TABLE sor_offen_text

            2    (offen_id       NUMBER,

            3      first_name     VARCHAR2(10),

            4      middle_name    VARCHAR2(11),

            5      last_name      VARCHAR2( 9),

            6      date_of_birth  VARCHAR2( 9))

            7  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO sor_offen_text VALUES

            3    (1, 'Craig', 'T', 'Boker', '26-OCT-60')

            4  SELECT * FROM DUAL

            5  /

           

          1 row created.

           

          SCOTT@orcl12c> CREATE TABLE sor_code

            2    (code_id      VARCHAR2(2),

            3      description  VARCHAR2(11))

            4  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO sor_code VALUES ('OK', 'Oklahoma')

            3  SELECT * FROM DUAL

            4  /

           

          1 row created.

           

          SCOTT@orcl12c> CREATE TABLE sor_location_text

            2    (offen_id      NUMBER,

            3      status          VARCHAR2( 8),

            4      address1      VARCHAR2(17),

            5      address2      VARCHAR2( 8),

            6      city          VARCHAR2(12),

            7      state          VARCHAR2( 2),

            8      zip          NUMBER,

            9      county          VARCHAR2(10),

          10      phone_number  VARCHAR2(12))

          11  /

           

          Table created.

           

          SCOTT@orcl12c> INSERT ALL

            2  INTO sor_location_text VALUES

            3    (1, 'VERIFIED', '12 N. York', NULL, 'Altus', 'OK', 73521, 'Jackson', '500 379-4575')

            4  SELECT * FROM DUAL

            5  /

           

          1 row created.

           

          SCOTT@orcl12c> -- corrected procedure (see comments along left edge):

          SCOTT@orcl12c> create or replace procedure offen_text_proc

            2      (rid in rowid, tlob in out nocopy clob) is

            3        tag varchar2(30);

            4        phone varchar2(30);

            5  -- added variables:

            6        date_of_birth varchar2(9);

            7        address    varchar2(100);

            8  begin

            9    for c1 in (SELECT sot.FIRST_NAME,

          10               sot.MIDDLE_NAME,

          11               sot.LAST_NAME,

          12               sot.DATE_OF_BIRTH,

          13               slt.ADDRESS1 ||' '|| nvl( slt.ADDRESS2,' ')|| ' '||  slt.CITY|| ' '||    (SELECT sc.DESCRIPTION FROM sor_code sc WHERE sc.CODE_ID = slt.STATE  )  ||' '||   slt.ZIP|| ' '||  slt.COUNTY as address,

          14               nvl( slt.PHONE_NUMBER,' ')

          15  -- added alias:

          16                 AS phone

          17               FROM sor_offen_text sot,

          18                 sor_location_text slt

          19               WHERE sot.OFFEN_ID  = slt.OFFEN_ID

          20  -- added condition:

          21               AND sot.ROWID = rid

          22               AND (upper(slt.STATUS) = 'VERIFIED'))

          23    loop

          24      tag :='<fullname>';

          25        dbms_lob.writeappend(tlob, length(tag), tag);

          26        if (c1.FIRST_NAME is not null) then

          27          dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);

          28          dbms_lob.writeappend(tlob, length(' '), ' ');

          29        end if;

          30        if (c1.MIDDLE_NAME is not null) then

          31          dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);

          32          dbms_lob.writeappend(tlob, length(' '), ' ');

          33        end if;

          34        if (c1.LAST_NAME is not null) then

          35          dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);

          36        end if;

          37        tag :='</fullname>';

          38        dbms_lob.writeappend(tlob, length(tag), tag);

          39

          40      tag :='<dob>';

          41        dbms_lob.writeappend(tlob, length(tag), tag);

          42        if (c1.DATE_OF_BIRTH is not null) then

          43          DATE_OF_BIRTH := nvl(REGEXP_SUBSTR(c1.DATE_OF_BIRTH, '\d\d\d\d($|\s)'), ' ');

          44  --       dbms_lob.writeappend(tlob, length(DATE_OF_BIRTH), phone);

          45  -- changed line above to line below

          46          dbms_lob.writeappend(tlob, length(DATE_OF_BIRTH), DATE_OF_BIRTH);

          47        end if;

          48        tag :='</dob>';

          49        dbms_lob.writeappend(tlob, length(tag), tag);

          50

          51       tag :='<address>';

          52        dbms_lob.writeappend(tlob, length(tag), tag);

          53        if (c1.address is not null) then

          54          address := nvl(REGEXP_SUBSTR(c1.address, '\d\d\d\d($|\s)'), ' ');

          55          dbms_lob.writeappend(tlob, length(address), address);

          56        end if;

          57        tag :='</address>';

          58        dbms_lob.writeappend(tlob, length(tag), tag);

          59

          60

          61        tag :='<phone>';

          62        dbms_lob.writeappend(tlob, length(tag), tag);

          63

          64        if (c1.PHONE is not null) then

          65          phone := nvl(REGEXP_SUBSTR(c1.PHONE, '\d\d\d\d($|\s)'), ' ');

          66          dbms_lob.writeappend(tlob, length(phone), phone);

          67        end if;

          68        tag :='</phone>';

          69        dbms_lob.writeappend(tlob, length(tag), tag);

          70

          71      end loop;

          72    end;

          73  /

           

          Procedure created.

           

          SCOTT@orcl12c> SHOW ERRORS

          No errors.

          SCOTT@orcl12c> -- preferences you provided:

          SCOTT@orcl12c> begin

            2    ctx_ddl.create_preference('offen_text_proc', 'user_datastore');

            3    ctx_ddl.set_attribute('offen_text_proc', 'procedure', 'offen_text_proc');

            4    ctx_ddl.set_attribute('offen_text_proc', 'output_type', 'CLOB');

            5  end;

            6  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> begin

            2    ctx_ddl.create_section_group('namegroup', 'BASIC_SECTION_GROUP');

            3    ctx_ddl.add_ndata_section('namegroup', 'fullname', 'fullname');

            4    ctx_ddl.add_ndata_section('namegroup', 'phone', 'phone');

            5    ctx_ddl.add_ndata_section('namegroup', 'address', 'address');

            6    ctx_ddl.add_ndata_section('namegroup', 'dob', 'dob');

            7  end;

            8  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> begin

            2      ctx_ddl.create_preference('NDATA_WL', 'BASIC_WORDLIST');

            3      ctx_ddl.set_attribute('NDATA_WL', 'NDATA_ALTERNATE_SPELLING', 'FALSE');

            4      ctx_ddl.set_attribute('NDATA_WL', 'NDATA_BASE_LETTER', 'TRUE');

            5       ctx_ddl.set_attribute('NDATA_WL', 'NDATA_JOIN_PARTICLES',

            6       'de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');

            7  end;

            8  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> -- index you provided

          SCOTT@orcl12c> -- (changed table name sor_offender_text to sor_offen_text

          SCOTT@orcl12c> --  to match table name used in the rest of your code):

          SCOTT@orcl12c> create index name_idx on sor_offen_text(first_name) indextype is ctxsys.context

            2  parameters ('datastore offen_text_proc section group namegroup wordlist ndata_wl  memory 500M')

            3  /

           

          Index created.

           

          SCOTT@orcl12c> -- variable and value:

          SCOTT@orcl12c> VARIABLE name VARCHAR2(100)

          SCOTT@orcl12c> EXEC :name := 'Boker Craig'

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> -- query you provided:

          SCOTT@orcl12c> select first_name, middle_name, last_name from

            2      (select /*+ FIRST_ROWS */

            3             first_name, middle_name, last_name,  score(1) scr

            4  FROM sor_offen_text sot,

            5    sor_location_text slt

            6  WHERE sot.OFFEN_ID  = slt.OFFEN_ID

            7  AND (upper(slt.STATUS) = 'VERIFIED') and contains(first_name,

            8             ' ndata(address,'||:name||') OR

            9           ndata(fullname, '||:name||') ',1)>0

          10       order by score(1) desc

          11      ) where rownum <= 10

          12  /

           

          FIRST_NAME MIDDLE_NAME LAST_NAME

          ---------- ----------- ---------

          Craig      T           Boker

           

          1 row selected.

          • 2. Re: Index empty-Name searching logic.
            n_shah18

            Thanks for replying and helping me out this issue..

            question: Regarding address , DOB and PHONE# . Seem like it does not  like '-'

             

            Should I change this data store  to MULTI_COLUMN_DATASTORE use a function to replace '-' to '/-' or skipjoin on '-' (which is the best as your experience)

             

            I' m not following the   select nvl(REGEXP_SUBSTR('123-564-7894', '\d\d\d\d($|\s)'), ' ') from dual;

             

            used in the proc, only returns last 4 , which is stored in index right ?

             

            If a user search on 1235467894 or 123-546-7894 wont return any data but if user searches on 7894 then it return data.

            also Searching on dallas or Oklahoma wont return any data.. address is just working on number only.

             

            How to make it more flexible?

             

            ps DOB is a date column in database.

             

            Many thanks

            Appreciate your time

            Neel.

            • 3. Re: Index empty-Name searching logic.
              Barbara Boehmer

              In my first response, I just changed enough to make it run, but didn't look it over closely.  I can now see that you copied the odd code using regexp_substr from the link that you provided and inappropriately applied it to the date_of_birth and address sections, as well as the phone section.  As you noted, it returns any four digits before a space or the end of the string.  I don't know what they had in mind when they put that in the documentation example.  In the following revision, I have removed all of that regexp_substr stuff and the variables that went with it and applied to_char to your date_of_birth column, since it is a date, as it should be.  As far as whether or not you want to declare the hyphen as a printjoin or skipjoin or modify it in the procedure or leave it as is, depends on how you want to search.  I would be inclined to leave it as is in the data, but not use any hyphens in the search.  Any hyphens in the data will be considered break characters between tokens, like a space, but using ndata will search with or without a space anyhow.  However, using the hyphen in the search would be considered as minus, which would subtract the score of the search criteria on the right from the search criteria on the left.  Please see the suggested revision below that seems to do what you want.  If not, then please provide specific examples of what criteria you want to search for and what you want to find and not find based on that search criteria.  I have included a section to show what the procedure produces.

               

               

              SCOTT@orcl12c> -- tables and data:

              SCOTT@orcl12c> CREATE TABLE sor_offen_text

                2    (offen_id       NUMBER,

                3      first_name     VARCHAR2(10),

                4      middle_name    VARCHAR2(11),

                5      last_name      VARCHAR2( 9),

                6      date_of_birth  DATE)

                7  /

               

              Table created.

               

              SCOTT@orcl12c> INSERT ALL

                2  INTO sor_offen_text VALUES

                3    (1, 'Craig', 'T', 'Boker', TO_DATE ('26-OCT-60', 'DD-MON-RR'))

                4  SELECT * FROM DUAL

                5  /

               

              1 row created.

               

              SCOTT@orcl12c> CREATE TABLE sor_code

                2    (code_id      VARCHAR2(2),

                3      description  VARCHAR2(11))

                4  /

               

              Table created.

               

              SCOTT@orcl12c> INSERT ALL

                2  INTO sor_code VALUES ('OK', 'Oklahoma')

                3  SELECT * FROM DUAL

                4  /

               

              1 row created.

               

              SCOTT@orcl12c> CREATE TABLE sor_location_text

                2    (offen_id      NUMBER,

                3      status          VARCHAR2( 8),

                4      address1      VARCHAR2(17),

                5      address2      VARCHAR2( 8),

                6      city          VARCHAR2(12),

                7      state          VARCHAR2( 2),

                8      zip          NUMBER,

                9      county          VARCHAR2(10),

              10      phone_number  VARCHAR2(12))

              11  /

               

              Table created.

               

              SCOTT@orcl12c> INSERT ALL

                2  INTO sor_location_text VALUES

                3    (1, 'VERIFIED', '12 N. York', NULL, 'Altus', 'OK', 73521, 'Jackson', '500 379-4575')

                4  SELECT * FROM DUAL

                5  /

               

              1 row created.

               

              SCOTT@orcl12c> -- procedure:

              SCOTT@orcl12c> create or replace procedure offen_text_proc

                2      (rid in rowid, tlob in out nocopy clob) is

                3        tag varchar2(30);

                4  -- removed unnecessary variables

                5  begin

                6    for c1 in (SELECT sot.FIRST_NAME,

                7               sot.MIDDLE_NAME,

                8               sot.LAST_NAME,

                9               sot.DATE_OF_BIRTH,

              10               slt.ADDRESS1 ||' '|| nvl( slt.ADDRESS2,' ')|| ' '||  slt.CITY|| ' '||    (SELECT sc.DESCRIPTION FROM sor_code sc WHERE sc.CODE_ID = slt.STATE  )  ||' '||   slt.ZIP|| ' '||  slt.COUNTY as address,

              11               nvl( slt.PHONE_NUMBER,' ') AS phone

              12               FROM sor_offen_text sot,

              13                 sor_location_text slt

              14               WHERE sot.OFFEN_ID  = slt.OFFEN_ID

              15               AND sot.ROWID = rid

              16               AND (upper(slt.STATUS) = 'VERIFIED'))

              17    loop

              18      tag :='<fullname>';

              19        dbms_lob.writeappend(tlob, length(tag), tag);

              20        if (c1.FIRST_NAME is not null) then

              21          dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);

              22          dbms_lob.writeappend(tlob, length(' '), ' ');

              23        end if;

              24        if (c1.MIDDLE_NAME is not null) then

              25          dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);

              26          dbms_lob.writeappend(tlob, length(' '), ' ');

              27        end if;

              28        if (c1.LAST_NAME is not null) then

              29          dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);

              30        end if;

              31        tag :='</fullname>';

              32        dbms_lob.writeappend(tlob, length(tag), tag);

              33

              34      tag :='<dob>';

              35        dbms_lob.writeappend(tlob, length(tag), tag);

              36  -- changed if..end if section below:

              37        if (c1.DATE_OF_BIRTH is not null) then

              38          dbms_lob.writeappend

              39            (tlob,

              40             length(TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY')),

              41             TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY'));

              42        end if;

              43        tag :='</dob>';

              44        dbms_lob.writeappend(tlob, length(tag), tag);

              45

              46       tag :='<address>';

              47        dbms_lob.writeappend(tlob, length(tag), tag);

              48  -- changed if..end if section below:

              49        if (c1.address is not null) then

              50          dbms_lob.writeappend(tlob, length(c1.address), c1.address);

              51        end if;

              52        tag :='</address>';

              53        dbms_lob.writeappend(tlob, length(tag), tag);

              54

              55        tag :='<phone>';

              56        dbms_lob.writeappend(tlob, length(tag), tag);

              57  -- changed if..end if section below:

              58        if (c1.PHONE is not null) then

              59          dbms_lob.writeappend(tlob, length(c1.phone), c1.phone);

              60        end if;

              61        tag :='</phone>';

              62        dbms_lob.writeappend(tlob, length(tag), tag);

              63

              64      end loop;

              65    end;

              66  /

               

              Procedure created.

               

              SCOTT@orcl12c> SHOW ERRORS

              No errors.

              SCOTT@orcl12c> -- show what offen_text_proc procedure produces:

              SCOTT@orcl12c> SET SERVEROUTPUT ON

              SCOTT@orcl12c> DECLARE

                2    v_clob  CLOB;

                3  BEGIN

                4    DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);

                5    FOR r IN (SELECT ROWID rid FROM sor_offen_text) LOOP

                6       offen_text_proc (r.rid, v_clob);

                7       DBMS_OUTPUT.PUT_LINE (v_clob);

                8    END LOOP;

                9    DBMS_LOB.FREETEMPORARY (v_clob);

              10  END;

              11  /

              <fullname>Craig T Boker</fullname><dob>26-OCT-1960</dob><address>12 N. York   Altus Oklahoma 73521

              Jackson</address><phone>500 379-4575</phone>

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c>

              SCOTT@orcl12c> -- preferences:

              SCOTT@orcl12c> begin

                2    ctx_ddl.create_preference('offen_text_proc', 'user_datastore');

                3    ctx_ddl.set_attribute('offen_text_proc', 'procedure', 'offen_text_proc');

                4    ctx_ddl.set_attribute('offen_text_proc', 'output_type', 'CLOB');

                5  end;

                6  /

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> begin

                2    ctx_ddl.create_section_group('namegroup', 'BASIC_SECTION_GROUP');

                3    ctx_ddl.add_ndata_section('namegroup', 'fullname', 'fullname');

                4    ctx_ddl.add_ndata_section('namegroup', 'phone', 'phone');

                5    ctx_ddl.add_ndata_section('namegroup', 'address', 'address');

                6    ctx_ddl.add_ndata_section('namegroup', 'dob', 'dob');

                7  end;

                8  /

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> begin

                2      ctx_ddl.create_preference('NDATA_WL', 'BASIC_WORDLIST');

                3      ctx_ddl.set_attribute('NDATA_WL', 'NDATA_ALTERNATE_SPELLING', 'FALSE');

                4      ctx_ddl.set_attribute('NDATA_WL', 'NDATA_BASE_LETTER', 'TRUE');

                5       ctx_ddl.set_attribute('NDATA_WL', 'NDATA_JOIN_PARTICLES',

                6       'de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');

                7  end;

                8  /

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> -- index:

              SCOTT@orcl12c> create index name_idx on sor_offen_text(first_name) indextype is ctxsys.context

                2  parameters ('datastore offen_text_proc section group namegroup wordlist ndata_wl  memory 500M')

                3  /

               

              Index created.

               

              SCOTT@orcl12c> -- variables:

              SCOTT@orcl12c> VARIABLE name    VARCHAR2(100)

              SCOTT@orcl12c> VARIABLE address VARCHAR2(100)

              SCOTT@orcl12c> VARIABLE phone    VARCHAR2(100)

              SCOTT@orcl12c> -- queries with different variable values:

              SCOTT@orcl12c> EXEC :name := 'Boker Craig'

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> select * from

                2      (select /*+ FIRST_ROWS */ sot.*, slt.state, slt.phone_number, score(1) scr

                3  FROM sor_offen_text sot,

                4    sor_location_text slt

                5  WHERE sot.OFFEN_ID  = slt.OFFEN_ID

                6  AND (upper(slt.STATUS) = 'VERIFIED') and contains(first_name,

                7             ' ndata(fullname,'||:name||')',1)>0

                8       order by score(1) desc

                9      ) where rownum <= 10

              10  /

               

                OFFEN_ID FIRST_NAME MIDDLE_NAME LAST_NAME DATE_OF_BIRTH   ST PHONE_NUMBER        SCR

              ---------- ---------- ----------- --------- --------------- -- ------------ ----------

                       1 Craig      T           Boker     Wed 26-Oct-1960 OK 500 379-4575        100

               

              1 row selected.

               

              SCOTT@orcl12c> -- queries with different variable values:

              SCOTT@orcl12c> EXEC :address := 'Oklahoma'

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> select * from

                2      (select /*+ FIRST_ROWS */ sot.*, slt.state, slt.phone_number, score(1) scr

                3  FROM sor_offen_text sot,

                4    sor_location_text slt

                5  WHERE sot.OFFEN_ID  = slt.OFFEN_ID

                6  AND (upper(slt.STATUS) = 'VERIFIED') and contains(first_name,

                7             ' ndata(address,'||:address||')',1)>0

                8       order by score(1) desc

                9      ) where rownum <= 10

              10  /

               

                OFFEN_ID FIRST_NAME MIDDLE_NAME LAST_NAME DATE_OF_BIRTH   ST PHONE_NUMBER        SCR

              ---------- ---------- ----------- --------- --------------- -- ------------ ----------

                       1 Craig      T           Boker     Wed 26-Oct-1960 OK 500 379-4575        100

               

              1 row selected.

               

              SCOTT@orcl12c> EXEC :phone := '500 379 4575'

               

              PL/SQL procedure successfully completed.

               

              SCOTT@orcl12c> select * from

                2      (select /*+ FIRST_ROWS */ sot.*, slt.state, slt.phone_number, score(1) scr

                3  FROM sor_offen_text sot,

                4    sor_location_text slt

                5  WHERE sot.OFFEN_ID  = slt.OFFEN_ID

                6  AND (upper(slt.STATUS) = 'VERIFIED') and contains(first_name,

                7             ' ndata(phone,'||:phone||')',1)>0

                8       order by score(1) desc

                9      ) where rownum <= 10

              10  /

               

                OFFEN_ID FIRST_NAME MIDDLE_NAME LAST_NAME DATE_OF_BIRTH   ST PHONE_NUMBER        SCR

              ---------- ---------- ----------- --------- --------------- -- ------------ ----------

                       1 Craig      T           Boker     Wed 26-Oct-1960 OK 500 379-4575         85

               

              1 row selected.

               

              SCOTT@orcl12c> select * from

                2      (select /*+ FIRST_ROWS */ sot.*, slt.state, slt.phone_number, score(1) scr

                3  FROM sor_offen_text sot,

                4    sor_location_text slt

                5  WHERE sot.OFFEN_ID  = slt.OFFEN_ID

                6  AND (upper(slt.STATUS) = 'VERIFIED') and contains(first_name,

                7             ' ndata(fullname,'||:name||') OR

                8           ndata(address,'||:address||') OR

                9           ndata(phone,'||:phone||')',1)>0

              10       order by score(1) desc

              11      ) where rownum <= 10

              12  /

               

                OFFEN_ID FIRST_NAME MIDDLE_NAME LAST_NAME DATE_OF_BIRTH   ST PHONE_NUMBER        SCR

              ---------- ---------- ----------- --------- --------------- -- ------------ ----------

                       1 Craig      T           Boker     Wed 26-Oct-1960 OK 500 379-4575        100

               

              1 row selected.

              • 4. Re: Index empty-Name searching logic.
                n_shah18

                Thanks Barbara.

                This will work for now.

                Requirement changed at our end. Phone # and DOB is not needed any more, but this is a good start..

                Thanks for your time and effort

                appreciated it.

                 

                Regards

                Neel

                • 5. Re: Re: Index empty-Name searching logic.
                  n_shah18

                  Hi Barabara

                   

                  Need your help on this pls

                  I keep on getting error when try to run the search query.

                   

                   

                   

                   

                  sample data

                  John mayor 12/07/1983 123456789 5 4

                  Juen major 01/07/1963 127846789 4 2

                   

                   

                  create or replace procedure text_proc_N

                     (rid in rowid, tlob in out nocopy clob) is

                     tag varchar2(30);

                    

                  begin

                    for c1 in (select LAST_NAME, first_name , middle_name ,dob  DATE_OF_BIRTH , ssn , race_type_id race ,gender_type_id gender 

                                  from  cch_jb.person_attribute1  pat

                                  where pat.rowid=rid                           )

                                                 

                      loop

                        tag :='<fullname>';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                          if (c1.FIRST_NAME is not null) then

                            dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);

                            dbms_lob.writeappend(tlob, length(' '), ' ');

                          end if;

                          if (c1.MIDDLE_NAME is not null) then

                            dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);

                            dbms_lob.writeappend(tlob, length(' '), ' ');

                          end if;

                          if (c1.LAST_NAME is not null) then

                            dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);

                          end if;

                          tag :='</fullname>';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                   

                          tag :='<dob>';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                    -- changed if..end if section below:

                          if (c1.DATE_OF_BIRTH is not null) then

                            dbms_lob.writeappend

                              (tlob,

                               length(TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY')),

                               TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY'));

                          end if;

                         tag :='</dob>';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                      

                         tag :='<ssn>';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                    -- changed if..end if section below:

                          if (c1.ssn is not null) then

                            dbms_lob.writeappend(tlob, length(c1.ssn), c1.ssn);

                          end if;

                          tag :='</ssn>';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                        

                           tag :='gender';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                          if (c1.gender is not null) then

                            dbms_lob.writeappend(tlob, length(c1.gender), c1.gender);

                          end if;

                    

                       tag :='</gender>';

                         dbms_lob.writeappend(tlob, length(tag), tag);

                        

                        

                           tag :='race';

                          dbms_lob.writeappend(tlob, length(tag), tag);

                        if (c1.race is not null) then

                           dbms_lob.writeappend(tlob, length(c1.race), c1.race);

                       end if;

                       tag :='</race>';

                         dbms_lob.writeappend(tlob, length(tag), tag);

                        

                         end loop;

                    end;

                   

                    exec ctx_ddl.drop_preference('text_proc');

                  begin

                    ctx_ddl.create_preference('text_proc', 'user_datastore');

                    ctx_ddl.set_attribute('text_proc', 'procedure', 'text_proc_N');

                    ctx_ddl.set_attribute('text_proc', 'output_type', 'CLOB');

                  end;

                  /

                   

                   

                  exec ctx_ddl.drop_section_group('namegroup');

                  begin

                    ctx_ddl.create_section_group('namegroup', 'BASIC_SECTION_GROUP');

                    ctx_ddl.add_ndata_section('namegroup', 'fullname', 'fullname');

                    ctx_ddl.add_ndata_section('namegroup', 'ssn', 'ssn');

                    ctx_ddl.add_ndata_section('namegroup', 'race', 'race');

                    ctx_ddl.add_ndata_section('namegroup', 'gender', 'gender');

                    ctx_ddl.add_ndata_section('namegroup', 'dob', 'dob');

                  end;

                  /

                   

                   

                  exec ctx_ddl.drop_preference('ndata_wl');

                  begin

                     ctx_ddl.create_preference('NDATA_WL', 'BASIC_WORDLIST');

                     ctx_ddl.set_attribute('NDATA_WL', 'NDATA_ALTERNATE_SPELLING', 'FALSE');

                     ctx_ddl.set_attribute('NDATA_WL', 'NDATA_BASE_LETTER', 'TRUE');

                      ctx_ddl.set_attribute('NDATA_WL', 'NDATA_JOIN_PARTICLES',

                      'de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');

                  end;

                  /

                   

                   

                   

                  create index text_idx_1 on cch_jb.person_attribute1  (first_name) indextype is ctxsys.context

                  parameters ('datastore text_proc section group namegroup wordlist ndata_wl  memory 500M SYNC (ON COMMIT)' );

                  /

                   

                  select  LAST_NAME, first_name , middle_name ,dob  DATE_OF_BIRTH , ssn , race_type_id race ,gender_type_id gender , score(1) scr
                      FROM person_attribute1 pa1
                      WHERE  
                          contains(first_name,
                                 ' ndata(fullname,'||:name||') OR ndata(dob,'||:dob||') OR ndata(gender,'||:dob||') OR ndata(race,'||:dob||') OR  ndata(ssn,'||:ssn||')',1)>0
                           order by score(1) desc;@
                  

                   

                   

                  I keep on getting error

                  ORA-29902: error in executing ODCIIndexStart() routine

                  ORA-20000: Oracle Text error:

                  DRG-50901: text query parser syntax error on line 1, column 17 

                   

                   

                   

                  but when I try

                  Just Ndata with fullname name string then it works fine.

                   

                   

                  kindly suggest.

                   

                   

                  Neel

                  • 6. Re: Re: Re: Index empty-Name searching logic.
                    Barbara Boehmer

                    I don't get the error that you are getting, but I don't have the variable values that you are searching for either.  You need to post those and a copy and a paste of a complete run, as I have below.  The only things that I changed, other than a little formatting so it was easier to read and analyze, is that you were searching for gender and race within dob, so I fixed that in the query.

                     


                    SCOTT@orcl12c> CREATE TABLE person_attribute1

                      2    (last_name    VARCHAR2(15),

                      3      first_name    VARCHAR2(15),

                      4      middle_name    VARCHAR2(15),

                      5      dob        DATE,

                      6      ssn        NUMBER,

                      7      race_type_id    NUMBER,

                      8      gender_type_id    NUMBER)

                      9  /

                     

                    Table created.

                     

                    SCOTT@orcl12c> INSERT ALL

                      2  INTO person_attribute1 (first_name, last_name, dob, ssn, race_type_id, gender_type_id)

                      3  VALUES ('John','mayor', TO_DATE ('12/07/1983', 'MM/DD/YYYY'), 123456789,5,4)

                      4  INTO person_attribute1 (first_name, last_name, dob, ssn, race_type_id, gender_type_id)

                      5  VALUES ('Juen', 'major', TO_DATE ('01/07/1963', 'MM/DD/YYYY'), 127846789,4,2)

                      6  SELECT * FROM DUAL

                      7  /

                     

                    2 rows created.

                     

                    SCOTT@orcl12c> create or replace procedure text_proc_N

                      2    (rid in rowid, tlob in out nocopy clob)

                      3  is

                      4    tag                varchar2(30);

                      5  begin

                      6    for c1 in

                      7      (select LAST_NAME, first_name , middle_name, dob DATE_OF_BIRTH, ssn,

                      8          race_type_id race, gender_type_id gender

                      9        from    person_attribute1  pat

                    10        where pat.rowid = rid)

                    11    loop

                    12      tag :='<fullname>';

                    13      dbms_lob.writeappend(tlob, length(tag), tag);

                    14      if (c1.FIRST_NAME is not null) then

                    15        dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);

                    16        dbms_lob.writeappend(tlob, length(' '), ' ');

                    17      end if;

                    18      if (c1.MIDDLE_NAME is not null) then

                    19        dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);

                    20        dbms_lob.writeappend(tlob, length(' '), ' ');

                    21      end if;

                    22      if (c1.LAST_NAME is not null) then

                    23        dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);

                    24      end if;

                    25      tag :='</fullname>';

                    26      dbms_lob.writeappend(tlob, length(tag), tag);

                    27      --

                    28      tag :='<dob>';

                    29      dbms_lob.writeappend(tlob, length(tag), tag);

                    30      if (c1.DATE_OF_BIRTH is not null) then

                    31          dbms_lob.writeappend

                    32            (tlob,

                    33            length(TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY')),

                    34            TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY'));

                    35      end if;

                    36      tag :='</dob>';

                    37      dbms_lob.writeappend(tlob, length(tag), tag);

                    38      --

                    39      tag :='<ssn>';

                    40      dbms_lob.writeappend(tlob, length(tag), tag);

                    41      if (c1.ssn is not null) then

                    42        dbms_lob.writeappend(tlob, length(c1.ssn), c1.ssn);

                    43      end if;

                    44      tag :='</ssn>';

                    45      dbms_lob.writeappend(tlob, length(tag), tag);

                    46      --

                    47      tag :='gender';

                    48      dbms_lob.writeappend(tlob, length(tag), tag);

                    49      if (c1.gender is not null) then

                    50        dbms_lob.writeappend(tlob, length(c1.gender), c1.gender);

                    51      end if;

                    52      tag :='</gender>';

                    53      dbms_lob.writeappend(tlob, length(tag), tag);

                    54      --

                    55      tag :='race';

                    56      dbms_lob.writeappend(tlob, length(tag), tag);

                    57      if (c1.race is not null) then

                    58        dbms_lob.writeappend(tlob, length(c1.race), c1.race);

                    59      end if;

                    60      tag :='</race>';

                    61      dbms_lob.writeappend(tlob, length(tag), tag);

                    62    end loop;

                    63  end text_proc_N;

                    64  /

                     

                    Procedure created.

                     

                    SCOTT@orcl12c> show errors

                    No errors.

                    SCOTT@orcl12c> begin

                      2        ctx_ddl.create_preference('text_proc', 'user_datastore');

                      3        ctx_ddl.set_attribute('text_proc', 'procedure', 'text_proc_N');

                      4        ctx_ddl.set_attribute('text_proc', 'output_type', 'CLOB');

                      5  end;

                      6  /

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> begin

                      2        ctx_ddl.create_section_group('namegroup', 'BASIC_SECTION_GROUP');

                      3        ctx_ddl.add_ndata_section('namegroup', 'fullname', 'fullname');

                      4        ctx_ddl.add_ndata_section('namegroup', 'ssn', 'ssn');

                      5        ctx_ddl.add_ndata_section('namegroup', 'race', 'race');

                      6        ctx_ddl.add_ndata_section('namegroup', 'gender', 'gender');

                      7        ctx_ddl.add_ndata_section('namegroup', 'dob', 'dob');

                      8  end;

                      9  /

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> begin

                      2          ctx_ddl.create_preference('NDATA_WL', 'BASIC_WORDLIST');

                      3          ctx_ddl.set_attribute('NDATA_WL', 'NDATA_ALTERNATE_SPELLING', 'FALSE');

                      4          ctx_ddl.set_attribute('NDATA_WL', 'NDATA_BASE_LETTER', 'TRUE');

                      5          ctx_ddl.set_attribute('NDATA_WL', 'NDATA_JOIN_PARTICLES',

                      6          'de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');

                      7  end;

                      8  /

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> create index text_idx_1 on person_attribute1  (first_name) indextype is ctxsys.context

                      2  parameters

                      3    ('datastore text_proc

                      4      section group namegroup

                      5      wordlist ndata_wl

                      6      memory 500M

                      7      SYNC (ON COMMIT)' )

                      8  /

                     

                    Index created.

                     

                    SCOTT@orcl12c> variable name    varchar2(30)

                    SCOTT@orcl12c> variable dob    varchar2(30)

                    SCOTT@orcl12c> variable gender    number

                    SCOTT@orcl12c> variable race    number

                    SCOTT@orcl12c> variable ssn    number

                    SCOTT@orcl12c> exec :name := 'John Mayor'

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> exec :dob := '07-12-1983'

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> exec :gender := 4

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> exec :race := 2

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> exec :ssn := 123456789

                     

                    PL/SQL procedure successfully completed.

                     

                    SCOTT@orcl12c> select  LAST_NAME, first_name , middle_name ,dob  DATE_OF_BIRTH , ssn , race_type_id race ,

                      2          gender_type_id gender , score(1) scr

                      3  FROM    person_attribute1 pa1

                      4  WHERE  contains

                      5            (first_name,

                      6          'ndata (fullname,' || :name    || ') OR

                      7          ndata (dob,'      || :dob    || ') OR

                      8          ndata (gender,'  || :gender  || ') OR

                      9          ndata (race,'      || :race    || ') OR

                    10          ndata (ssn,'      || :ssn    || ')',

                    11          1) > 0

                    12  order  by score(1) desc

                    13  /

                     

                    LAST_NAME      FIRST_NAME      MIDDLE_NAME    DATE_OF_BIRTH          SSN      RACE    GENDER        SCR

                    --------------- --------------- --------------- --------------- ---------- ---------- ---------- ----------

                    mayor          John            NULL            Wed 07-Dec-1983  123456789          5          4        100

                    major          Juen            NULL            Mon 07-Jan-1963  127846789          4          2        40

                     

                    2 rows selected.

                    • 7. Re: Index empty-Name searching logic.
                      n_shah18

                      Thank you for your reply

                       

                      I think I understand the problem, In your query you pass all the parameters, nothing is null, so that why you don't get error.

                      Also the way user will search from gui is first_name, last_name and ssn are mandatory field but other are optional.

                       

                      Regarding  my search query ..

                       

                      Input parameter

                      :name='AMES G'  -- looking  for james G

                      :dob=:'12/23/1970'

                      :gender=5

                      :race=2

                      :ssn=actual ssn

                      :

                       

                      select  LAST_NAME, first_name , middle_name ,dob  DATE_OF_BIRTH , ssn , race_type_id race ,gender_type_id gender , score(1) scr

                          FROM person_attribute1 pa1

                          WHERE

                              contains(first_name,

                                     ' ndata(fullname,'||:name||') OR

                                      ndata(dob,'||:dob||') OR ndata(gender,'||:gender||') OR ndata(race,'||:race||') OR

                                      ndata(ssn,'||:ssn||')',1)>0

                               order by score(1) desc

                       

                      Return the multiple row.

                       

                      When I take out any one the input string then it throws the error above.

                       

                       

                      also. researched about ACCUM function beside Ndata as POC.

                       

                       

                      here is my  try so far.. but I get the same error as above.

                       

                      BEGIN

                            CTX_DDL.DROP_PREFERENCE ('pref_person_attrib');

                        

                            commit;

                      END;

                      /

                       

                      BEGIN

                            CTX_DDL.CREATE_PREFERENCE ('pref_person_attrib', 'MULTI_COLUMN_DATASTORE');

                            CTX_DDL.SET_ATTRIBUTE('pref_person_attrib','COLUMNS','LAST_NAME,FIRST_NAME,MIDDLE_NAME,SSN,GENDER_OTEXT,RACE_OTEXT,DOB_OTEXT');

                            CTX_DDL.SET_ATTRIBUTE('pref_person_attrib','delimiter','NEWLINE');

                      commit;   

                      END;

                      /

                       

                       

                      ALTER TABLE PERSON_ATTRIBUTE

                      ADD (DUMMY  CHAR(1 BYTE) DEFAULT 1);

                       

                       

                      begin

                      ctx_ddl.drop_section_group ( group_name => 'my_section_group');

                      end;

                      /

                       

                      begin

                      ctx_ddl.create_section_group ( group_name => 'my_section_group' , group_type => 'basic_section_group' );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' , section_name => 'last_name', tag => 'last_name', visible => true );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' ,section_name => 'first_name', tag => 'first_name', visible => true );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' ,section_name => 'middle_name', tag => 'middle_name', visible => true );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' ,section_name => 'ssn', tag => 'ssn', visible => true );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' ,section_name => 'gender_otext', tag => 'gender_otext', visible => true );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' ,section_name => 'race_otext', tag => 'race_otext', visible => true );

                      ctx_ddl.add_field_section ( group_name => 'my_section_group' ,section_name => 'dob_otext', tag => 'dob_otext', visible => true );

                      end;

                      /

                       

                      drop INDEX IDX_PERSON_ATTRIB_name;

                       

                       

                       

                      CREATE INDEX IDX_PERSON_ATTRIB_name ON PERSON_ATTRIBUTE(dummy)

                      INDEXTYPE IS CTXSYS.CONTEXT

                      parameters ('DATASTORE pref_person_attrib filter ctxsys.null_filter section group my_section_group stoplist ctxsys.EMPTY_STOPLIST SYNC (ON COMMIT)');

                       

                       

                       

                       

                       

                       

                       

                      CREATE TABLE stem_tab (test_word  VARCHAR2 (4000));

                       

                       

                      BEGIN

                            CTX_DDL.CREATE_PREFERENCE ('stem_lex', 'BASIC_LEXER');

                            CTX_DDL.SET_ATTRIBUTE      ('stem_lex', 'INDEX_STEMS', 'ENGLISH');

                            CTX_DDL.SET_ATTRIBUTE      ('stem_lex', 'PRINTJOINS', '-');

                          END;

                          /

                       

                       

                      CREATE INDEX stem_tab_idx on stem_tab (test_word)

                          INDEXTYPE IS CTXSYS.CONTEXT

                          PARAMETERS ('LEXER stem_lex')

                          /

                       

                       

                      CREATE OR REPLACE FUNCTION get_root

                            (p_word IN VARCHAR2)

                            RETURN VARCHAR2

                          AS

                            v_word            VARCHAR2 (32767);

                          BEGIN

                            DELETE FROM stem_tab;

                            COMMIT;

                            INSERT INTO stem_tab (test_word) VALUES (p_word);

                           COMMIT;

                           EXECUTE IMMEDIATE 'ALTER INDEX stem_tab_idx REBUILD';

                           SELECT MIN (token_text)

                           INTO   v_word

                           FROM   dr$stem_tab_idx$i;

                           RETURN v_word;

                         EXCEPTION

                           WHEN NO_DATA_FOUND THEN RETURN p_word;

                           WHEN OTHERS THEN RETURN SQLERRM (SQLCODE);

                         END get_root;

                         /

                       

                       

                       

                       

                      select score(1) score,pa.*

                      from person_attribute pa

                      where contains(dummy,

                      '((%'||:lname||'% within LAST_NAME)*10 ACCUM (!'||:lname||' within LAST_NAME)*9 ACCUM (SYN('||:lname||',NAMESYN1) within LAST_NAME)*2)

                      and ((%'||:fname||'% within FIRST_NAME)*10 ACCUM (!'||:fname||' within FIRST_NAME)*9 ACCUM (SYN('||:fname||',NAMESYN1) within FIRST_NAME)*2)

                      ACCUM ((%'||:mname||'% within MIDDLE_NAME)*10 ACCUM (!'||:mname||' within MIDDLE_NAME)*9 ACCUM (SYN('||:mname||',NAMESYN1) within MIDDLE_NAME)*2)

                      and ((%'||:ssn1||'% within SSN)*10 ACCUM ($'||:ssn1||' within SSN)*9 ACCUM (%'||:ssn1||'% within SSN)*2)

                      ACCUM ((%'||:gendertext||'% within GENDER_OTEXT)*10 ACCUM (!'||:gendertext||' within GENDER_OTEXT)*9 ACCUM (SYN('||:gendertext||',NAMESYN1) within GENDER_OTEXT)*2)

                      ACCUM ((%'||:racet||'% within RACE_OTEXT)*10 ACCUM (!'||:racet||' within RACE_OTEXT)*9 ACCUM (SYN('||:racet||',NAMESYN1) within RACE_OTEXT)*2)

                      ACCUM ((%'||:dobt||'% within DOB_OTEXT)*10 ACCUM (!'||:dobt||' within DOB_OTEXT)*9 ACCUM (SYN('||:dobt||',NAMESYN1) within DOB_OTEXT)*2)

                      ',1)>0

                      order by score desc;

                       

                       

                       

                      I am trying to find the faster and better solution out of two.

                       

                      Kindly suggest from your experience

                       

                      Many thanks

                      Neel

                      • 8. Re: Re: Index empty-Name searching logic.
                        Barbara Boehmer

                        You can use NVL2 to allow for null parameter values as demonstrated below.

                         

                        SCOTT@orcl12c> exec :name := 'John Mayor'

                         

                        PL/SQL procedure successfully completed.

                         

                        SCOTT@orcl12c> exec :dob := null

                         

                        PL/SQL procedure successfully completed.

                         

                        SCOTT@orcl12c> exec :gender := null

                         

                        PL/SQL procedure successfully completed.

                         

                        SCOTT@orcl12c> exec :race := null

                         

                        PL/SQL procedure successfully completed.

                         

                        SCOTT@orcl12c> exec :ssn := 123456789

                         

                        PL/SQL procedure successfully completed.

                         

                        SCOTT@orcl12c> select  LAST_NAME, first_name , middle_name ,dob  DATE_OF_BIRTH , ssn , race_type_id race ,

                          2          gender_type_id gender , score(1) scr

                          3  FROM    person_attribute1 pa1

                          4  WHERE  contains

                          5            (first_name,

                          6          'ndata (fullname,' || :name || ')' ||

                          7          NVL2 (:dob, ' OR ndata (dob,'|| :dob || ')', NULL) ||

                          8          NVL2 (:gender, ' OR ndata (gender,' || :gender || ')', NULL) ||

                          9          NVL2 (:race, ' OR ndata (race,' || :race || ')', NULL) ||

                        10          ' OR ndata (ssn,' || :ssn || ')',

                        11          1) > 0

                        12  order  by score(1) desc

                        13  /

                         

                        LAST_NAME      FIRST_NAME      MIDDLE_NAME    DATE_OF_BIRTH          SSN      RACE    GENDER        SCR

                        --------------- --------------- --------------- --------------- ---------- ---------- ---------- ----------

                        mayor          John                            Wed 07-Dec-1983  123456789          5          4        100

                        major          Juen                            Mon 07-Jan-1963  127846789          4          2        40

                         

                        2 rows selected.

                        • 9. Re: Re: Index empty-Name searching logic.
                          Barbara Boehmer

                          As to which method is faster or better, you will have to test and compare on your system and see which is faster and still meets your needs.  In general, using just accum may be faster than ndata.  However, when you add wildcards to the accum method that will slow things down significantly, especially if you have leading and trailing wildcards on short terms.  You can also combine methods.  You may want to use a text search that returns a large result set using an index, then use something like utl_match.jaro_winkler smilarity to further limit and/or order the rows of the result set.

                          • 10. Re: Re: Re: Index empty-Name searching logic.
                            n_shah18

                            Hi Barbara

                            I have been testing this results Nand I having few issue with synomyns.

                            I added the thesaurus in my index but I am not getting expected result, I worked great on ROBERT and BOB but fails on WALT and WALTER.

                             

                            Pls see the my try below.

                             

                             

                            create or replace procedure text_proc_N

                                  (rid in rowid, tlob in out nocopy clob)

                                is

                                  tag                varchar2(30);

                                begin

                                  for c1 in

                                    (select LAST_NAME, first_name , middle_name, dob DATE_OF_BIRTH, ssn,

                                        race_type_id race, gender_type_id gender

                                      from    person_attribute1  pat

                                    where pat.rowid = rid)

                                loop

                                  tag :='<fname>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  if (c1.FIRST_NAME is not null) then

                                    dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);

                                    dbms_lob.writeappend(tlob, length(' '), ' ');

                                  end if;

                                  tag :='</fname>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                 

                                 

                                  tag :='<mname>';

                                  if (c1.MIDDLE_NAME is not null) then

                                    dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);

                                    dbms_lob.writeappend(tlob, length(' '), ' ');

                                  end if;

                                   tag :='</mname>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  

                                   tag :='<lname>';

                                  if (c1.LAST_NAME is not null) then

                                    dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);

                                  end if;

                                  tag :='</lname>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  --

                                  tag :='<dob>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  if (c1.DATE_OF_BIRTH is not null) then

                                      dbms_lob.writeappend(tlob,

                                        length(TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY')),

                                        TO_CHAR (c1.DATE_OF_BIRTH, 'DD-MON-YYYY'));

                                  end if;

                                  tag :='</dob>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  --

                                  tag :='<ssn>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  if (c1.ssn is not null) then

                                    dbms_lob.writeappend(tlob, length(c1.ssn), c1.ssn);

                                  end if;

                                  tag :='</ssn>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  --

                                  tag :='gender';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  if (c1.gender is not null) then

                                    dbms_lob.writeappend(tlob, length(c1.gender), c1.gender);

                                  end if;

                                  tag :='</gender>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  --

                                  tag :='race';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                  if (c1.race is not null) then

                                    dbms_lob.writeappend(tlob, length(c1.race), c1.race);

                                  end if;

                                  tag :='</race>';

                                  dbms_lob.writeappend(tlob, length(tag), tag);

                                end loop;

                              end text_proc_N;

                              /

                             

                            begin

                            ctx_thes.drop_thesaurus('NAMESYN1');

                            end;

                            /

                            begin

                            ctx_thes.create_relation('NAMESYN1','Wallace','SYN','Wally');

                            commit;

                            ctx_thes.create_relation('NAMESYN1','Walter','SYN','Wally');

                            commit;

                            ctx_thes.create_relation('NAMESYN1','Walter','SYN','Walt');

                            commit;

                            ctx_thes.create_relation('NAMESYN1','Walt','SYN','Walter');

                            commit;

                            ctx_thes.create_relation('NAMESYN1',Robert,'SYN','Bob');

                            commit;

                            end ;

                             

                            begin ctx_ddl.drop_preference('text_proc'); end;
                            /

                            begin
                              ctx_ddl.create_preference('text_proc', 'user_datastore');
                              ctx_ddl.set_attribute('text_proc', 'procedure', 'text_proc_N');
                              ctx_ddl.set_attribute('text_proc', 'output_type', 'CLOB');
                            end;
                            /
                            ---Name group sections to identify our tags in index
                            begin ctx_ddl.drop_section_group('namegroup');
                            end;
                            /

                            begin
                              ctx_ddl.create_section_group('namegroup', 'BASIC_SECTION_GROUP');
                              ctx_ddl.add_ndata_section('namegroup', 'fname', 'fname');
                              ctx_ddl.add_ndata_section('namegroup', 'mname', 'mname');
                              ctx_ddl.add_ndata_section('namegroup', 'lname', 'lname');
                              ctx_ddl.add_ndata_section('namegroup', 'ssn', 'ssn');
                              ctx_ddl.add_ndata_section('namegroup', 'race', 'race');
                              ctx_ddl.add_ndata_section('namegroup', 'gender', 'gender');
                              ctx_ddl.add_ndata_section('namegroup', 'dob', 'dob');
                            end;
                            /

                            ---similiar name wordlist pereference
                            begin ctx_ddl.drop_preference('ndata_wl');
                            end;
                            /
                            begin
                              ctx_ddl.create_preference('NDATA_WL', 'BASIC_WORDLIST');
                              ctx_ddl.set_attribute('NDATA_WL', 'NDATA_ALTERNATE_SPELLING', 'FALSE');
                              ctx_ddl.set_attribute('NDATA_WL', 'NDATA_BASE_LETTER', 'TRUE');
                              ctx_ddl.set_attribute('NDATA_WL','prefix_index', 'YES');
                              ctx_ddl.set_attribute('NDATA_WL', 'NDATA_THESAURUS', 'NAMESYN1');
                                ctx_ddl.set_attribute('NDATA_WL', 'NDATA_JOIN_PARTICLES',
                                'de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');
                            end;
                            /

                            drop index text_idx;
                            /

                            create index text_idx on person_attribute1  (first_name) indextype is ctxsys.context
                              parameters
                                  ('datastore text_proc
                                    section group namegroup
                                    wordlist ndata_wl
                                    memory 500M
                                    SYNC (ON COMMIT)' );

                            select  LAST_NAME, first_name , middle_name ,dob  DATE_OF_BIRTH , ssn , race_type_id race ,gender_type_id gender , score(1) scr

                                , utl_match.jaro_winkler_similarity (:fname||nvl2(:mname,:mname,'')||:lname||:SSN||nvl2(:dob,:dob,'')||nvl2(:race,:race,'')||nvl2(:gender,:gender,'') ,

                                first_NAME||nvl2(:mname,middle_name,'')||last_name||SSN||nvl2(:dob,dob,'')||nvl2(:race,race_type_id,'')||nvl2(:gender,gender_type_id,'') ) jws ,

                                utl_match.edit_distance_similarity  (:fname||nvl2(:mname,:mname,'')||:lname||:SSN||nvl2(:dob,:dob,'')||nvl2(:race,:race,'')||nvl2(:gender,:gender,'') ,

                                first_NAME||nvl2(:mname,middle_name,'')||last_name||SSN||nvl2(:dob,dob,'')||nvl2(:race,race_type_id,'')||nvl2(:gender,gender_type_id,'') )  ed

                            FROM    person_attribute1 pa

                                WHERE  contains

                                      (first_name,

                              'ndata (fname,' || :fname || ')' ||

                                  nvl2(:mname,' OR ndata (mname,'|| :mname || '),',null) ||

                                  ' or ndata (lname,'|| :lname || ')' ||

                                        NVL2 (:dob, ' OR ndata (dob,'|| :dob || ')', NULL) ||

                                        NVL2 (:gender, ' OR ndata (gender,' || :gender || ')', NULL) ||

                                        NVL2 (:race, ' OR ndata (race,' || :race || ')', NULL) ||

                                        nvl2(:ssn,' OR ndata (ssn,' || :ssn || ')',null),  1) > 0

                              order  by score(1) desc , jws desc ,ed desc

                             

                             

                            if I search on last name robert it returns bob but if search on walt or walter does not return any things.

                            Kindly suggest what it is acting this way.

                            I made sure that data has walt and walter as last name in them.

                            • 11. Re: Index empty-Name searching logic.
                              Barbara Boehmer

                              In your procedure, the second line below is missing.  There may be other errors.

                               

                               

                                tag :='<lname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                if (c1.LAST_NAME is not null) then
                                  dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);
                                end if;
                                tag :='</lname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                              • 12. Re: Re: Re: Re: Index empty-Name searching logic.
                                Barbara Boehmer

                                It looks like you have the same problem with fname and mname as with lname when you split them from name.  You also have some lines that don't belong, indicated with comments below.  There may be other errors.

                                 

                                 

                                tag :='<fname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                if (c1.FIRST_NAME is not null) then
                                   dbms_lob.writeappend(tlob, length(c1.FIRST_NAME), c1.FIRST_NAME);
                                --   dbms_lob.writeappend(tlob, length(' '), ' ');
                                end if;
                                tag :='</fname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                --
                                tag :='<mname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                if (c1.MIDDLE_NAME is not null) then
                                   dbms_lob.writeappend(tlob, length(c1.MIDDLE_NAME), c1.MIDDLE_NAME);
                                --   dbms_lob.writeappend(tlob, length(' '), ' ');
                                end if;
                                tag :='</mname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                --
                                tag :='<lname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                if (c1.LAST_NAME is not null) then
                                   dbms_lob.writeappend(tlob, length(c1.LAST_NAME), c1.LAST_NAME);
                                end if;
                                tag :='</lname>';
                                dbms_lob.writeappend(tlob, length(tag), tag);
                                • 13. Re: Index empty-Name searching logic.
                                  n_shah18

                                  Hi Barbara

                                  Solutions is working great, as expect but We have another problem

                                  My table sas 3 Million + records

                                  When I create Index on 50,000 records it creates almost 1 mill records in index.

                                  When I create Index on 500,000 records it creates almost 10 mill records.

                                  When I tried running create index on full table It used up almost 42 GB of tablespace, I guess using Clob(s) in index creation is a lot of overhead involved.

                                  It takes almost 1.5 hrs to create one.

                                   

                                  question I there I way to  shorten the size and space it is using?

                                   

                                  Is there I faster way to rebuild / keep index in sync  (sync (on commit does not work)?)

                                   

                                  If above two question comes to point which is unsolvable then what my alternative where I can do same kind of search ?

                                   

                                  Many thanks In advance

                                  Neel

                                  • 14. Re: Index empty-Name searching logic.
                                    Barbara Boehmer

                                    In general, the most important thing is to make your queries do what you want quickly.  Things like index creation time and disk space are less important.  Disk space is cheap and should be your least concern.  I assume that you will only be creating your full index once and just updating it thereafter, so it should not matter much how  long it takes to create it initially.  There are various methods of synchronizing and optimizing text indexes.  What interval you use depends on your needs.  There is also the transactional option.  When you say that sync(on commit) does not work, you need to be more specific.  Are you not getting the results from queries that you expect and assume it is because the index was not synchronized?  It may be that synchronization did not occur because you did not update the column that the index was created on as part of your update process.  Or is synchronization causing too much fragmentation slowing your queries?  If so, then you may need to optimize more frequently.  Please be more specific as to what you mean when you say it "does not work".

                                     

                                    The following is a general guideline to efficient indexing of text indexes:

                                    http://docs.oracle.com/cd/E11882_01/text.112/e24435/aoptim.htm#CCAPP9272

                                     

                                    The following is a general guideline as to how often to update text indexes:

                                    http://docs.oracle.com/cd/E11882_01/text.112/e24435/aoptim.htm#CCAPP9280

                                    1 2 Previous Next