Comments (28)
I can (unfortunately) confirm both...
I have raised SR to Oracle regarding issue on Autonomous Database.
from apex-blog.
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.
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.
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.
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.
What are the commands for drop and recreate the Oracle Text only?
from apex-blog.
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.
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.
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.DRVORA-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.
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.
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.
But not every run, a little bit weird...
from apex-blog.
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.
But not every run, a little bit weird...
Could you please show what is job names for those rows?
from apex-blog.
Here is my simple test case that I did use for SR
text_index_test.txt
from apex-blog.
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 ...
from apex-blog.
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.
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.
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.
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.
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.
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.
Ok. Then you can place a button somewhere in the admin app to do it manually 😉. Sometime in the next update...
from apex-blog.
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.
This seems to be bug in always free ADB. In paid ADW and ATP text index sync works.
from apex-blog.
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.
Two months and Oracle still working on issue....
from apex-blog.
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)
- How can i Insert a image to Blog post? HOT 5
- Errors appears in the App Builder (import): Build Status must be specified. Parse As Schema must be specified. File is not a valid Oracle APEX application export file. HOT 10
- RSS as "raw XML" HOT 20
- BLog login ERROR HOT 6
- Error with new constant handling and translated app HOT 16
- Application Item G_DATE_FORMAT is missing in Public App HOT 5
- Application Language Derived From always English HOT 2
- Are you interested in the (german) translated app? HOT 3
- APEX 21.2 - Font APEX Icon "fa-rss-square" is empty (?) HOT 4
- Enhancement - CKEditor5 HOT 1
- Post pagination incorrect HOT 1
- Oracle Text error: DRG-10599: column is not indexed HOT 1
- Installation error for "apex_blog_22.2.4.20230518" HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
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.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from apex-blog.