Hello again,
Could anyone advise on how to use data sections for xml section groups? Here is a quick sample:
CREATE TABLE books(
id number,
val clob
);
insert into books(id, val) VALUES(1, q'#
<book>
<references>
<ref name="isbn10">0123456789</ref>
<ref name="isbn13">0123456789123</ref>
</references>
<titles>
<title language="english">
The Lord of the rings
</title>
<title language="spanish">
El Señor de los Anillos
</title>
<title language="german">
Der Herr der Ringe
</title>
</titles>
</book>
#');
insert into books(id, val) VALUES(2, q'#
<book>
<references>
<ref name="isbn10">9876543210</ref>
<ref name="isbn13">3219876543321</ref>
</references>
<titles>
<title language="english">
Harry Potter and the Order of the Phoenix
</title>
<title language="italian">
Harry Potter e l'ordine della fenice
</title>
<title language="dutch">
Harry Potter en de Orde van de Feniks
</title>
</titles>
</book>
#');
COMMIT;
Since it's XML content, I would like to use the XML SECTION GROUP and create the sections like this:
BEGIN
ctx_ddl.create_section_group ('books_xml_sg', 'XML_SECTION_GROUP');
END;
/
BEGIN
ctx_ddl.add_field_section('books_xml_sg', 'reference', 'book/references/ref/name', true);
END;
/
BEGIN
ctx_ddl.add_attr_section('books_xml_sg', 'language', 'book/titles/title@language');
END;
/
And I create my index like this:
CREATE INDEX books_idx on books (val) indextype is ctxsys.context
PARAMETERS
('DATASTORE CTXSYS.DIRECT_DATASTORE
SECTION GROUP books_xml_sg
MEMORY 5M
'
);
When I do a SELECT, it works:
SELECT id, score(99)
FROM books
WHERE contains(val, 'harry', 99) > 1
ORDER BY score(99) DESC;
But, when I take the example from this link:
set long 500000
set pagesize 0
variable displayrs clob;
declare
rs clob;
begin
ctx_query.result_set('books_idx', 'harry',
'<ctx_result_set_descriptor>
<count/>
<group sdata="reference" topn="5" sortby="count" order="desc">
<count exact="true"/>
</group>
<group sdata="language" topn="3" sortby="value" order="asc">
<count exact="true"/>
</group>
</ctx_result_set_descriptor>',
rs);
/* Pretty-print the result set (rs) for display purposes.
It is not required if you are going to manipulate it in XML.*/
select xmlserialize(Document XMLType(rs) as clob indent size=2) into :displayrs from dual;
dbms_lob.freetemporary(rs);
end;
/
select :displayrs from dual;
I get the following error:
Error report -
ORA-20000: Oracle Text error:
DRG-50857: oracle error in ctx_query.result_set
ORA-20000: Oracle Text error:
DRG-13600: Syntax error in the result set descriptor at group
DRG-10837: section REFERENCE does not exist
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.CTX_QUERY", line 853
ORA-06512: at line 4
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.
Could anyone help in here and guide me on how to use sections in XML_SECTION_GROUP?
Regards,