8 Replies Latest reply on May 11, 2011 9:12 PM by Barbara Boehmer

    CTX_DOC.FILTER issue with TEXTKEY being invalid??????

    251503
      Hello,

      I am using Oracle 10g. I have a table which looks like (abbreviated):

      CREATE table RPT_SOURCE (
      SOURCE_ID NUMBER(10,0) NOT NULL ENABLE,
      BODY_FORMAT VARCHAR2(10) NOT NULL ENABLE,
      BODY BLOB default empty_blob()
      );

      and an Oracle Text index that looks like:

      CREATE INDEX idx_txt_body ON RPT_SOURCE(BODY)
      INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
      ('FILTER CTXSYS.AUTO_FILTER format column BODY_FORMAT');


      I then have a stored procedure for filtering the BLOB data in the body field (above). The goal here is to have Oracle read the blob data and store the filtered text in a clob field of another table. This type of action is outlined in the Oracle documentation:

      CREATE OR REPLACE PROCEDURE PROC_STORE_BODY_TEXT (INDEX_NAME VARCHAR, TEXTKEY VARCHAR, RESTAB IN OUT CLOB, QUERY_ID NUMBER, PLAINTEXT NUMBER)
      IS
      BEGIN
      IF PLAINTEXT = 1 THEN
      CTX_DOC.FILTER(INDEX_NAME, TEXTKEY, RESTAB, QUERY_ID, TRUE);
      ELSE
      -- Output in HTML
      CTX_DOC.FILTER(INDEX_NAME, TEXTKEY, RESTAB, QUERY_ID, FALSE);
      END IF;
      END;
      /

      I call the above procedure with the following values:
      INDEX_NAME = "idx_txt_body"
      TEXTKEY = "82"
      RESTAB = "filtered_idx_text_output"
      QUERY_ID = 92
      PLAINTEXT =1

      The "filtered_idx_text_output" table listed for RESTAB is defined as (according to the Oracle documentation):

      CREATE TABLE FILTERED_IDX_TEXT_OUTPUT (
      QUERY_ID NUMBER NOT NULL,
      DOCUMENT CLOB,
      CONSTRAINT PK_FILTERED_TEXT PRIMARY KEY (QUERY_ID)
      );

      The problem is that when I run this, I get the Oracle Text error:

      DRG-11445: rowid value is not valid: 82
      ORA-06512: at "CTXSYS.DRUE", line 160
      ......

      The value of 82 is the primary key value in the source_id field of the rpt_source table for a row that contains a document in the index. I have switched to use a variety of values for the TEXTKAY, but I always get the same error.

      Does anyone have a clue as to what is wrong?

      Thanks - Peter
        • 1. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
          Roger Ford-Oracle
          Your RPT_SOURCE doesn't appear to have a primary key column. You need to declare it as

          CREATE table RPT_SOURCE (
          SOURCE_ID NUMBER(10,0) PRIMARY KEY,
          BODY_FORMAT VARCHAR2(10) NOT NULL ENABLE,
          BODY BLOB default empty_blob()
          );

          If there is no primary key column, Text will use the ROWID, hence your error. See the doc for CTX_DOC.SET_KEY_TYPE

          http://download.oracle.com/docs/cd/B10501_01/text.920/a96518/cdocpkg.htm#17976
          • 2. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
            Barbara Boehmer
            There were also a few more errors. Please see the corrected code below.
            SCOTT@orcl_11gR2> CREATE table RPT_SOURCE
              2    (SOURCE_ID    NUMBER   (10,0) NOT NULL ENABLE,
              3       BODY_FORMAT  VARCHAR2 (10)   NOT NULL ENABLE,
              4       BODY          BLOB          default empty_blob(),
              5       CONSTRAINT   pk_rpt_source_id PRIMARY KEY (source_id))
              6  /
            
            Table created.
            
            SCOTT@orcl_11gR2> -- test data:
            SCOTT@orcl_11gR2> INSERT INTO rpt_source (source_id, body_format, body) VALUES
              2    (82, 'BINARY', UTL_RAW.CAST_TO_RAW ('This is a test.'))
              3  /
            
            1 row created.
            
            SCOTT@orcl_11gR2> CREATE INDEX idx_txt_body ON RPT_SOURCE (BODY)
              2  INDEXTYPE IS CTXSYS.CONTEXT
              3  PARAMETERS
              4    ('FILTER      CTXSYS.AUTO_FILTER
              5        format column     BODY_FORMAT')
              6  /
            
            Index created.
            
            SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE PROC_STORE_BODY_TEXT
              2    (INDEX_NAME IN       VARCHAR2,
              3       TEXTKEY    IN       VARCHAR2,
              4       RESTAB        IN       VARCHAR2,
              5       QUERY_ID   IN       NUMBER,
              6       PLAINTEXT  IN       NUMBER)
              7  AS
              8  BEGIN
              9    IF PLAINTEXT = 1 THEN
             10        CTX_DOC.FILTER
             11          (INDEX_NAME, TEXTKEY, RESTAB, QUERY_ID, TRUE);
             12    ELSE
             13        -- Output in HTML
             14        CTX_DOC.FILTER
             15          (INDEX_NAME, TEXTKEY, RESTAB, QUERY_ID, FALSE);
             16    END IF;
             17  END PROC_STORE_BODY_TEXT;
             18  /
            
            Procedure created.
            
            SCOTT@orcl_11gR2> SHOW ERRORS
            No errors.
            SCOTT@orcl_11gR2> CREATE TABLE FILTERED_IDX_TEXT_OUTPUT
              2    (QUERY_ID    NUMBER NOT NULL,
              3       DOCUMENT    CLOB,
              4       CONSTRAINT  PK_FILTERED_TEXT PRIMARY KEY (QUERY_ID))
              5  /
            
            Table created.
            
            SCOTT@orcl_11gR2> BEGIN
              2    CTX_DOC.SET_KEY_TYPE ('PRIMARY_KEY');
              3    PROC_STORE_BODY_TEXT
              4        (INDEX_NAME => 'idx_txt_body',
              5         TEXTKEY    => '82',
              6         RESTAB     => 'filtered_idx_text_output',
              7         QUERY_ID   => 92,
              8         PLAINTEXT  => 1);
              9  END;
             10  /
            
            PL/SQL procedure successfully completed.
            
            SCOTT@orcl_11gR2> select * from filtered_idx_text_output
              2  /
            
              QUERY_ID
            ----------
            DOCUMENT
            --------------------------------------------------------------------------------
                    92
            This is a test.
            
            
            1 row selected.
            
            SCOTT@orcl_11gR2> 
            • 3. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
              Barbara Boehmer
              The following is a response to the following two related posts of yours that should have been in this Text forum:

              Obtaining which Oracle Text tokens belong to which "file"... is it possible

              Obtaining which Oracle Text tokens belong to which "file"... is it possible
              SCOTT@orcl_11gR2> CREATE table RPT_SOURCE
                2    (SOURCE_ID    NUMBER   (10,0) NOT NULL ENABLE,
                3       BODY_FORMAT  VARCHAR2 (10)   NOT NULL ENABLE,
                4       BODY          BLOB          default empty_blob(),
                5       CONSTRAINT   pk_rpt_source_id PRIMARY KEY (source_id))
                6  /
              
              Table created.
              
              SCOTT@orcl_11gR2> -- test data:
              SCOTT@orcl_11gR2> INSERT INTO rpt_source (source_id, body_format, body) VALUES
                2    (6, 'BINARY', UTL_RAW.CAST_TO_RAW ('This is a mountain test.'))
                3  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> INSERT INTO rpt_source (source_id, body_format, body) VALUES
                2    (32, 'BINARY', UTL_RAW.CAST_TO_RAW ('This is another moutain test.'))
                3  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> INSERT INTO rpt_source (source_id, body_format, body) VALUES
                2    (89, 'BINARY', UTL_RAW.CAST_TO_RAW ('more mountain data'))
                3  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> CREATE INDEX idx_txt_body ON RPT_SOURCE (BODY)
                2  INDEXTYPE IS CTXSYS.CONTEXT
                3  PARAMETERS
                4    ('FILTER      CTXSYS.AUTO_FILTER
                5        format column     BODY_FORMAT')
                6  /
              
              Index created.
              
              SCOTT@orcl_11gR2> SELECT t.source_id, i.token_text
                2  FROM   rpt_source t, dr$idx_txt_body$i i
                3  WHERE  CONTAINS (t.body, i.token_text) > 0
                4  ORDER  BY t.source_id, i.token_text
                5  /
              
               SOURCE_ID TOKEN_TEXT
              ---------- ----------------------------------------------------------------
                       6 MOUNTAIN
                       6 TEST
                      32 ANOTHER
                      32 MOUTAIN
                      32 TEST
                      89 DATA
                      89 MORE
                      89 MOUNTAIN
              
              8 rows selected.
              
              SCOTT@orcl_11gR2> 
              • 4. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
                251503
                Thanks for the replies. I appreciate the swift responses. I found out what the issue was. My rpt_source table did have a defined primary key, but it was created as a primary key constraint later in my DDL. The problem was that I created the text index before I created the primary key constraint. Essentailly, the ctxsys.ctx_indexes table contained a null value for my index idx_key_name field. Once I dropped my index and re-created it, all was well.
                • 5. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
                  251503
                  Barbara,

                  Wow, how did you find those other posts? That is good. I appreciate your effort in this. I will try out your solution. Many kudos!!

                  Thanks - Peter
                  • 6. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
                    Barbara Boehmer
                    orlenpa wrote:
                    ... how did you find those other posts? ...
                    All I had to do was click on your profile:

                    http://forums.oracle.com/forums/profile.jspa?userID=248500

                    I usually do that when I see somebody that is new to this Text sub-forum, as it is common that new members don't find the right forum right away and are redirected from other forums. I see that you registered in 2000, but didn't post until recently, so I gather that you are not new to Oracle or the OTN forums, just new to the Text sub-forum.
                    • 7. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
                      488505
                      OK, we are having similar issues. I tried creating the table, inserting the date and creating the index per Barbara's sql above, but when I issue the query I get "no rows returned" because the dr$idx_txt_body$i is empty. Is there a configuration problem?

                      I'm using Windows 2008 server
                      Oracle 11.2.0.1

                      Edited by: user485502 on May 11, 2011 1:59 PM
                      • 8. Re: CTX_DOC.FILTER issue with TEXTKEY being invalid??????
                        Barbara Boehmer
                        Please post your question as a separate topic. You can provide a link to this post if you like. When you create your new topic, please include a copy and paste of the simplest possible test case that reproduces the problem. Please start with just seeing if your context installation is valid and you can just create a simple text index. If that works, then add one thing at a time until you find which thing causes the problem. You can then try selecting from ctx_user_index_errors. You need to eliminate any general problems before checking for key and filtering issues. I have provided an example of the simplest test case below.
                        SCOTT@orcl_11gR2> select status
                          2  from   dba_registry
                          3  where  comp_id = 'CONTEXT'
                          4  /
                        
                        STATUS
                        --------------------------------------------
                        VALID
                        
                        1 row selected.
                        
                        SCOTT@orcl_11gR2> create table test_tab as
                          2  select * from dual
                          3  /
                        
                        Table created.
                        
                        SCOTT@orcl_11gR2> create index test_idx
                          2  on test_tab (dummy)
                          3  indextype is ctxsys.context
                          4  /
                        
                        Index created.
                        
                        SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
                          2  /
                        
                        TOKEN_TEXT
                        ----------------------------------------------------------------
                        X
                        
                        1 row selected.
                        
                        SCOTT@orcl_11gR2> select * from test_tab
                          2  where  contains (dummy, 'x') > 0
                          3  /
                        
                        D
                        -
                        X
                        
                        1 row selected.
                        
                        SCOTT@orcl_11gR2>