I need your assistance with implementing Minus and exact phrase search in Oracle Text

Question

I'm currently facing an issue with implementing minus (for exclusion) and exact phrase search functionalities in my Oracle APEX application, specifically within an Interactive Report search feature. I've created a PL/SQL package to process user search queries, but I'm encountering difficulties with the minus and exact phrase searches not working as expected. Except those two everything is working as expected.

Background

My package includes a function, convert_text_query, designed to take a user input string, process it, and generate a query string that Oracle Text can use to search within a database table. The goal is to support:

• Minus (-) for excluding terms in the search.

• Exact phrase search by enclosing terms in double quotes (" ").

Issue

While other search functionalities work as intended, the minus and exact phrase searches do not return the expected results. For example, given two records in the database:

1. kommaddi tharun

2. kommaddi is a software developer

A search for kommaddi -tharun should exclude the first record and return only the second. Similarly, a search for "kommaddi tharun" should return only the first record if searching for that exact phrase.

However, I get an error

ORA-29902: error in executing ODCIIndexStart() routine

indicating an issue with the execution of the search operation.

Request

Could someone please guide me on how to correctly implement these functionalities within my package? I suspect the issue may be related to the syntax used in constructing the search string for Oracle Text or perhaps a configuration issue with Oracle Text itself.

Here is the core part of my package for your reference:

create or replace package body eba_demo_ig_text_pkg is 

    procedure execute_sql( 

        p_sql         in varchar2,  

        p_throw_error in boolean default true 

    ) is 

    begin 

        execute immediate p_sql; 

    exception 

        when others then  

            if p_throw_error then raise; end if; 

    end execute_sql; 

    function text_is_available return boolean  

    is 

        l_dummy number; 

    begin 

        select 1 into l_dummy  

          from sys.all_objects 

         where owner       = 'CTXSYS'  

           and object_name = 'CTX_DDL'  

           and rownum      = 1; 

        return true; 

    exception  

        when NO_DATA_FOUND then return false; 

    end text_is_available; 

    procedure init_oracle_text is 

    begin 

        if text_is_available then 

            create_text_preferences; 

            create_text_index; 

        end if; 

    end init_oracle_text; 

    procedure drop_text_index is  

    begin 

        execute_sql( q'#drop index EBA_DEMO_IG_TEXT_FTX force#' ); 

    end drop_text_index; 

    procedure drop_text_preferences is 

    begin 

        execute_sql( q'#begin ctx_ddl.drop_preference( 'EBA_DEMO_IG_LX_PREF'); end;#', false );  

        execute_sql( q'#begin ctx_ddl.drop_preference( 'EBA_DEMO_IG_DS_PREF'); end;#', false );  

        execute_sql( q'#begin ctx_ddl.drop_section_group( 'EBA_DEMO_IG_SG_PREF'); end;#', false );  

    end drop_text_preferences; 

    procedure create_text_preferences is 

    begin 

        -- Datastore Preference: Index the VORSCHRIFT_NUMMER and the VORSCHRIFT_BEZEICHNUNG_DEUTSCH columns 

        execute_sql(q'#  

        begin 

            ctx_ddl.create_preference( 

                preference_name  => 'EBA_DEMO_IG_DS_PREF', 

                object_name      => 'MULTI_COLUMN_DATASTORE' 

            ); 

         

            ctx_ddl.set_attribute( 

                preference_name  => 'EBA_DEMO_IG_DS_PREF', 

                attribute_name   => 'COLUMNS', 

                attribute_value  => 'VORSCHRIFT_NUMMER,VORSCHRIFT_BEZEICHNUNG_DEUTSCH' 

            ); 

         

            ctx_ddl.create_section_group( 

                group_name       => 'EBA_DEMO_IG_SG_PREF', 

                group_type       => 'XML_SECTION_GROUP' 

            ); 

         

            ctx_ddl.add_field_section( 

                group_name       => 'EBA_DEMO_IG_SG_PREF', 

                section_name     => 'VORSCHRIFT_BEZEICHNUNG_DEUTSCH', 

                tag              => 'VORSCHRIFT_BEZEICHNUNG_DEUTSCH', 

                visible          => true 

            ); 

         

            ctx_ddl.add_field_section( 

                group_name       => 'EBA_DEMO_IG_SG_PREF', 

                section_name     => 'VORSCHRIFT_NUMMER', 

                tag              => 'VORSCHRIFT_NUMMER', 

                visible          => true 

            ); 

         

            ctx_ddl.create_preference( 

                preference_name  => 'EBA_DEMO_IG_LX_PREF', 

                object_name      => 'BASIC_LEXER' 

            ); 

         

            ctx_ddl.set_attribute( 

                preference_name  => 'EBA_DEMO_IG_LX_PREF', 

                attribute_name   => 'MIXED_CASE', 

                attribute_value  => 'NO' 

            ); 

         

            ctx_ddl.set_attribute( 

                preference_name  => 'EBA_DEMO_IG_LX_PREF', 

                attribute_name   => 'BASE_LETTER', 

                attribute_value  => 'YES' 

            ); 

            ctx_ddl.set_attribute( 

                preference_name  => 'EBA_DEMO_IG_LX_PREF', 

                attribute_name   => 'BASE_LETTER_TYPE', 

                attribute_value  => 'GENERIC' 

            ); 

        end;#' 

        );  

    end create_text_preferences; 

    procedure create_text_index is 

    begin 

        execute immediate  

