Skip to Main Content

table or view does not exist: ok but which one?

user13117585Apr 13 2022 — edited Apr 13 2022

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,

Comments
Post Details
Added on Apr 13 2022
7 comments
54 views