Git Product home page Git Product logo

Comments (28)

jariolaine avatar jariolaine commented on June 23, 2024 1

I can (unfortunately) confirm both...

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024 1

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'
);

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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.

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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.

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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)'
);

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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.

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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.

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

image

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

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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 ;-)

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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

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

image

I set the interval to 60 ...

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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...

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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?!

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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.

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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.

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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;
/

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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. :(

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

from apex-blog.

scl-4711 avatar scl-4711 commented on June 23, 2024

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 👍

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

from apex-blog.

jariolaine avatar jariolaine commented on June 23, 2024

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

from apex-blog.

Related Issues (14)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.