2 Replies Latest reply: Jun 19, 2012 2:15 PM by 924529 RSS

    Number Search using Oracle Text

    924529
      Hi,

      I'm new to Oracle Text. I want to implement search for the unique ids. Like google search when the user start typing 123 it need to brings anything starting with 123 and has show like entries how google will shows. When I add number 4 to like 1234 then it has bring numbers starting with 1234. Any body suggest me what is the best approach to implement this. Do we have any examples for this kind requirement. If any please share. That will be a big help for me.

      In advance thanks a lot for the help.

      Thanks & Regards,
      Ramana.
        • 1. Re: Number Search using Oracle Text
          Barbara Boehmer
          Oracle Text is intended primarily for character data, but you can treat the numbers as characters. I have provided an example below.
          SCOTT@orcl_11gR2> CREATE TABLE test_tab (id  NUMBER, dummy VARCHAR2(1))
            2  /
          
          Table created.
          
          SCOTT@orcl_11gR2> INSERT INTO test_tab (id)
            2  SELECT object_id FROM all_objects
            3  /
          
          74986 rows created.
          
          SCOTT@orcl_11gR2> BEGIN
            2    CTX_DDL.CREATE_PREFERENCE ('test_mds', 'MULTI_COLUMN_DATASTORE');
            3    CTX_DDL.SET_ATTRIBUTE ('test_mds', 'COLUMNS', 'TO_CHAR(id) id');
            4  END;
            5  /
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> CREATE INDEX id_idx ON test_tab (dummy)
            2  INDEXTYPE IS CTXSYS.CONTEXT
            3  PARAMETERS ('DATASTORE  test_mds')
            4  /
          
          Index created.
          
          SCOTT@orcl_11gR2> VARIABLE search_string VARCHAR2(100)
          SCOTT@orcl_11gR2> EXEC :search_string := '123'
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> SELECT DISTINCT id
            2  FROM   test_tab
            3  WHERE  CONTAINS (dummy, :search_string || '%') > 0
            4  /
          
                  ID
          ----------
                 123
               12316
               12323
               12329
               12348
               12350
               12354
               12366
               12372
               12373
               12375
               12376
               12392
               12396
                1238
               12303
               12306
               12313
               12330
               12332
               12344
               12361
               12365
               12369
               12371
               12395
              123750
               12308
               12320
               12326
               12337
               12340
               12346
               12349
               12370
               12386
               12387
               12397
                1233
                1234
                1236
                1237
               12300
               12301
               12302
               12305
               12309
               12311
               12319
               12338
               12345
               12355
               12359
               12362
               12377
               12388
               12390
               12391
               12398
                1239
               12304
               12307
               12360
               12318
               12325
               12335
               12339
               12353
               12357
               12358
               12394
               12312
               12317
               12328
               12327
               12331
               12333
               12351
               12352
               12356
               12363
               12364
               12367
               12368
               12393
                1235
               12310
               12314
               12324
               12334
               12336
               12341
               12347
               12374
               12389
               12399
          
          96 rows selected.
          
          SCOTT@orcl_11gR2> EXEC :search_string := '1234'
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> /
          
                  ID
          ----------
               12348
               12344
               12340
               12346
               12349
                1234
               12345
               12341
               12347
          
          9 rows selected.
          • 2. Re: Number Search using Oracle Text
            924529
            Hi Barbara,

            Thanks a lot for quick reply. Its really helpful.

            Regards,
            Ramana.