q'#create index EBA_DEMO_IG_TEXT_FTX on T_VORSCHRIFT_SYNTHETIC (VORSCHRIFT_NUMMER) 

   indextype is ctxsys.context parameters ( 'section group  EBA_DEMO_IG_SG_PREF 

                                             datastore      EBA_DEMO_IG_DS_PREF 

                                             lexer          EBA_DEMO_IG_LX_PREF 

                                             stoplist       ctxsys.empty_stoplist 

                                             memory         10M 

                                             sync           (on commit)')#'; 

    end create_text_index; 



-- Original version - working   

    function convert_text_query( p_enduser_query in varchar2 ) return varchar2  

    is  

        l_tokens       apex_application_global.vc_arr2; 

        l_set_boolean  boolean := false; 

        c_xml constant varchar2(32767) := '<query><textquery><progression>' || 

                                            '<seq>#NORMAL_AND#</seq>' || 

                                            '<seq>#FUZZY_AND#</seq>' || 

                                            '<seq>#FUZZY_OR#</seq>' || 

                                          '</progression></textquery></query>'; 

        l_textquery    varchar2(32767) := ''; 

  

        function generate_query( p_feature in varchar2, p_combine in varchar2) return varchar2 is 

            l_query        varchar2(32767); 

            l_clean_token  varchar2(100); 

        begin 

            for i in 1..l_tokens.count loop 

                l_clean_token := lower( regexp_replace( l_tokens( i ), '[<>{}/()*%&!$?.:,;\+#]', '' ) ); 

                if ltrim( rtrim( l_clean_token ) ) is not null then 

                    if p_feature = 'FUZZY' then 

                        l_query := l_query || 'FUZZY({' || l_clean_token || '}, 50, 500) '; 

                    elsif p_feature = 'WILDCARD_RIGHT' then  

                        l_query := l_query || l_clean_token || '% '; 

                    else  

                        l_query := l_query || '{' || l_clean_token || '} '; 

                    end if; 

                    if p_combine = 'OR' then  

                        l_query := l_query || ' or '; 

                    else  

                        l_query := l_query || ' and '; 

                    end if; 

                end if; 

            end loop; 

            if p_combine = 'AND' then 

                l_query := substr( l_query, 1, length( l_query ) - 5 ); 

            else 

                l_query := substr( l_query, 1, length( l_query ) - 4 ); 

            end if; 

            return ltrim( rtrim( l_query ));  

        end generate_query; 

    begin 

        if substr( p_enduser_query, 1, 8 ) = 'ORATEXT:' then 

            return substr( p_enduser_query, 9 ); 

        else  

            l_textquery := c_xml; 

            l_tokens := apex_util.string_to_table( p_enduser_query, ' ' ); 

     

            l_textquery := replace( l_textquery, '#NORMAL_AND#', generate_query( 'NORMAL', 'AND' ) ); 

            l_textquery := replace( l_textquery, '#FUZZY_AND#', generate_query( 'FUZZY', 'AND' ) );

            

     

            return l_textquery; 

        end if; 

    end convert_text_query;





end eba_demo_ig_text_pkg

I appreciate any advice or examples on how to handle minus for exclusion and exact phrase searches correctly in this context. Thank you in advance for your assistance!

Issue:

While other search functionalities work as intended, the minus and exact phrase searches do not return the expected results. For example, given two records in the database:

1. kommaddi tharun

2. kommaddi is a software developer

A search for kommaddi -tharun should exclude the first record and return only the second. Similarly, a search for "kommaddi tharun" should return only the first record if searching for that exact phrase.

Answer 1

I can't help from what you've posted so far. It's not a complete testcase, and it relies on a datatype "apex_application_global.vc_arr2" which is not available to me in a standard database connection.

There's a comment before "convert_text_query" which says "-- Original version - working" but presumably this is the function you need help with? I assuming you just feed the output of that function into a CONTAINS operator. It would be more useful just to see the output of that function - then I could tell you whether it's valid, or invalid in some fashion.

One extra point, you list your error as

ORA-29902: error in executing ODCIIndexStart() routine

Whereas this is normally just an introducer - I would expect to see something like:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1

It's possible that Apex is hinding the full message from you. Even more reason to find out exactly what your code is emitting, and trying that manually in a CONTAINS operator.

Answer 2

I haven't had a chance to analyze your code in detail, but you should be aware that I have an extensive query parser available which does exactly this. It's on GitHub here: https://github.com/oracle-samples/oracle-db-examples/tree/main/text/query-parser That's the original Oracle version - as I'm now retired I've forked my own version here, which has some improvements not checked in yet on the original version: https://github.com/RogerFord/oracle-db-examples/tree/main/text/query-parser