执行查询,结果如下:
SQL> select * from testmis.zindex_info where contains(内容,'细砂')>0;
select * from testmis.zindex_info where contains(内容,'细砂')>0
*
ERROR 位于第 1 行:
ORA-20000: interMedia Text error:
DRG-10599: 列没有索引
SQL> select * from testmis.zindex_info where contains(内容,'细砂')>0;
select * from testmis.zindex_info where contains(内容,'细砂')>0
*
ERROR 位于第 1 行:
ORA-20000: interMedia Text error:
DRG-10599: 列没有索引
下载连接:
http://www.itpub.net/magazine/itpub200304.rar
itpub电子杂志第四期 这个是里面的一些内容利用Oracle Text,你可以回答如“在存在单词‘Oracle’的行同时存在单词’Corporation’而且两单词间距不超过10个单词的文本‘,’查询含有单词’Oracle’或者单词’ california’的文本,并且将结果按准确度进行排序‘,’含有词根train的文本‘。以下的sql代码实现了如上功能。我们且不管这些语法是如何使用的。
DROP INDEX index mytext_idx
/
CREATE INDEX mytext_idx
ON mytext( thetext )
INDEXTYPE is CTXSYS.CONTEXT
/
SELECT id
FROM mytext
WHERE contains (thetext, 'near((Oracle,Corporation),10)') > 0
/
SELECT score (1), id
FROM mytext
WHERE contains (thetext, 'Oracle or california', 1) > 0
ORDER BY score (1) DESC
/
SELECT id
FROM mytext
WHERE contains (thetext, '$train') > 0;
1.2设置
首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色,意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。
还可以检查服务器是否有对PLSExtProc服务的监听。
lsnrctl status
should give status
LSNRCTL for Solaris: Version
8.1.5.0.0 - Production on 31-MAR-99 18:57:49
Home: http://www.itpub.net Mail: [email protected]
ITPub电子杂志(总第四期) 第6页
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production
Start Date 30-MAR-99 15:53:06
Uptime 1 days 3 hr. 4 min. 42 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File
/private7/Oracle/Oracle_home/network/admin/listener.ora
Listener Log File
/private7/Oracle/Oracle_home/network/log/listener.log
Services Summary...
PLSExtProc has 1 service handler(s)
oco815 has 3 service handler(s)
The command completed successfully
Oracle 是通过所谓的‘外部调用功能’(external procedure)来实现intermedia的。
B. Create a user/table/index/query thus: As SYS or SYSTEM:
----------------------------------------------------------
CREATE USER ctxtest IDENTIFIED BY ctxtest;
GRANT CONNECT, RESOURCE, ctxapp TO ctxtest;
----------------------------------------------------------
Do any other grants, quotas, tablespace etc. for the new user. As CTXTEST:
----------------------------------------------------------
CREATE TABLE quick (
quick_id NUMBER PRIMARY KEY,
text VARCHAR(80));
INSERT INTO quick
(quick_id, text)
VALUES (1, 'The cat sat on the mat');
INSERT INTO quick
Home: http://www.itpub.net Mail: [email protected]
ITPub电子杂志(总第四期) 第7页(quick_id, text)
VALUES (2, 'The quick brown fox jumped over the lazy dog');
COMMIT ; CREATE INDEX quick_text
ON quick ( text )
INDEXTYPE IS ctxsys.CONTEXT;