Database Software

I am testing a simple user data store on 10g in preparation for implementing a complex multi-table/multi column text index. Can someone please point out why this is not working for me? TIA ... RK

For User SCOTT.

create table rktest1 (id1 number primary key, text1a clob, text1b varchar(200));
create table rktest2 (id2 number primary key, text2a clob, text2b varchar(200));

insert into rktest1 values (1, 'Rajesh', 'Kapur');
insert into rktest1 values (2, 'Kapur', 'Rajesh');

insert into rktest2 values (1, 'Natasha', 'Nikhil');
insert into rktest2 values (2, 'Nikhil', 'Natasha');

-----------------------------
create or replace procedure rktest1_proc(
rid in rowid,
tlob in out clob
)
is
begin
for c1 in (select text1a,
text1b
from rktest1
WHERE rktest1.rowid = rid)
loop
dbms_lob.writeappend(tlob, length(c1.text1a), c1.text1a);
dbms_lob.writeappend(tlob, length(c1.text1b), c1.text1b);
end loop;
end;

----------------------------
begin
ctx_ddl.drop_preference('rktestud');
ctx_ddl.create_preference('rktestud', 'user_datastore');
ctx_ddl.set_attribute('rktestud', 'procedure', 'rktest1_proc');
ctx_ddl.set_attribute('rktestud', 'output_type', 'CLOB');
end;

create index rktest_text_1
on rktest1(text1a)
indextype is ctxsys.context
parameters ('datastore rktestud');
------------------------------
The following SQL did not return any rows....

select * from rktest1 where contains (text1a, 'rajesh') > 0;

---- After I get this test to work, I will attempt to enhance the stored procedure to index data from rktest1 and rktest2.