OracleText - Error

[scl-4711]

Hi Jari,

I have a problem that is only indirectly related to the blog.

My blog runs on the "Always Free Autonomous Database" in the Oracle Cloud.

There is the following error when I want to edit an entry DRG-50850: drftsync on-commit internal error...

In MyOracleSupport I find for DRG-50850: "Inserting into a table with a Text index with the SYNC ON COMMIT attribute fails"

Do you have any idea what I can do? (How) can I customize the Oracle Text Index from "sync on commit" to for example "intervals"? Can I disable / drop the Oracle Text Index? Is the app usable afterwards?

Thx Steffen

[jariolaine]

Hi Steffen,

Thanks for informing this. I need check more deeply, but it might be bug in blog application or APEX. Updates to blog_posts table work OK when doing it using e.g. SQL Developer. I use also always free ADB. If you drop text index, at least blog search will not work and will give errors.

Regards, Jari

[jariolaine]

Hi Steffen, Could you please install blog admin application to same workspace, but new application id. Then try edit post from application you just installed. When doing that, I don't get any errors from application that I installed to new id. Existing application still gives error.

For me this sounds APEX bug. Maybe they have issue on upgrade to 23.2 or patch 23.2.1. Need try somehow reproduce this and raise SR.

[scl-4711]

Hi Jari, without reinstall supporting objects? If not I need more time to backup my blog and reinstall all again. A simple export and import fails because of the virtual colums. Datapump with always free ADB - I have no knowlege about ;-) Regards Steffen

[scl-4711]

What are the commands for drop and recreate the Oracle Text only?

[jariolaine]

Hi Jari, without reinstall supporting objects?

Don't delete existing admin app. Install another copy to new application id. When installing admin app to new id, you can select not to upgrade supporting objects.

[jariolaine]

What are the commands for drop and recreate the Oracle Text only?

Here is commands:

--------------------------------------------------------
--  Drop text indexes
--------------------------------------------------------
drop index blog_comments_ctx;
drop index blog_posts_ctx;

--------------------------------------------------------
--  Drop text index preferences
--------------------------------------------------------
begin
  ctx_ddl.drop_preference( 'BLOG_COMMENTS_UDS' );
end;
/
begin
  ctx_ddl.drop_preference( 'BLOG_POSTS_UDS' );
end;
/

--------------------------------------------------------
--  Create text index preferences for index BLOG_COMMENTS_CTX
--------------------------------------------------------
begin

  ctx_ddl.create_preference(
     preference_name  => 'BLOG_COMMENTS_UDS'
    ,object_name      => 'USER_DATASTORE'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_COMMENTS_UDS'
    ,attribute_name   => 'OUTPUT_TYPE'
    ,attribute_value  => 'CLOB'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_COMMENTS_UDS'
    ,attribute_name   => 'PROCEDURE'
    ,attribute_value  =>
      apex_string.format(
        p_message => '%s.BLOG_CTX.GENERATE_COMMENT_DATASTORE'
        ,p0 => sys_context( 'USERENV', 'CURRENT_SCHEMA' )
      )
  );

end;
/
--------------------------------------------------------
--  Create text index BLOG_COMMENTS_CTX
--------------------------------------------------------
create index blog_comments_ctx on blog_comments( ctx_search )
indextype is ctxsys.context parameters(
  'datastore      blog_comments_uds
   lexer          ctxsys.default_lexer
   section group  ctxsys.auto_section_group
   stoplist       ctxsys.empty_stoplist
   filter         ctxsys.null_filter
   sync           (on commit)'
);

--------------------------------------------------------
--  Create text index preferences for index BLOG_POSTS_CTX
--------------------------------------------------------
begin

  ctx_ddl.create_preference(
     preference_name  => 'BLOG_POSTS_UDS'
    ,object_name      => 'USER_DATASTORE'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_POSTS_UDS'
    ,attribute_name   => 'OUTPUT_TYPE'
    ,attribute_value  => 'CLOB'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_POSTS_UDS'
    ,attribute_name   => 'PROCEDURE'
    ,attribute_value  =>
      apex_string.format(
        p_message => '%s.BLOG_CTX.GENERATE_POST_DATASTORE'
        ,p0 => sys_context( 'USERENV', 'CURRENT_SCHEMA' )
      )
  );

