具体实施如下：
-- KBUSER创建存储过程
create or replace procedure KBUser.P_BKXX_ORATEXT(ID in rowid, B_TMP in out blob) is
begin
for recTmp in (select C_MC, C_JS, B_GXTB from T_BBS_BKXX where rowid = ID)
loop
dbms_lob.writeappend(B_TMP, length(recTmp.C_MC), recTmp.C_MC);
dbms_lob.writeappend(B_TMP, length(recTmp.C_JS), recTmp.C_JS);
dbms_lob.writeappend(B_TMP, dbms_lob.getlength(recTmp.B_GXTB), recTmp.B_GXTB);
end loop;
end;
/
-- CTXSYS创建执行存储过程
create or replace procedure CTXSYS.s_P_BKXX_ORATEXT(ID in rowid, B_TMP in out blob) is
begin
KBUser.P_BKXX_ORATEXT(ID, B_TMP);
end;
/
-- CTXSYS赋权给KBUser
grant execute on s_P_BKXX_ORATEXT to KBUser;
-- KBUser创建Preference
begin
ctx_ddl.drop_preference('UD_BKXX_GXTB');
ctx_ddl.create_preference('UD_BKXX_GXTB', 'user_datastore');
ctx_ddl.set_attribute('UD_BKXX_GXTB', 'procedure', 's_P_BKXX_ORATEXT');
ctx_ddl.set_attribute('UD_BKXX_GXTB', 'output_type', 'BLOB');
end;
/
-- 创建索引
drop index I_BBS_BKXX_ORATEXT force;
create index I_BBS_BKXX_ORATEXT on T_BBS_BKXX(B_GXTB) indextype is ctxsys.context parameters('datastore UD_BKXX_GXTB lexer TAXLEX filter CTXSYS.INSO_FILTER');
-- 刷新索引
begin
ctx_ddl.sync_index('I_BBS_BKXX_ORATEXT', '2M');
end;
/
-- 查询SQL
select C_MC from T_BBS_BKXX where contains(B_GXTB, 'doc') > 0;
-- 相关表结构
create table KBUser.T_BBS_BKXX
(
N_BH number(15) default 1, --编号
C_MC varchar2(32) null, --名称
C_JS varchar2(4000) null, --介绍
B_GXTB blob null, --个性图标
C_GXTBFMT varchar2(20) default 'binary', --文件格式（binary, text）
primary key(N_BH)
) tablespace TBSForum;
