Hello everyone,
I'm trying to use sdata in Oracle 19 and I'm facing a very strange issue.
I create a very simple table with one row.
CREATE TABLE hw(id number, c clob);
insert into hw values(1,
'<doc>
<id>1</id>
<name>hello world</name>
<status>published</status>
</doc>
');
commit;
I create an XML SECTION GROUP because my content is an XML document.
BEGIN
ctx_ddl.create_section_group (group_name => 'hw_sg', group_type => 'XML_SECTION_GROUP');
ctx_ddl.add_field_section ('hw_sg', 'id', 'doc/id', true);
ctx_ddl.add_field_section ('hw_sg', 'name', 'doc/name', TRUE);
END;
/
If I create the index now like this:
DROP INDEX hw_idx;
CREATE INDEX hw_idx
ON hw(c)
INDEXTYPE IS ctxsys.context
PARAMETERS ('
section group hw_sg
');
I can check the statistics like this:
delete t;
declare
x clob;
begin
ctx_report.index_stats('hw_idx',x);
insert into t values(x);
dbms_lob.freetemporary(x);
end;
/
This is the report that is generated and everything seems to be fine.
===========================================================================
STATISTICS FOR "HW"."HW_IDX"
===========================================================================
indexed documents: 1
allocated docids: 1
$I rows: 4
---------------------------------------------------------------------------
TOKEN STATISTICS
---------------------------------------------------------------------------
unique tokens: 4
average $I rows per token: 1.00
tokens with most $I rows:
WORLD (0:TEXT) 1
PUBLISHED (0:TEXT) 1
HELLO (0:TEXT) 1
1 (0:TEXT) 1
average size per token: 3
tokens with largest size:
WORLD (0:TEXT) 3
PUBLISHED (0:TEXT) 3
HELLO (0:TEXT) 3
1 (0:TEXT) 3
average frequency per token: 1.00
most frequent tokens:
WORLD (0:TEXT) 1
PUBLISHED (0:TEXT) 1
HELLO (0:TEXT) 1
1 (0:TEXT) 1
token statistics by type:
token type: 0:TEXT
unique tokens: 4
total rows: 4
average rows: 1.00
total size: 12
average size: 3
average frequency: 1.00
most frequent tokens:
WORLD 1
PUBLISHED 1
HELLO 1
1 1
---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------
total size of $I data: 12
$I rows: 4
estimated $I rows if optimal: 4
estimated row fragmentation: 0 %
garbage docids: 0
estimated garbage size: 0
most fragmented tokens:
WORLD (0:TEXT) 0 %
PUBLISHED (0:TEXT) 0 %
HELLO (0:TEXT) 0 %
1 (0:TEXT) 0 %
However, when I do this and add ONE sdata section:
BEGIN
ctx_ddl.drop_section_group (group_name => 'hw_sg');
END;
/
BEGIN
ctx_ddl.create_section_group (group_name => 'hw_sg', group_type => 'XML_SECTION_GROUP');
ctx_ddl.add_field_section ('hw_sg', 'doc/id', 'id', true);
ctx_ddl.add_field_section ('hw_sg', 'doc/name', 'name', TRUE);
END;
/
BEGIN
ctx_ddl.add_sdata_section ('hw_sg', 'status', 'doc/status', 'VARCHAR2');
ctx_ddl.set_section_attribute('hw_sg', 'status', 'optimized_for', 'search');
END;
/
And recreate the index the same way:
DROP INDEX hw_idx;
CREATE INDEX hw_idx
ON hw(c)
INDEXTYPE IS ctxsys.context
PARAMETERS ('
section group hw_sg
');
I can describe index and check its size.
select ctx_report.describe_index('hw_idx') from dual;
select ctx_report.index_size('hw_idx') from dual;
But, when I try to do
delete t;
declare
x clob;
begin
ctx_report.index_stats('hw_idx',x);
insert into t values(x);
dbms_lob.freetemporary(x);
end;
/
select * from t;
I got an exception telling me a table or a view is missing.
declare
x clob;
begin
ctx_report.index_stats('hw_idx',x);
insert into t values(x);
dbms_lob.freetemporary(x);
end;
Error report -
ORA-20000: Oracle Text error:
DRG-50857: oracle error in dreessql
ORA-00942: table or view does not exist
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.CTX_REPORT", line 553
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.
I really don't understand what table may be missing? The difference between the two codes are only the sdata section.
Does anyone have an idea on how I can debug this and check on which table it's complaining?
I have provided the whole script if anyone is kind enough to test it on a different instance (maybe a different version than 19). Maybe it will work ?
Thank you in advance for any feedback !
Regards,