end;
/
--------------------------------------------------------
--  Create text index BLOG_POSTS_CTX
--------------------------------------------------------
create index blog_posts_ctx on blog_posts( ctx_search )
indextype is ctxsys.context parameters(
  'datastore      blog_posts_uds
   lexer          ctxsys.default_lexer
   section group  ctxsys.auto_section_group
   stoplist       ctxsys.empty_stoplist
   filter         ctxsys.null_filter
   sync           (on commit)'
);

[jariolaine]

What I did test, when installing application to apex.oracle.com, it works OK.

In autonomous database, when I create text index that syncs every interval:

create index blog_posts_ctx on blog_posts( ctx_search )
indextype is ctxsys.context parameters(
  'datastore      blog_posts_uds
   lexer          ctxsys.default_lexer
   section group  ctxsys.auto_section_group
   stoplist       ctxsys.empty_stoplist
   filter         ctxsys.null_filter
   sync           ( every "FREQ=MINUTELY;INTERVAL=1" )'
);

Run update

update blog_posts
set ctx_search = 'X'
;
commit;

I can see error from all_scheduler_job_run_details when sync job runs

select *
from all_scheduler_job_run_details
order by actual_start_date desc
;

ORA-20000: Oracle Text error: DRG-50857: oracle error in dretbase ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "CTXSYS.DRIXMD", line 1860 ORA-28133: full table access is restricted by fine-grained security ORA-06512: at "CTXSYS.DRIXMD", line 1845 ORA-06512: at "CTXSYS.DRV

ORA-06512: at "CTXSYS.DRUE", line 186 ORA-06512: at "CTXSYS.DRVDML", line 946 ORA-06512: at line 1

But index is synced ok If running

begin
  ctx_ddl.sync_index( 
    idx_name => 'BLOG_POSTS_CTX'
  );
end;
/

This seems to be some issue/bug on ATP.

[scl-4711]

I can (unfortunately) confirm both - but I can add posts again without error on save with the index that syncs every interval.

[jariolaine]

I can (unfortunately) confirm both...

I have raised SR to Oracle regarding issue on Autonomous Database.

[jariolaine]

but I can add posts again without error on save with the index that syncs every interval.

Yes, but text index sync fails. And blog public app search will not include your new posts or changes to existing posts.

[scl-4711]

But not every run, a little bit weird...

[scl-4711]

but I can add posts again without error on save with the index that syncs every interval.

Yes, but text index sync fails. And blog public app search will not include your new posts or changes to existing posts.

I understand that, but it's not that important at the moment. It's bad when you can't blog on a blog ;-)

[jariolaine]

But not every run, a little bit weird...

Could you please show what is job names for those rows?

[jariolaine]

Here is my simple test case that I did use for SR text_index_test.txt

[jariolaine]

I understand that, but it's not that important at the moment. It's bad when you can't blog on a blog ;-)

In that case you can disable sync totally

--------------------------------------------------------
--  Drop text indexes
--------------------------------------------------------
drop index blog_comments_ctx;
drop index blog_posts_ctx;

--------------------------------------------------------
--  Drop text index preferences
--------------------------------------------------------
begin
  ctx_ddl.drop_preference( 'BLOG_COMMENTS_UDS' );
end;
/
begin
  ctx_ddl.drop_preference( 'BLOG_POSTS_UDS' );
end;
/

--------------------------------------------------------
--  Create text index preferences for index BLOG_COMMENTS_CTX
--------------------------------------------------------
begin

  ctx_ddl.create_preference(
     preference_name  => 'BLOG_COMMENTS_UDS'
    ,object_name      => 'USER_DATASTORE'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_COMMENTS_UDS'
    ,attribute_name   => 'OUTPUT_TYPE'
    ,attribute_value  => 'CLOB'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_COMMENTS_UDS'
    ,attribute_name   => 'PROCEDURE'
    ,attribute_value  =>
      apex_string.format(
        p_message => '%s.BLOG_CTX.GENERATE_COMMENT_DATASTORE'
        ,p0 => sys_context( 'USERENV', 'CURRENT_SCHEMA' )
      )
  );

