This discussion is archived
2 Replies Latest reply: Jul 5, 2013 2: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 Newbie
Currently Being Moderated

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

    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.

Legend

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