我现在用oracle 9i的Oracle Text来做全文检索。现在想将一个表中的多个列(包含blob列)创建到一个索引。我用的是USER_DATASTORE的BLOB类型。但是索引能够创建成功,可是查不到任何数据,不知道是为什么? 具体实施如下: -- 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;
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;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货