This discussion is archived
3 Replies Latest reply: May 20, 2013 10:15 AM by Barbara Boehmer RSS

context index not working

cs01kks Newbie
Currently Being Moderated
Hi ,
I am using context index with preference user_datastore. I attached a procedure to it. when I drop and create the index and check dr$index_Name$i table it shows 140 rows. If I do the same thing tommorow dr$index_name$i table shows 180 rows. total number of rows in the table remained same on both days. Also, i dont get the results consistently, one day I get the results with CONSISTS clause and next day it wonnt show up. Any clues on fixing this behaviour. I would also appreciate on your inputs on how should I sync up this index with out COMMIT mode. My main issue is, I do not get intended results for this posted select query.


BEGIN
   ctx_ddl.drop_preference ('ni_emp_datastore');
   ctx_ddl.drop_preference ('ni_eng_case_insensitive');
END;

BEGIN
   ctx_ddl.create_preference ('ni_emp_datastore', 'user_datastore');
   ctx_ddl.set_attribute ('ni_emp_datastore', 'procedure', 'index_ni_docs');
   ctx_ddl.create_preference ('ni_eng_case_insensitive', 'BASIC_LEXER');
   ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'base_letter', 'YES');
   ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'mixed_case', 'NO');
   ctx_ddl.set_attribute ('ni_eng_case_insensitive', 'printjoins', '-.');

   COMMIT;
END;

DROP INDEX ni_documents_t1;

CREATE INDEX ni_documents_t1
   ON ni_documents (file_name)
   INDEXTYPE IS ctxsys.context
   PARAMETERS ( 'datastore ni_emp_datastore lexer ni_eng_case_insensitive sync (on commit)' );

EXEC DBMS_STATS.GATHER_TABLE_STATS('APPL', 'ni_documents', cascade=>TRUE);

SELECT *
  FROM ni_documents
 WHERE file_name LIKE '%123%';
This gives the following:
 PR121230.PDF
PR121235.PDF
12338.pdf

I don't get any results with CONTAINS clause.

SELECT *
  FROM ni_documents
 WHERE contains (file_name, '%123%') > 0; --- no rows returned
CREATE OR REPLACE PROCEDURE index_ni_docs (p_id IN ROWID, p_lob IN OUT CLOB)
AS
BEGIN
   FOR x
      IN (SELECT file_name,
                 DOC.status,
                 subs,
                 division,
                 department,
                 attribute_1,
                 attribute_2,
                 attribute_3,
                 attribute_4,
                 attribute_5,
                 (SELECT    dst.document_subtype_code
                         || ' '
                         || dt.document_type_code
                         || ' '
                         || dst.description
                         || ' '
                         || dt.description
                    FROM ni_doc_st dst, ni_doc_typ dt
                   WHERE dst.document_subtype_id = doc.document_subtype_id
                         AND dt.document_type_id = dst.document_type_id)
                    typ_subtyp,
                 TO_CHAR (DOCUMENT_DATE, 'YYYY') DOCUMENT_DATE_YEAR,
                 TO_CHAR (DOC.LAST_UPDATE_DATE, 'MM/DD/YYYY') MODIFIED
            FROM ni_documents doc
           WHERE doc.ROWID = p_id)
   LOOP
      BEGIN
         IF x.file_name IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.file_name) + 1,
                                  x.file_name || ' ');
         END IF;
         IF x.status IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.status) + 1,
                                  x.status || ' ');
         END IF;
         IF x.subs IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.subsidiary) + 1,
                                  x.subsidiary || ' ');
         END IF;
         IF x.division IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.division) + 1,
                                  x.division || ' ');
         END IF;

         IF x.department IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.department) + 1,
                                  x.department || ' ');
         END IF;
         IF x.attribute_1 IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.attribute_1) + 1,
                                  x.attribute_1 || ' ');
         END IF;

         IF x.typ_attrib_2 IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.typ_attrib_2) + 1,
                                  x.typ_attrib_2 || ' ');
         END IF;
         IF x.typ_attrib_3 IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.typ_attrib_3) + 1,
                                  x.typ_attrib_3 || ' ');
         END IF;
         IF x.typ_attrib_4 IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.typ_attrib_4) + 1,
                                  x.typ_attrib_4 || ' ');
         END IF;

         IF x.attribute_5 IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.empno) + 1,
                                  x.empno || ' ');
         END IF;

         IF x.typ_subtyp IS NOT NULL
         THEN
            DBMS_LOB.writeappend (p_lob,
                                  LENGTH (x.typ_subtyp) + 1,
                                  x.typ_subtyp || ' ');
         END IF;
         ni_api_pkg.msg (0, '[TEXTVAL] ' || SUBSTR (p_lob, 1, 2000));
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE ('error');
      END;
   END LOOP;
END;
/
please advice

Thanks
kumar

Edited by: cs01kks on May 20, 2013 11:08 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points