USER_DATASTORE with CLOB Example

Consider a table in which the author, title, and text fields are separate, as in the articles table defined as follows:

create table articles(
    id       number,
    author   varchar2(80),
    title    varchar2(120),
    text     clob );

The author and title fields are to be part of the indexed document text. Assume user appowner writes a stored procedure with the user datastore interface that synthesizes a document from the text, author, and title fields:

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;

This procedure takes in a rowid and a temporary CLOB locator, and concatenates all the article’s columns into the temporary CLOB. The for loop executes only once.

The user appowner creates the preference as follows:

begin

ctx_ddl.create_preference('myud', 'user_datastore');
ctx_ddl.set_attribute('myud', 'procedure', 'myproc');
ctx_ddl.set_attribute('myud', 'output_type', 'CLOB');

end;

When appowner creates the index on articles(text) using this preference, the indexing operation sees author and title in the document text.