Home » Server Options » Text & interMedia » BFile content index And Network Share (10g, Linux - Windows)
BFile content index And Network Share [message #356146] Wed, 29 October 2008 13:59 Go to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
I'm creating a index from a directory and i'm indexing the content files(docs, pdfs, xls) in a context index with:
CREATE TABLE my_doc (
   id NUMBER,
   file_name VARCHAR2(255),
   upload_date VARCHAR2(10),
   filesize VARCHAR2(20),
   mime VARCHAR2(50),
   content BFILE,
 CONSTRAINT doc_pk PRIMARY KEY (id)
);
CREATE OR REPLACE DIRECTORY documents AS '\\192.168.1.16\Share1';
GRANT READ ON DIRECTORY documents TO ctxsys;

The problem here is that the DB Os is Linux and the share is in a Windows PC how do you define a DIRECTORY in the scenario i've not found a resource on this(NFS-Samba).

The rest of the index is created ok with:
CREATE OR REPLACE PROCEDURE put_file
 (
  p_file_name  IN  my_doc.file_name%TYPE,
  p_upload_date IN my_doc.upload_date%TYPE,
  p_filesize IN my_doc.filesize%TYPE,
  p_mime IN my_doc.mime%TYPE
) AS
   index_name varchar2(20) := 'my_doc_idx';
 BEGIN
   INSERT INTO my_doc (id, file_name, upload_date,
   filesize, mime, content)
   VALUES (my_doc_seq.NEXTVAL, p_file_name, p_upload_date,
   p_filesize, p_mime,
   BFILENAME('DOKUMENTE',p_file_name));
   COMMIT;
END;

exec put_file('test.doc', '10.10.2005', '100', 'doc');


When i get the context errors:
SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp;

i get:

DRG-50857: oracle error in drstldef
ORA-22285: non-existent directory or file for FILEEXISTS operation


Thanks for your support/help.

[Updated on: Wed, 29 October 2008 15:29]

Report message to a moderator

Re: BFile content index And Network Share [message #356184 is a reply to message #356146] Wed, 29 October 2008 19:19 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Well now i've created a mounted volume in Linux Os it's visible and oracle has the proper rights over this, now the problem is:

Root:/path/smbdir/
File is: {root}/01/Test.doc

Now the problem seem to be: ORA-2401 Ilegal parameter in OCI Lob function

Any one?
Re: BFile content index And Network Share [message #356201 is a reply to message #356184] Wed, 29 October 2008 23:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please post a copy and paste of a complete run, not just the alleged pieces. There are a lot of mismatches in what you have posted. You have created a directory object named documents, but you have referenced a directory object named dokumente in your procedure. You have provided an error number 2401 with an error message that don't match, so one or the other or both is false. You have listed a fileexists error when there is no fileexists in the code you provided. Also, can you do a successful run on a normal directory, so that you can narrow the problem down to the type of directory path access and eliminate all other factors? You need to make sure the rest of the code is correct first. Please try to provide something like what I have provided below. It is important not to leave out any pieces as it may be those pieces that are causing the problem.

SCOTT@orcl_11g> CREATE TABLE my_doc (
  2  	id	     NUMBER,
  3  	file_name    VARCHAR2(255),
  4  	upload_date  VARCHAR2(10),
  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 dokumente AS 'c:\oracle11g';

Directory created.

SCOTT@orcl_11g> CREATE SEQUENCE my_doc_seq;

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE put_file
  2   (
  3    p_file_name   IN  my_doc.file_name%TYPE,
  4    p_upload_date IN my_doc.upload_date%TYPE,
  5    p_filesize    IN my_doc.filesize%TYPE,
  6    p_mime	     IN my_doc.mime%TYPE
  7  )
  8  AS
  9    index_name	varchar2(20) := 'my_doc_idx';
 10  BEGIN
 11    INSERT INTO my_doc (id, file_name, upload_date,
 12    filesize, mime, content)
 13    VALUES (my_doc_seq.NEXTVAL, p_file_name, p_upload_date,
 14    p_filesize, p_mime,
 15    BFILENAME('DOKUMENTE', p_file_name));
 16    COMMIT;
 17  END;
 18  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> exec put_file('test1.doc', '10.10.2005', '100', 'doc');

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec put_file('banana.pdf', '10.10.2005', '100', 'pdf');

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec put_file('test.xls', '10.10.2005', '100', 'xls');

PL/SQL procedure successfully completed.

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

  COUNT(*)
----------
         3

SCOTT@orcl_11g> CREATE INDEX my_doc_idx
  2  ON my_doc (content)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> SELECT COUNT(*) FROM dr$my_doc_idx$i
  2  /

  COUNT(*)
----------
       311

SCOTT@orcl_11g> 


Re: BFile content index And Network Share [message #356204 is a reply to message #356184] Wed, 29 October 2008 23:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you do get all of the code to work on a regular directory, so you have narrowed the problem down to the directory access, then please see the article below and scroll down to the section on windows shares and mapped drives.

http://www.jlcomp.demon.co.uk/faq/utl_file.html

Re: BFile content index And Network Share [message #356404 is a reply to message #356204] Thu, 30 October 2008 11:20 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Thanks Barabara, you pointed me to the right direction the procedure name and the directory was different, also the the directory name is case sensitive, Thanks! Laughing

It's working now, in this scenario the file content is stored in the BFile? all of it? i was looking to just save a reference to the file and a keyword index only.

I did some test but is some doc file it got this error:
DRG-11207: user filter command exited with status 11
DRG-50900: text query parser error on line 1, column 8
DRG-50905: invalid score threshold <textquery
DRG-50900: text query parser error on line 1, column 8
DRG-50905: invalid score threshold <textquery

Runnig Oracle 10g 10.2.0.3.0

[Updated on: Thu, 30 October 2008 11:29]

Report message to a moderator

Re: BFile content index And Network Share [message #356413 is a reply to message #356404] Thu, 30 October 2008 12:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I can't begin to guess at the problem just by viewing the error messages. You need to post an actual run of your current code as previously requested. If you just want to store a reference to a file, such as a path, and be able to search using Oracle Text then you probably should use a file_datastore instead of what you have. Syntax and examples for the file_datastore are available in the online documentation.
Re: BFile content index And Network Share [message #356468 is a reply to message #356413] Thu, 30 October 2008 18:36 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Ok, theres the output...
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 30 17:08:42 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

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 
  2      PARAMETERS ('LEXER GTCProd_lex SYNC ( ON COMMIT)');

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.

The problem before described begins with a file with a latin characters, like this filename "24686@Bases de Cotizaciòn 27-04.doc" note the accented "Cotizaciòn"
SQL> exec Loadfile_Concurso('/05/24686@Bases de Cotizaciòn 27-04.doc', to_date('26/07/2004','dd/MM/Y
YYY'), '501', 'doc');

PL/SQL procedure successfully completed.

Errors:
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


But when manually looking for the file:

SQL> DECLARE
  2      v_file BFILE :=BFILENAME ('GC_CONCURSOSDOCS', '/05/24686@Bases de Cotizaciòn 27-04.doc');
  3  BEGIN
  4  IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
  5   DBMS_OUTPUT.PUT_LINE ('File exists.');
  6   ELSIF DBMS_LOB.FILEEXISTS (v_file) = 0 THEN
  7   DBMS_OUTPUT.PUT_LINE ('File does not exist');
  8   ELSE
  9   DBMS_OUTPUT.PUT_LINE ('Unable to test existence');
 10  END IF;
 11  END;
 12  /
File exists.

PL/SQL procedure successfully completed.

Before this particular error, the problem was the mounting the directory with a charset, but as you see this was solved.
Any info/help on this ...?? Thanks!

[Updated on: Thu, 30 October 2008 18:38]

Report message to a moderator

Re: BFile content index And Network Share [message #356669 is a reply to message #356468] Fri, 31 October 2008 16:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
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. Also your fecha_upload should be DATE datatype, not VARCHAR2. However, the error that you are receiving regarding a user_filter indicates that you are using a user_filter, so once again the error is due to a part of the code that you have not provided. Why not just use ctxsys.auto_filter? Please see the demonstration below.

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%')
--------------------------------------------------------------------------------
<b>testing</b> on halloween
This is the original contents of <b>test1</b>.doc.






SCOTT@orcl_11g> 

[Updated on: Fri, 31 October 2008 16:40]

Report message to a moderator

Re: BFile content index And Network Share [message #357018 is a reply to message #356669] Mon, 03 November 2008 09:48 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Hi Barbara, i 've recreated the index with:

Quote:

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

Index created.



Still have the same error: DRG-11207: user filter command exited with status 11 when:

Quote:
SQL> exec Loadfile_Concurso('/05/24686@Bases de Cotizaciòn 27-04.doc', to_date('26/07/2004','dd/MM/Y
YYY'), '501', 'doc');

PL/SQL procedure successfully completed.



Seems to be all on the filter... because using this:
Quote:
SQL> DECLARE
2 v_file BFILE :=BFILENAME ('GC_CONCURSOSDOCS', '/05/24686@Bases de Cotizaciòn 27-04.doc');
3 BEGIN
4 IF DBMS_LOB.FILEEXISTS (v_file) = 1 THEN
5 DBMS_OUTPUT.PUT_LINE ('File exists.');
6 ELSIF DBMS_LOB.FILEEXISTS (v_file) = 0 THEN
7 DBMS_OUTPUT.PUT_LINE ('File does not exist');
8 ELSE
9 DBMS_OUTPUT.PUT_LINE ('Unable to test existence');
10 END IF;
11 END;
12 /
File exists.

PL/SQL procedure successfully completed.



It finds the file and also when it's inserted in the DB the Bfile has te file.

Now I think, i'm using auto-filter(look the index creation script). Is there any diagnose, debug, or more details of the error? it looks very truncated error response, i've been searching this error codes without luck in the oracle site theres no references to this exit codes of the filters, then again it's using the auto_filter in the index creation script or theres another setting to this?? by the way i'm using RHEL 4

Also, i'm showing all the code that i'm using. What part of the code do you think is missing?

[Updated on: Mon, 03 November 2008 10:07]

Report message to a moderator

Re: BFile content index And Network Share [message #357054 is a reply to message #357018] Mon, 03 November 2008 13:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Theoretically, the only way that you should receive:

DRG-11207: user filter command exited with status 11

is if you are using a user_filter, but perhaps not.

The rest of your errors:

DRG-50900: text query parser error on line 1, column 8
DRG-50905: invalid score threshold <textquery
DRG-50900: text query parser error on line 1, column 8
DRG-50905: invalid score threshold <textquery

seem to indicate that you are using a query template and that there is either some error in your syntax or you have exceeded some sort of limit. I suppose it is possible that this could then result in the previous error sort of generally referring to an inability to return from whatever filter is being used. So, what is missing now is a complete copy and paste of the specific query and criteria, that produces the error, including the error, not disjointed pieces. It may be that your search crtieria is too broad or you have applied a weight that is out of range or you are missing something in a progressive relaxation or some such thing. Definitely need to see the query and resulting error stack together.

Re: BFile content index And Network Share [message #357070 is a reply to message #357054] Mon, 03 November 2008 14:46 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Barbara Boehmer wrote on Mon, 03 November 2008 13:11
Theoretically, the only way that you should receive:

DRG-11207: user filter command exited with status 11

is if you are using a user_filter, but perhaps not.

The rest of your errors:

DRG-50900: text query parser error on line 1, column 8
DRG-50905: invalid score threshold <textquery
DRG-50900: text query parser error on line 1, column 8
DRG-50905: invalid score threshold <textquery

seem to indicate that you are using a query template and that there is either some error in your syntax or you have exceeded some sort of limit. I suppose it is possible that this could then result in the previous error sort of generally referring to an inability to return from whatever filter is being used. So, what is missing now is a complete copy and paste of the specific query and criteria, that produces the error, including the error, not disjointed pieces. It may be that your search crtieria is too broad or you have applied a weight that is out of range or you are missing something in a progressive relaxation or some such thing. Definitely need to see the query and resulting error stack together.




Right i'll focus on the error: DRG-11207: user filter command exited with status 11, i'll try to use a updated version of the database, did you tried with a file name like the one i showed you on the SQL's..?

How do i check if the index is using auto filter, because it seems to be the same error like the one before the index without FILTER CTXSYS.AUTO_FILTER?

Do you know any way to debug this ? or get more specific error description?

Thanks for your help

[Updated on: Mon, 03 November 2008 14:48]

Report message to a moderator

Re: BFile content index And Network Share [message #357076 is a reply to message #357070] Mon, 03 November 2008 15:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I think you misunderstood what I said. I am saying that you should focus on finding the query and data that produce the error as this might cause the error, even if there is nothing wrong with the filter.

Re: BFile content index And Network Share [message #357078 is a reply to message #357076] Mon, 03 November 2008 15:51 Go to previous messageGo to next message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Ahhh i got you, well let me describe the scenario:


  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òn 27-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


Now the only description of the error i got is
DRG-11207: user filter command exited with status 11
this is found when:
SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors ORDER BY err_timestamp

Thats why i said that i needed to focus on the DRG-11207... Error

[Updated on: Mon, 03 November 2008 15:56]

Report message to a moderator

Re: BFile content index And Network Share [message #357080 is a reply to message #357078] Mon, 03 November 2008 16:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Stop using the long file name. Use the short file name as previously discussed. Also, you have a space in your year Y YYY that does not belong there. Then post an actual copy and paste of a continuous run of all the steps instead of just describing them. What you need to post is something like what I have posted below, which shows that loading with the short file name produces rows in the domain index tables and does not log any errors and attempting to load using the long file name does not produce additional rows and does log an error.

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òn 27-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òn 27-04.doc


SCOTT@orcl_11g> 





Re: BFile content index And Network Share [message #357082 is a reply to message #357078] Mon, 03 November 2008 16:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Additionally, here is an example of what might produce the other error that you are getting, by attempting to explain a progressive query:

SCOTT@orcl_11g> begin
  2    ctx_query.explain
  3  	 (index_name => 'GC_ConcursoDoc_CTX',
  4  	  text_query => '<query>
  5  			   <textquery grammar="CONTEXT">
  6  			     <progression>
  7  			       <seq>test%</seq>
  8  			     </progression>
  9  			   </textquery>
 10  			 </query>',
 11  	  explain_table => 'test_explain',
 12  	  sharelevel => 0,
 13  	  explain_id => 'Test');
 14  end;
 15  /
begin
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-50900: text query parser error on line 2, column 23
DRG-50905: invalid score threshold <textquery
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_QUERY", line 374
ORA-06512: at line 2




as opposed to an allowable explain of a standard, non-progressive query on the same index:

SCOTT@orcl_11g> begin
  2    ctx_query.explain
  3  	 (index_name => 'GC_ConcursoDoc_CTX',
  4  	  text_query => 'test%',
  5  	  explain_table => 'test_explain',
  6  	  sharelevel => 0,
  7  	  explain_id => 'Test');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


Re: BFile content index And Network Share [message #357085 is a reply to message #357082] Mon, 03 November 2008 17:20 Go to previous message
redonisc
Messages: 20
Registered: March 2008
Location: Guatemala, C.A.
Junior Member
Ok thanks i'll try to get the short names in to the process, in this scenario or with file_datastore the full document is stored in the database? or it's just one time index and token load??
To be my self clear, the functionality required from this is not duplicate the store of the document, of course the internal data of the index will be in any case stored.

Thanks
Previous Topic: xml search + single quote handling (merged)
Next Topic: File_DataStore Performance.
Goto Forum:
  


Current Time: Thu Mar 28 03:53:24 CDT 2024