working with BFile content and index

Bfile locator stay in table columns and file are stayed in hard drive.

Steps
=========
0.Create the Oracle directory object

1. Create the table.

2. Create the sequence.

3. Create the index.

4. Create procedure Loadfile_Concurso

5. Load the document with: exec Loadfile_Concurso('/05/24686@Bases de Cotizaci򮠲7-04.doc', to_date('26/07/2004','dd/MM/Y YYY'), '501', 'doc');

6. No errors... executing any of the previous.

7. List the rows on the index with: SELECT count(*) FROM dr$GC_ConcursoDoc_CTX$i

8. No rows... no the index table


-----------------------------
SCOTT@orcl_11g> -- 0. Create the Oracle directory object:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY GC_ConcursosDocs AS 'c:\oracle11g';

Directory created.

SCOTT@orcl_11g> -- 1. Create the table.
SCOTT@orcl_11g> CREATE TABLE GC_ConcursoDoc_Idx (
2 id NUMBER,
3 nombre_archivo VARCHAR2(255),
4 fecha_upload DATE,
5 filesize VARCHAR2(20),
6 mime VARCHAR2(50),
7 content BFILE,
8 CONSTRAINT doc_pk PRIMARY KEY (id)
9 );

Table created.

SCOTT@orcl_11g> -- 2. Create the sequence.
SCOTT@orcl_11g> CREATE SEQUENCE GC_CONCURSODOC_SEQ
2 START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;

Sequence created.

SCOTT@orcl_11g> -- 3. Create the index.
SCOTT@orcl_11g> CREATE INDEX GC_ConcursoDoc_CTX ON GC_ConcursoDoc_Idx(content)
2 INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.AUTO_FILTER SYNC ( ON COMMIT)');

Index created.

SCOTT@orcl_11g> -- 4. Create procedure Loadfile_Concurso.
SCOTT@orcl_11g> 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 )
8 AS
9 index_name varchar2(20) := 'GC_ConcursoDoc_CTX';
10 BEGIN
11 INSERT INTO GC_ConcursoDoc_Idx (id, nombre_archivo, fecha_upload,filesize, mime, content)
12 VALUES (GC_CONCURSODOC_SEQ.NEXTVAL, p_file_name, p_upload_date, p_filesize, p_mime,BFILENAME
13 ('GC_CONCURSOSDOCS',p_file_name));
14 COMMIT;
15 END;
16 /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- 5. Load with short file name.
SCOTT@orcl_11g> exec Loadfile_Concurso('24686O~1.DOC', to_date('26/07/2004','dd/MM/YYYY'), '501', 'doc');

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- list rows:
SCOTT@orcl_11g> SELECT count(*) FROM dr$GC_ConcursoDoc_CTX$i;

COUNT(*)
----------
2

SCOTT@orcl_11g> -- list errors:
SCOTT@orcl_11g> SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp;

no rows selected

SCOTT@orcl_11g>
SCOTT@orcl_11g> -- 5. Repeat of step 5 with long file name.
SCOTT@orcl_11g> exec Loadfile_Concurso('24686@Bases de Cotizaci򮠲7-04.doc', to_date('26/07/2004','dd/MM/YYYY'), '501', 'doc');

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- list rows:
SCOTT@orcl_11g> SELECT count(*) FROM dr$GC_ConcursoDoc_CTX$i;

COUNT(*)
----------
2

SCOTT@orcl_11g> -- list errors:
SCOTT@orcl_11g> SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp;

ERR_INDEX_NAME ERR_TIMEST
------------------------------ ----------
ERR_TEXT
--------------------------------------------------------------------------------
GC_CONCURSODOC_CTX 03/11/2008
DRG-11101: failed to open file 24686@Bases de Cotizaci򮠲7-04.doc

[There are various problems with long file names with spaces in them on Windows systems. It is better to use the short name that you see with dir/x, usually the first 6 characters, followed by a tilde, followed by a number, then the extension.]

==========================================================
============Another modified Example======================
==========================================================
SCOTT@orcl_11g> HOST DIR c:\oracle11g\2*.doc/x
...
Directory of c:\oracle11g

10/31/2008 02:09 PM 22 24686O~1.DOC 24686orclBases de Cotizaci•n 27-04.doc
1 File(s) 22 bytes
0 Dir(s) 62,669,983,744 bytes free

SCOTT@orcl_11g>

-----------------------------------------------------------------------
SCOTT@orcl_11g> CREATE TABLE GC_ConcursoDoc_Idx (
2 id NUMBER,
3 nombre_archivo VARCHAR2(255),
4 fecha_upload DATE,
5 filesize VARCHAR2(20),
6 mime VARCHAR2(50),
7 content BFILE,
8 CONSTRAINT doc_pk PRIMARY KEY (id)
9 );

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY GC_ConcursosDocs AS 'c:\oracle11g';

Directory created.

SCOTT@orcl_11g> CREATE INDEX GC_ConcursoDoc_CTX ON GC_ConcursoDoc_Idx(content)
2 INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.AUTO_FILTER SYNC ( ON COMMIT)');

Index created.

SCOTT@orcl_11g> CREATE SEQUENCE GC_CONCURSODOC_SEQ
2 START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;

Sequence created.

SCOTT@orcl_11g> 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 )
8 AS
9 index_name varchar2(20) := 'GC_ConcursoDoc_CTX';
10 BEGIN
11 INSERT INTO GC_ConcursoDoc_Idx (id, nombre_archivo, fecha_upload,filesize, mime, content)
12 VALUES (GC_CONCURSODOC_SEQ.NEXTVAL, p_file_name, p_upload_date, p_filesize, p_mime,BFILENAME
13 ('GC_CONCURSOSDOCS',p_file_name));
14 COMMIT;
15 END;
16 /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> exec Loadfile_Concurso('24686O~1.DOC', to_date('26/07/2004','dd/MM/YYYY'), '501', 'doc');

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp;

no rows selected

SCOTT@orcl_11g> exec Loadfile_Concurso('test1.doc', to_date('26/07/2004','dd/MM/YYYY'), '501', 'doc');

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp;

no rows selected

SCOTT@orcl_11g> SELECT COUNT (*) FROM gc_concursodoc_idx
2 /

COUNT(*)
----------
2

SCOTT@orcl_11g> SELECT token_text FROM dr$gc_concursodoc_ctx$i
2 /

TOKEN_TEXT
----------------------------------------------------------------
CONTENTS
DOC
HALLOWEEN
ORIGINAL
TEST1
TESTING

6 rows selected.

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT CTX_DOC.SNIPPET ('gc_concursodoc_ctx', ROWID, 'test%') FROM gc_concursodoc_idx
2 /

CTX_DOC.SNIPPET('GC_CONCURSODOC_CTX',ROWID,'TEST%')
--------------------------------------------------------------------------------
testing on halloween
This is the original contents of test1.doc.

SCOTT@orcl_11g>