Hi Tom,
i was using the example you gave to create user_data_store which would create xml (on 11g).
in my case i have over 10 tables containing text and reference the master table in a one to many relationship (possible that they have no referencing row to the main table)
also possible that many of the columns are null.
i narrowed it down to 3 tables only for demonstrating:
--create tables
create table people(pseq number primary key,id varchar2(250), name varchar2(250),description varchar2(4000));
create table people_add (pseq number, address varchar2(4000),phone varchar2(250));
create table people_mail (pseq number, email varchar2(250),email_desc varchar2(4000));
ALTER TABLE TEXT.PEOPLE_ADD ADD (FOREIGN KEY (PSEQ) REFERENCES TEXT.PEOPLE (PSEQ) ON DELETE CASCADE);
ALTER TABLE TEXT.PEOPLE_MAIL ADD (FOREIGN KEY (PSEQ) REFERENCES TEXT.PEOPLE (PSEQ) ON DELETE CASCADE);
--populate tables
insert into people values (1,'111111','lulu','no information');
insert into people values (2,'222222','dan','bank account');
insert into people values (3,'333333','dana','');
insert into people values (4,'444444','kuku','information');
insert into people values (5,'111111','lala','basketball');
insert into people values (6,'111111','','missing name');
insert into people values (7,'111111','nono','something something');
insert into people_add values (1,'street','012-phone');
insert into people_add values (1,'second street','');
insert into people_add values (2,'first','');
insert into people_add values (3,'5th','1357');
insert into people_add values (5,'3rd','111');
insert into people_mail values (1,'mymail@somemail.com','office email');
insert into people_mail values (4,'mymail2@somemail.com','e-mail');
insert into people_mail values (5,'','at home');
commit;
-- create procedure
create or replace procedure people_full_search (rid in rowid,p_clob in out NOCOPY clob)
is
begin
FOR r1 IN (SELECT pseq,nvl(id,' ') id,nvl(name,' ') name,nvl(description,' ') description FROM people WHERE ROWID = rid) LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 4, '<id>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.id), r1.id);
DBMS_LOB.WRITEAPPEND (p_clob, 6, '<name>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.name), r1.name);
DBMS_LOB.WRITEAPPEND (p_clob, 7, '</name>');
DBMS_LOB.WRITEAPPEND (p_clob, 13, '<description>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.description), r1.description);
DBMS_LOB.WRITEAPPEND (p_clob, 14, '</description>');
FOR r2 IN (SELECT nvl(address,' ') address, nvl(phone,' ') phone FROM people_add WHERE pseq = r1.pseq) LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 9, '<address>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.address), r2.address);
DBMS_LOB.WRITEAPPEND (p_clob, 10, '</address>');
DBMS_LOB.WRITEAPPEND (p_clob, 7, '<phone>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.phone), r2.phone);
DBMS_LOB.WRITEAPPEND (p_clob, 8, '</phone>');
END LOOP;
FOR r3 IN (SELECT nvl(email,' ') email, nvl(email_desc,' ') email_desc FROM people_mail WHERE pseq = r1.pseq) LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 7, '<email>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.email), r3.email);
DBMS_LOB.WRITEAPPEND (p_clob, 8, '</email>');
DBMS_LOB.WRITEAPPEND (p_clob, 12, '<email_desc>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.email_desc), r3.email_desc);
DBMS_LOB.WRITEAPPEND (p_clob, 13, '</email_desc>');
END LOOP;
DBMS_LOB.WRITEAPPEND (p_clob, 5, '</id>');
END LOOP;
end people_full_search;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE ('people_datastore', 'USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE ('people_datastore', 'PROCEDURE', 'people_full_search');
CTX_DDL.SET_ATTRIBUTE ('people_datastore', 'OUTPUT_TYPE', 'CLOB');
END;
/
CREATE INDEX people_search ON people (search)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE people_datastore
SECTION GROUP CTXSYS.AUTO_SECTION_GROUP');
it seem that when i want to get data from across 3 tables i face a Cartesian join.
adding a condition will filter rows that don't have related data some of the tables, and outer join will take hours and fail on temp resources.
am i down to the second option of index each table separately?
thanks,
Liat