1 2 Previous Next 18 Replies Latest reply: Jul 10, 2012 5:49 AM by Frieda RSS

    Problem with ctx_doc.ifilter: numeric or value error

    Frieda
      10.2.0.5
      Hi,

      I am trying to help a customer who migrated their production database (RAC) to 10.2.0.5 and to a new machine. Unfortunately I cannot have a look in their database, I can only asked questions. They are using an user_datastore with ctx_doc.ifilter. After the upgrade they made an index rebuild yesterday which worked fine, the documents are still searchable. But since this morning they are getting always the following indexing error for all new documents:

      ERR_INDEX_NAME: DOK_ANHANG_IM
      ERR_TIMESTAMP: 20.06.2012 09:33:36
      ERR_TEXTKEY: AAAT4lAAHAADQc5AAD
      ERR_TEXT: DRG-12604: execution of user datastore procedure has failed
      DRG-50857: oracle error in drsinopen
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "CTXSYS.DRUE", line 183
      ORA-06512: at "CTXSYS.CTX_DOC", line 1480
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512

      The extproc listener seems to be ok, ctx_adm.test_extproc gives no error.
      When they comment out the line with ctx_doc.ifilter in the datastore procedure then they are not getting any errors at all, so it must be the ifilter procedure.
      ctx_doc.markup is also creating an error.

      I have no ideas, why ctx_doc.ifilter could have started creating errors this morning.

      Any hints what else could be wrong? What else to check?
        • 1. Re: Problem with ctx_doc.ifilter: numeric or value error
          Roger Ford-Oracle
          Is ctx_doc.ifilter working outside of the user datastore?

          Can you try calling the user datastore procedure from a separate function? For example if your table is called mytable and your user datastore procedure is called user_datastore_proc (and returns a CLOB) then try something like:
          set serveroutput on
          
          DECLARE
            my_rowid rowid;
            my_clob  clob;
          BEGIN
            select rowid into my_rowrid from mytable where rownum = 1;
            dbms_lob.createtemporary( my_clob, true );
            user_datastore_proc( my_rowid,  my_clob );
            dbms_output.put_line ( substr( my_clob, 1, 255) );
          END;
          /
          You can adjust the line "select rowid into my_rowrid from mytable where rownum = 1" to run the user ds on a specific row, or you can leave that line out and take the rowid value directly from CTX_USER_INDEX_ERRORS and instead use
          user_datastore_proc( chartorowid( 'ABC123ABC123ABC123'), my_clob);
          Edited by: Roger Ford on Jun 20, 2012 4:13 AM
          • 2. Re: Problem with ctx_doc.ifilter: numeric or value error
            Frieda
            Roger, i forget to mention, I've done that already, they are getting the same error.
            Here is the procedure I always use for testing the datastore procedures:
            set serveroutput on
            set long 32000
            set pages 10000
            set wrap on
            set linesize 120
            declare
            io_clob clob:= null ;
            buff varchar2(4000);
            amnt integer;
            i integer:=0;
            begin
            dbms_output.enable(20000);
            for j in
            (
            select o.rowid from dok_anhang o
            where dok_id in (20888)
            )
            loop
            dbms_lob.CreateTemporary
            (io_clob, true, dbms_lob.session);
            prcd_index_dok_anhang(j.rowid, io_clob);
            insert into output values(io_clob);
            commit;
            end loop;
            null;
            end;
            /
            set long 32000
            set pages 10000
            set head off
            select * from output;

            Edited by: Anne-Marie Rosa on Jun 20, 2012 4:34 AM
            • 3. Re: Problem with ctx_doc.ifilter: numeric or value error
              Roger Ford-Oracle
              OK ... are you able to post the code of the "prcd_index_dok_anhang" procedure?
              • 4. Re: Problem with ctx_doc.ifilter: numeric or value error
                Frieda
                CREATE OR REPLACE PROCEDURE EPOS_NEU."PRCD_INDEX_DOK_ANHANG" (rid in rowid
                , io_loc in out nocopy clob)
                /******************************************************************************
                ******************************************************************************/
                is
                buf varchar2(32000) := null;
                v_step varchar2(100);
                begin
                v_step:= 'begin';
                for c1 in (select '</DOKTEXT> <ANW_ID>'||dok_anw_id||'</ANW_ID>'
                ||'<DOK_ID>'||dok_id||'</DOK_ID>'
                ||'<NAME>'||dok_name||'</NAME>'
                ||'<MIME_TYPE>'||mime_type||'</MIME_TYPE>'
                ||'<SIZE>'||doc_size||'</SIZE>'
                ||'<INHALT>'||dok_inhalt||'</INHALT>' buffer
                ,blob_content
                ,mime_type
                from epos_neu.dok_anhang
                where rowid = rid)
                loop
                buf := '<DOKTEXT>';
                dbms_lob.writeappend(io_loc, length(buf), buf);

                if substr(c1.mime_type,1,5) !='image'
                then
                     v_step:= 'ifilter';
                     ctx_doc.ifilter(c1.blob_content, io_loc);
                end if;

                buf := c1.buffer;
                v_step:= 'writeappend';
                dbms_lob.writeappend(io_loc, length(buf), buf);
                end loop;
                exception
                when others then
                     epos_neu.prcd_programm_fehler('prcd_index_dok_anhang: '||v_step,
                SQLCODE,
                SQLERRM,
                                                   epos_neu.PKG_EPOS_Neu_Security.FUND_GETCONTEXT('PP_NR'));
                raise;
                end;
                /
                • 5. Re: Problem with ctx_doc.ifilter: numeric or value error
                  Herald ten Dam
                  Hi,

                  have a look at document "USER_DATASTORE Procedure for Text Index fails DRG-12604 DRG-50857 [ID 730503.1]" at Oracle Support. Your are using dbms_lob.writeappend. The buffer to extend a lob can only be 4k, so if you are exceeding this limmit the error is thrown. In above document the workaround is given.

                  Herald ten Dam
                  http://htendam.wordpress.com
                  • 6. Re: Problem with ctx_doc.ifilter: numeric or value error
                    Roger Ford-Oracle
                    Good catch, Herald. That certainly looks like the same issue.

                    The code looks odd to me. We see:
                         buf := '<DOKTEXT>';
                         dbms_lob.writeappend(io_loc, length(buf), buf);
                    but then shortly afterwards do:
                               v_step:= 'ifilter';
                               ctx_doc.ifilter(c1.blob_content, io_loc);
                    So we're passing an already-populated clob to ctx_doc.ifilter. I wouldn't expect to do that - we'd normally pass an empty clob to ifilter. Perhaps this is the problem? ifilter sees som text in io_loc so it tries to append rather than overwrite, and hence the error if it tries to append more than 4K.

                    How about this instead:
                    CREATE OR REPLACE PROCEDURE EPOS_NEU."PRCD_INDEX_DOK_ANHANG" (rid in rowid
                                               , io_loc in out nocopy clob)
                    /******************************************************************************
                    ******************************************************************************/
                     is
                       buf varchar2(32000) := null;
                       v_step     varchar2(100);
                       temp_clob  clob;
                     begin
                       v_step:= 'begin';
                       for c1 in (select '</DOKTEXT> <ANW_ID>'||dok_anw_id||'</ANW_ID>'
                                         ||'<DOK_ID>'||dok_id||'</DOK_ID>'
                                         ||'<NAME>'||dok_name||'</NAME>'
                                         ||'<MIME_TYPE>'||mime_type||'</MIME_TYPE>'
                                         ||'<SIZE>'||doc_size||'</SIZE>'
                                         ||'<INHALT>'||dok_inhalt||'</INHALT>' buffer
                                         ,blob_content
                                         ,mime_type
                                    from epos_neu.dok_anhang
                                    where rowid = rid)
                       loop
                         buf := '<DOKTEXT>';
                         dbms_lob.writeappend(io_loc, length(buf), buf);
                    
                         if substr(c1.mime_type,1,5) !='image'
                         then
                               v_step:= 'ifilter';
                                  dbms_lob.createtemporary(temp_clob, true);
                               ctx_doc.ifilter(c1.blob_content, temp_clob);
                                  io_loc := io_loc || temp_clob;
                         end if;
                    
                         buf := c1.buffer;
                         v_step:= 'writeappend';
                         dbms_lob.writeappend(io_loc, length(buf), buf);
                       end loop;
                     exception
                       when others then
                         epos_neu.prcd_programm_fehler('prcd_index_dok_anhang: '||v_step,
                                               SQLCODE,
                                                SQLERRM,
                                                       epos_neu.PKG_EPOS_Neu_Security.FUND_GETCONTEXT('PP_NR'));
                       raise;
                     end;
                    /
                    That uses temp_clob and concatenates it the "easy way" with io_loc after the filtering. Doing an ordinary SQL concatenation certainly works where the clob is > 4000 characters, though may be less efficient than looping over the lob with dbms_lob.read and dbms_lob.writeappend.
                    • 7. Re: Problem with ctx_doc.ifilter: numeric or value error
                      Frieda
                      The odd thing about it is, that the procedure is working correct on 10.2.0.4 for several years in several installations. I just uploaded a document in 10.2.0.4 with a size of 509823, it seems to be indexed, at least i can search for an entry at the end of the document and ctx_doc.markup is displaying the document completely.

                      ctx_doc.ifilter seems to append the whole clob to io_loc. I can try your solution with the tempory lob but I am afraid it will slow down the indexing process.
                      • 8. Re: Problem with ctx_doc.ifilter: numeric or value error
                        Roger Ford-Oracle
                        I'm afraid I can't explain what might have changed.

                        I suspect that any slow-down will be minimal - the cost of an extra clob allocation and concatenation probably isn't high compared to the cost of running that SELECT - certainly if the data is being fetched from disk.
                        • 9. Re: Problem with ctx_doc.ifilter: numeric or value error
                          Frieda
                          Thank you, Roger,

                          the problem is that I hate to make untested changes on someone else's production database and the procedure is working fine at several other installations. They do not have an similar Test database to test the changed procedure, they only have 10.2.0.4 and there everything was ok.
                          I thought that it might have something to do with the new installation, we once had the problem that TNS_ADMIN was pointing to an old ORACLE_HOME and so lots of documents could not be indexed but some could. But here TNS_ADMIN seems to be ok on both RAC-Nodes.
                          I think I have to postpone the problem until Monday, because I have to leave.
                          • 10. Re: Problem with ctx_doc.ifilter: numeric or value error
                            Frieda
                            The customer created a new datastore procedure, which does not use writeappend anymore just ctx_doc.ifilter with a temporary clob, but he still is getting an error when testing the procedure with a script as you can see below.
                            CREATE OR REPLACE PROCEDURE TEST_INDEX_DOK_ANHANG (rid in rowid
                            , io_loc in out nocopy clob)
                            /******************************************************************************
                            ******************************************************************************/
                            
                            is
                            
                            --buf varchar2(32000) := null;
                            
                            v_step varchar2(100);
                            temp_clob clob;
                            begin
                            v_step:= 'begin';
                            for c1 in (select blob_content,mime_type from dok_anhang where rowid = rid)
                                loop
                                    if substr(c1.mime_type,1,5) !='image'
                                    then
                                        v_step:= 'ifilter';
                                        dbms_lob.createtemporary(temp_clob, true);
                                        ctx_doc.ifilter(c1.blob_content, temp_clob);
                                    end if;
                                    dbms_lob.freetemporary(temp_clob);
                                end loop;
                            end;
                            /
                            SQL> declare
                              2          io_clob clob:= null ;
                              3          buff varchar2(4000);
                              4          amnt  integer;
                              5          i integer:=0;
                              6  begin
                              7  dbms_output.enable(200000000);
                              8       for j in
                              9            (
                             10            select o.rowid from dok_anhang o
                             11  where  dok_id in (2155326)
                             12  )
                             13            loop
                             14                   dbms_lob.CreateTemporary
                             15                 (io_clob, true, dbms_lob.session);
                             16                   test_index_dok_anhang(j.rowid, io_clob);
                             17                   insert into output values(io_clob);
                             18             commit;
                             19       end loop;
                             20  null;
                             21  end;
                             22  /
                            declare
                            *
                            FEHLER in Zeile 1:
                            ORA-06510: PL/SQL: unhandled user-defined exception 
                            ORA-06512: at "CTXSYS.CTX_DOC", line 1478 
                            ORA-06510: PL/SQL: unhandled user-defined exception 
                            ORA-06512: at "CTXSYS.DRVDISP", line 306 
                            ORA-06510: PL/SQL: unhandled user-defined exception 
                            ORA-06512: at "TEST_INDEX_DOK_ANHANG", line 20 
                            ORA-06512: at line 16 
                            
                            
                            SQL> spool off
                            Edited by: Anne-Marie Rosa on Jun 25, 2012 8:28 AM

                            Edited by: Anne-Marie Rosa on Jun 25, 2012 8:29 AM
                            • 11. Re: Problem with ctx_doc.ifilter: numeric or value error
                              Herald ten Dam
                              Hi,

                              the new procedure never returns io_clob back, it is not returned. Temp_clob should be used to fill io_loc.

                              Herald ten Dam
                              http://htendam.wordpress.com
                              • 12. Re: Problem with ctx_doc.ifilter: numeric or value error
                                Frieda
                                Sorry about the confusion, but I just wanted to prove that calling ctx_doc.ifilter without using any writeappend or concatenation is throwing the error message. I know that it never will return the filtered clob.
                                • 13. Re: Problem with ctx_doc.ifilter: numeric or value error
                                  Roger Ford-Oracle
                                  It's not the same error, though.

                                  Please post your code inside { code } tags - without the spaces, same before and after the code in order to maintain formatting.
                                  • 14. Re: Problem with ctx_doc.ifilter: numeric or value error
                                    Frieda
                                    Thanks for the hint, Roger. I have to admit that the errror message is different, but ifilter is throwing an error.

                                    Today I have run the script again on our new development database (11.2.0.3) and there is no error at all and I think it is the same filter as in 10.2.0.5. When I add
                                     io_loc := temp_clob;
                                    to the procedure, can see the whole document in the output table.

                                    I don't think that the procedure is the problem, it might be something in the configuration of the extproc listener. Maybe LD_LIBRARY_PATH is not set correctly for the extproc listener. The other datastore text indexes are working ok.
                                    1 2 Previous Next