end;
/
--------------------------------------------------------
--  Create text index BLOG_COMMENTS_CTX
--------------------------------------------------------
create index blog_comments_ctx on blog_comments( ctx_search )
indextype is ctxsys.context parameters(
  'datastore      blog_comments_uds
   lexer          ctxsys.default_lexer
   section group  ctxsys.auto_section_group
   stoplist       ctxsys.empty_stoplist
   filter         ctxsys.null_filter'
);

--------------------------------------------------------
--  Create text index preferences for index BLOG_POSTS_CTX
--------------------------------------------------------
begin

  ctx_ddl.create_preference(
     preference_name  => 'BLOG_POSTS_UDS'
    ,object_name      => 'USER_DATASTORE'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_POSTS_UDS'
    ,attribute_name   => 'OUTPUT_TYPE'
    ,attribute_value  => 'CLOB'
  );

  ctx_ddl.set_attribute(
    preference_name   => 'BLOG_POSTS_UDS'
    ,attribute_name   => 'PROCEDURE'
    ,attribute_value  =>
      apex_string.format(
        p_message => '%s.BLOG_CTX.GENERATE_POST_DATASTORE'
        ,p0 => sys_context( 'USERENV', 'CURRENT_SCHEMA' )
      )
  );

end;
/
--------------------------------------------------------
--  Create text index BLOG_POSTS_CTX
--------------------------------------------------------
create index blog_posts_ctx on blog_posts( ctx_search )
indextype is ctxsys.context parameters(
  'datastore      blog_posts_uds
   lexer          ctxsys.default_lexer
   section group  ctxsys.auto_section_group
   stoplist       ctxsys.empty_stoplist
   filter         ctxsys.null_filter'
);

[scl-4711]

But not every run, a little bit weird...

Could you please show what is job names for those rows?

I set the interval to 60 ...

[jariolaine]

I think those success rows are when you have created text index and/or when sync happens and there isn't anything to sync.

[scl-4711]

Here is my simple test case that I did use for SR text_index_test.txt

I can reproduce this - the first run is succeeded, the others are all failed...

[scl-4711]

I think those success rows are when you have created text index and/or when sync happens and there isn't anything to sync.

Maybe - you said that if indexing doesn't work, search doesn't find anything. I can still find my latest blog entry from before in the search, so the indexing must have worked?!

[jariolaine]

Maybe - you said that if indexing doesn't work, search doesn't find anything. I can still find my latest blog entry from before in the search, so the indexing must have worked?!

When you have data on table and you create/recreate index, indexing words. But sync insert/update to index will fail if index is sync on commit and insert/update is done using APEX. Sync insert/update will fail if index sync is every interval.

[scl-4711]

Maybe - you said that if indexing doesn't work, search doesn't find anything. I can still find my latest blog entry from before in the search, so the indexing must have worked?!

When you have data on table and you create/recreate index, indexing words. But sync insert/update to index will fail if index is sync on commit and insert/update is done using APEX. Sync insert/update will fail if index sync is every interval.

Ok, then the workaround is to drop and recreate index after blogging. Not nice but ok for the moment. Thx for your support.

[jariolaine]

Ok, then the workaround is to drop and recreate index after blogging. Not nice but ok for the moment. Thx for your support.

You don't need recreate text indexes. Manually sync works e.g. from SQL Developer, what I tested.

begin
  ctx_ddl.sync_index('BLOG_POSTS_CTX');
  ctx_ddl.sync_index('BLOG_COMMENTS_CTX');
end;
/

[scl-4711]

Ok. Then you can place a button somewhere in the admin app to do it manually 😉. Sometime in the next update...

[jariolaine]

Ok. Then you can place a button somewhere in the admin app to do it manually 😉. Sometime in the next update...

Manual sync is not working from APEX page process or APEX automation or dbms_scheduler job what I did test. :(

[jariolaine]

This seems to be bug in always free ADB. In paid ADW and ATP text index sync works.

[scl-4711]

This seems to be bug in always free ADB. In paid ADW and ATP text index sync works.<

Ok, maybe it will be fixed anytime 👍

[jariolaine]

Two months and Oracle still working on issue....

[jariolaine]

It seems this issue is now fixed in my Always Free ADB. In attachment recreate_text_indexes.zip is script to recreate text indexes.