7 Replies Latest reply: Feb 4, 2013 6:08 AM by 984246 RSS

    Example usage of "ctx_doc.markup"?

    984246
      I'm new to Oracle and I'm unsure how to use the "ctx_doc.markup" function.

      I've tried the example seen here: highlighting terms in document using ctx_doc.highlight procedure but I get the following error:
      Error starting at line 6 in command:
      declare markedup clob default empty_clob();
        begin  
        ctx_doc.markup(index_name => 'text_index', textkey => '11885', text_query => '{FUZZY({hello},,,W),FUZZY({john},,,W)}', restab => markedup, tagset => 'HTML_DEFAULT');
        end;
      Error report:
      ORA-20000: Oracle Text error:
      DRG-50857: oracle error in drvdoc.reslob_chk
      ORA-22275: invalid LOB locator specified
      ORA-06512: at "CTXSYS.DRUE", line 160
      ORA-06512: at "CTXSYS.CTX_DOC", line 2198
      ORA-06512: at line 3
      20000. 00000 -  "%s"
      *Cause:    The stored procedure 'raise_application_error'
                 was called which causes this error to be generated.
      *Action:   Correct the problem as described in the error message or contact
                 the application administrator or DBA for more information.
      Note that I've executed this in Oracle SQL Developer's Worksheet

      Edit: Also, should I use in-memory or table markup-ing? In-memory seems more performant, though I don't know really know the difference.

      Edited by: 981243 on Feb 1, 2013 12:12 AM
        • 1. Re: Example usage of "ctx_doc.markup"?
          Roger Ford-Oracle
          Try calling:
             dbms_lob.createtemporary( markedup, true );
          before the call to ctx_doc.markup. I'm not sure whether empty_clob is supposed to do the same thing, though. If that doesn't work, let us know and we'll figure out a different solution.

          I would always use the in-memory version unless there's a specific need to have the results in a table.
          • 2. Re: Example usage of "ctx_doc.markup"?
            984246
            Error starting at line 8 in command:
              begin  
              dbms_lob.createtemporary( markedup, true );
              ctx_doc.markup(index_name => 'text_index', textkey => '11885', text_query => '{FUZZY({hello},,,W),FUZZY({john},,,W)}', restab => markedup, tagset => 'HTML_DEFAULT');
              end;
            Error report:
            ORA-06550: line 2, column 29:
            PLS-00201: identifier 'MARKEDUPCLOB' must be declared
            ORA-06550: line 2, column 3:
            PL/SQL: Statement ignored
            ORA-06550: line 3, column 137:
            PLS-00201: identifier 'MARKEDUPCLOB' must be declared
            ORA-06550: line 3, column 3:
            PL/SQL: Statement ignored
            06550. 00000 -  "line %s, column %s:\n%s"
            *Cause:    Usually a PL/SQL compilation error.
            *Action:
            • 3. Re: Example usage of "ctx_doc.markup"?
              Roger Ford-Oracle
              Please post the full code. There's no declaration of your variable "markedup" here. I suspect you're using a variable "markupclob" somewhere else.
              • 4. Re: Example usage of "ctx_doc.markup"?
                Ebalthes-Oracle
                Here is a working markup example
                SQL> create table test(id number primary key, text varchar2(46));
                
                Table created.
                
                SQL> insert into test values(11885,'hello John');
                
                1 row created.
                
                SQL> create index text_index on test(text) indextype is ctxsys.context;
                
                Index created.
                
                set serveroutput on size 1000000
                
                declare
                markedup clob;
                amt number := 200;
                line varchar2(200);
                 
                begin
                
                 ctx_doc.markup(index_name => 'text_index', textkey => '11885', text_query => 'FUZZY({hello},,,W),FUZZY({john},,,W)', restab => markedup, tagset => 'HTML_DEFAULT');
                
                 -- markedup is NULL when passed-in, so ctx_doc.markup will
                 -- allocate a temporary CLOB for us and place the results there.
                 dbms_lob.read(markedup, amt, 1, line);
                 dbms_output.put_line('FIRST 200 CHARS ARE:'||line);
                 -- have to de-allocate the temp lob
                 dbms_lob.freetemporary(markedup);
                end;
                /
                
                FIRST 200 CHARS ARE:<B>hello</B> <B>John</B>
                
                PL/SQL procedure successfully completed.
                • 5. Re: Example usage of "ctx_doc.markup"?
                  984246
                  I've copy pasted your example, and it worked perfectly.
                  However, when I modified it to fit the names of my tables and MULTI_COLUMN_DATASTORE indidex, it fails with:
                  Error starting at line 1 in command:
                  declare
                  markedup clob;
                  amt number := 200;
                  line varchar2(200);
                  begin
                  
                  ctx_doc.markup(index_name => 'text_index', textkey => '11885', text_query => 'FUZZY({hello},,,W),FUZZY({john},,,W)', restab => markedup, tagset => 'HTML_DEFAULT');
                  
                  -- markedup is NULL when passed-in, so ctx_doc.markup will
                  -- allocate a temporary CLOB for us and place the results there.
                  dbms_lob.read(markedup, amt, 1, line);
                  dbms_output.put_line('FIRST 200 CHARS ARE:'||line);
                  -- have to de-allocate the temp lob
                  dbms_lob.freetemporary(markedup);
                  end;
                  Error report:
                  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                  ORA-06512: at "SYS.DBMS_LOB", line 1064
                  ORA-06512: at line 11
                  06502. 00000 -  "PL/SQL: numeric or value error%s"
                  *Cause:    
                  *Action:
                  I have tried putting
                  set serveroutput on size 1000000
                  before declare, but I don't know/think it gets executed. I also tried increasing from 200 to 4000 and more. I should note that I will put this into a sproc, which will return the temp. CLOB to a ASP.NET application.

                  My text index definition:
                  CREATE INDEX text_index ON "test"("dummyfield")
                      INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
                       ('DATASTORE prefs LEXER myworldlexer STOPLIST mystoplist FILTER CTXSYS.AUTO_FILTER SYNC (ON COMMIT)');
                  And my table definition:
                    ID NUMBER(10, 0) NOT NULL 
                  , "dummyfield" CLOB 
                  , "field2" VARCHAR2(510 BYTE) DEFAULT NULL NOT NULL 
                  , "field3" VARCHAR2(20 BYTE) DEFAULT NULL NOT NULL 
                  , "field4" BLOB NOT NULL 
                  , "field5" CLOB 
                    ENABLE 
                  Edited by: 981243 on Feb 4, 2013 2:28 AM

                  Edited by: 981243 on Feb 4, 2013 2:29 AM
                  • 6. Re: Example usage of "ctx_doc.markup"?
                    Roger Ford-Oracle
                    I suspect you have multi-byte characters, and this is a problem with
                    amt number := 200;
                    line varchar2(200);
                    varchar2(200) is measured in bytes, by default, but 200 characters may require more than 200 bytes to store them.

                    If you change it to:
                    amt number := 200;
                    line varchar2(400);
                    Does it work OK then?
                    • 7. Re: Example usage of "ctx_doc.markup"?
                      984246
                      Indeed, I've used UTF8 encoded text which contained various characters from a few different languages.
                      Thanks for your help!

                      Edited by: 981243 on Feb 4, 2013 4:08 AM