Thanks for the question.
Asked: January 22, 2020 - 8:32 pm UTC
Last updated: January 31, 2022 - 2:13 am UTC
Version: 12.1
Viewed 1000+ times
CREATE OR REPLACE PROCEDURE asktom.ate_question_idx (rid in rowid, tlob in out nocopy clob) is begin for c1 in (select q.subject, q.question, q.answer, q.id from ate_submitted_questions q, ate_admins a where q.rowid = rid and q.admin_id = a.id(+) ) loop tlob := tlob || c1.subject || ' '; if c1.email is not null then tlob := tlob || c1.email || ' '; end if; tlob := tlob || c1.question || ' '; if c1.answer is not null and length(c1.answer) > 0 then tlob := tlob || c1.answer || ' '; end if; for c2 in (select review_comments, followup_comments from ate_question_reviews where question_id = c1.id order by created_on) loop if c2.review_comments is not null then tlob := tlob || c2.review_comments ||' '; end if; if c2.followup_comments is not null then tlob := tlob || c2.followup_comments ||' '; end if; end loop; end if; end loop; end;
begin ctxsys.ctx_ddl.create_preference('ATE_SEARCH_IDX_DST','USER_DATASTORE'); ctxsys.ctx_ddl.set_attribute('ATE_SEARCH_IDX_DST','PROCEDURE','ASKTOM.ATE_QUESTION_IDX'); end; / create index ATE_SEARCH_IDX on ATE_SUBMITTED_QUESTIONS(ANSWER) indextype is ctxsys.context parameters(' datastore ATE_SEARCH_IDX_DST sync (every SYSDATE + 4/24 memory 67108864) ') /
A reader, March 01, 2021 - 10:41 pm UTC
... and contains(answer, 'fuzzy({oracle text}, 30, 2000)') > 0 ...
contains(info_col, :your_criteria, 99) > 0}
Marcel van Lare, January 29, 2022 - 11:45 am UTC
New to good database design? Check out Chris Saxon's full fundamentals class.