读取oracle bfile字段,oracle bfile字段创建全文索引

SQL> CREATE TABLE GC_ConcursoDoc_Idx (

2     id NUMBER,

3     nombre_archivo VARCHAR2(255),

4     fecha_upload VARCHAR2(10),

5     filesize VARCHAR2(20),

6     mime VARCHAR2(50),

7     content BFILE,

8   CONSTRAINT doc_pk PRIMARY KEY (id)

9  );

Table created.

SQL> CREATE OR REPLACE DIRECTORY GC_ConcursosDocs AS '/u01/Concursos';

Directory created.

SQL> CREATE INDEX GC_ConcursoDoc_CTX ON GC_ConcursoDoc_Idx(content) INDEXTYPE IS CTXSYS.CONTEXT ;

Index created.

SQL> CREATE SEQUENCE GC_CONCURSODOC_SEQ

2  START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;

Sequence created.

SQL> CREATE OR REPLACE PROCEDURE Loadfile_Concurso

2   (

3    p_file_name  IN  GC_ConcursoDoc_Idx.nombre_archivo%TYPE,

4    p_upload_date IN GC_ConcursoDoc_Idx.fecha_upload%TYPE,

5    p_filesize IN GC_ConcursoDoc_Idx.filesize%TYPE,

6    p_mime IN GC_ConcursoDoc_Idx.mime%TYPE

7  ) AS

8     index_name varchar2(20) := 'GC_ConcursoDoc_CTX';

9   BEGIN

10     INSERT INTO GC_ConcursoDoc_Idx (id, nombre_archivo, fecha_upload,filesize, mime, content)

11     VALUES (GC_CONCURSODOC_SEQ.NEXTVAL, p_file_name, p_upload_date, p_filesize, p_mime,BFILENAME

('GC_CONCURSOSDOCS',p_file_name));

12     COMMIT;

13   END;

14  /

Procedure created.SQL> exec Loadfile_Concurso('/05/24686@Bases de Cotizaci�n 27-04.txt', to_date('26/07/2004','dd/MM/Y

YYY'), '501', 'txt');

PL/SQL procedure successfully completed.

BFILE字段上建立的索引,但它就是不能产生索引词,郁闷呀SQL> SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp;

ERR_INDEX_NAME ERR_TIMES ERR_TEXT

------------------------------ --------- -----------------------------------------------------

GC_CONCURSODOC_CTX 30-OCT-08 DRG-11207: user filter command exited with status 11

查看ctx_user_index_errors  发现filter出错！原因是全文检索使用filter，

重新建立索引CREATE INDEX GC_ConcursoDoc_CTX ON GC_ConcursoDoc_Idx(content) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ( FILTER CTXSYS.NULL_FILTER' )

索引建立成功，产生了索引词