2 Replies Latest reply: Jul 5, 2013 4:10 AM by bc4dda3c-5dfb-4837-8673-b62e74e1a78d RSS

    Context index with USER_DATASTORE With multiple columns doesn't return any records

    bc4dda3c-5dfb-4837-8673-b62e74e1a78d

      Hi Expert,

      Based on this url  : http://docs.oracle.com/cd/B28359_01/text.111/b28304/cdatadic.htm#i1006810

       

      I was trying to create an oracle text index , but i wasn't able to retrieve the data

      here are my scripts:

       

      create table articles(

          id       number,

          author   varchar2(80),

          title    varchar2(120),

          text     clob );

        

        

          create procedure myproc(rid in rowid, tlob in out clob nocopy) is

        begin

            for c1 in (select author, title, text from articles

                        where rowid = rid)

            loop

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

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

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

             end loop;

          end;

        

           

          begin

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

      ctx_ddl.set_attribute('myud', 'procedure', 'myproc');

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

      end;

        

          create index myindex2 on articles(text)

        indextype is ctxsys.context

        parameters ('DATASTORE myud');

       

        

            insert into articles(id,author,title)values (1,'A','AAAAAA');commit;

            insert into articles(id,author,title)values (2,'B','BBBBBB');commit;

            insert into articles(id,author,title)values (3,'C','CCCCCC');commit;

            insert into articles(id,author,title)values (4,'D','DDDDDD');commit;

          

            EXEC CTX_DDL.SYNC_INDEX('myindex2','2M');

          

            select * from articles where contains(text,'B',1)>0;  -- this display nothing.

       

      Can anybody help me?

        • 1. Re: Context index with USER_DATASTORE With multiple columns doesn't return any records
          Barbara Boehmer

          SCOTT@orcl_11gR2> create table articles(

            2       id      number,

            3       author   varchar2(80),

            4       title      varchar2(120),

            5       text      varchar2(30))

            6  /

           

          Table created.

           

          SCOTT@orcl_11gR2> create procedure myproc

            2    (rid  in     rowid,

            3      -- nocopy clob not clob nocopy:

            4      tlob in out nocopy clob)

            5  is

            6  begin

            7    for c1 in

            8       (select author, title, text

            9        from     articles

          10        where  rowid = rid)

          11    loop

          12       if c1.title is not null then

          13         dbms_lob.writeappend (tlob, length (c1.title), c1.title);

          14       end if;

          15       if c1.author is not null then

          16         -- add space between columns:

          17         dbms_lob.writeappend (tlob, 1, ' ');

          18         dbms_lob.writeappend (tlob, length (c1.author), c1.author);

          19       end if;

          20       if c1.text is not null then

          21         -- add space between columns:

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

          23         -- use append for clob, instead of writeappend:

          24         dbms_lob.append (tlob, c1.text);

          25       end if;

          26    end loop;

          27  end;

          28  /

           

          Procedure created.

           

          SCOTT@orcl_11gR2> show errors

          No errors.

          SCOTT@orcl_11gR2> begin

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

            3    ctx_ddl.set_attribute('myud', 'procedure', 'myproc');

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

            5  end;

            6  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl_11gR2> create index myindex2 on articles(text)

            2    indextype is ctxsys.context

            3    -- add empty_stoplist to parameters, so individual letters are indexed:

            4    parameters

            5       ('DATASTORE myud

            6         stoplist ctxsys.empty_stoplist')

            7  /

           

          Index created.

           

          SCOTT@orcl_11gR2> insert all

            2  into articles(id,author,title)values (1,'A','AAAAAA')

            3  into articles(id,author,title)values (2,'B','BBBBBB')

            4  into articles(id,author,title)values (3,'C','CCCCCC')

            5  into articles(id,author,title)values (4,'D','DDDDDD')

            6  select * from dual

            7  /

           

          4 rows created.

           

          SCOTT@orcl_11gR2> commit

            2  /

           

          Commit complete.

           

          SCOTT@orcl_11gR2> EXEC CTX_DDL.SYNC_INDEX('myindex2','2M')

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl_11gR2> -- show what procedure produces:

          SCOTT@orcl_11gR2> declare

            2    v_clob clob := empty_clob();

            3  begin

            4    for c1 in

            5       (select rowid rid

            6        from     articles)

            7    loop

            8       dbms_lob.createtemporary (v_clob, true);

            9       myproc (c1.rid, v_clob);

          10       dbms_output.put_line (v_clob);

          11       dbms_lob.freetemporary (v_clob);

          12    end loop;

          13  end;

          14  /

          AAAAAA A

          BBBBBB B

          CCCCCC C

          DDDDDD D

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl_11gR2> -- show what is indexed:

          SCOTT@orcl_11gR2> select token_text from dr$myindex2$i

            2  /

           

          TOKEN_TEXT

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

          A

          AAAAAA

          B

          BBBBBB

          C

          CCCCCC

          D

          DDDDDD

           

          8 rows selected.

           

          SCOTT@orcl_11gR2> select * from articles where contains(text,'B',1)>0

            2  /

           

                  ID

          ----------

          AUTHOR

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

          TITLE

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

          TEXT

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

                   2

          B

          BBBBBB

           

           

           

          1 row selected.