7 Replies Latest reply: Jan 21, 2013 3:33 PM by ArunkumarGunasekaran RSS

    writing a search function

    ArunkumarGunasekaran
      Hello Folks,

      I have written the below mentioned search() function which takes a 'one word' as an input parameter and searches a couple of CLOB datatype columns in my product table and returns the name and id of the corresponding product.
      ---  SEARCH FUNCTION ---- 
      ---  THE USER ENTERED KEYWORD IS SEARCHED IN THE PRODUCT DESCRIPTION COLUMN AND PRODUCT TAGS COLUMN
      function search (search_string varchar) return datacursor as dataset datacursor;
      begin
                open dataset for 
                select prod_key,prod_name from dim_product
                where contains (prod_keyword, concat(concat('%',search_string),'%')) >0 or contains (prod_desc, concat(concat('%',search_string),'%')) >0;     
      return dataset;
      end search;
      note :
      type datacursor is ref cursor;
      The code is working fine. Now I want to expand this function from one word search to many words that the user passes into the function to search the table. How can I achieve that?

      Thanks!
      Arun
        • 1. Re: writing a search function
          Dizwell
          You will allow your users to pass a long string of words.
          You will have written some code which parses that string and separates it into individual search words. This code will probably strip out any words supplied which Oracle Text considers to be stop words. You might also want to strip out things like 'AND' and 'OR', 'NOT' and "BT' (and anything else which can screw up Oracle Text syntactically, like brackets and other punctuation that has specific meaning to Oracle Text).
          You will then write code to construct a meaningful search term, by re-concatenating the separate search words into a single search string that contains syntactically-correct AND or OR or NOT joins.
          You then modify your existing code to use this new search string

          If, for example, I type "Beans, Pie and Chips", your code would turn this into "Beans", "Pie", "Chips". Then you'd concatenate that to be "Beans|Pie|Chips" (or maybe 'beans and pie and chips'). Then you'd stick that into your existing contains syntax.

          You will have to make up business rules for how users are allowed to type stuff in. Are they allowed to type in things like "Beans and (Pie or Chips)", for example? Do you simply dictate that 'all search terms will be combined' (and thus concatenate them with forced ANDs), or do you allow for ORs, ANDs and a mixture of the two? Do you allow NOTs? (One time I did this, I had to write 'parsing code' which, if it saw a minus in front of a word, knew to add that back into the final string with NOT in front of it, for example. So we allowed users to type things like "beans pie -chips" and that would become "(beans and pie) not chips".

          You will also have to make up rules about string delimiters. Do you allow users to comma-delimit their search terms, space-delimit them or a combination? If you are doing space delimiters, you'll have to loop round the input string until all multiple spaces are reduced to single spaces. Then you can break the string into its separate words before re-concatenating them into a syntactically-correct contains clause.

          Hope that helps a bit,
          Regards
          HJR
          • 2. Re: writing a search function
            Karthick_Arp
            You could create a Table type like this
            create or replace type search_string_table as table of varchar2(4000)
            /
            And modify your function like this
            function search (search_string_list search_string_table) return datacursor
            as
              dataset datacursor;
            begin
               open dataset for 
               select distinct prod_key
                    , prod_name 
                 from dim_product
                 join (
                         select column_value
                           from (table(search_string_list))
                      )
                   on contains (prod_keyword, concat(concat('%',column_value),'%')) > 0 
                   or contains (prod_desc   , concat(concat('%',column_value),'%')) > 0; 
               
               return dataset;
            end search;
            Now you call the function as
            ref_cursor := search(search_string_table('string1', 'string2', 'string3'));
            Note: Code is not tested
            • 3. Re: writing a search function
              chris227
              Hi,

              If i remember correctly contains can take servel search words connected with operators like AND and OR, e.g. something lilke (without rereading the docs)
              contains (prod_keyword, concat(concat('%',search_string1),'%') || 'OR' || concat(concat('%',search_string2),'%') ) >0
              • 4. Re: writing a search function
                SShubhangi
                See whether below code is helpful to you...

                create or replace procedure find_string( p_str in varchar2 )
                authid current_user
                as
                l_query long;
                l_case long;
                l_runquery boolean;
                l_tname varchar2(300);
                l_cname varchar2(300);
                type rc is ref cursor;
                l_cursor rc;
                begin
                dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );

                for x in (select * from user_tables )
                loop
                l_query := 'select distinct ''' || x.table_name || ''', $$
                from ' || x.table_name || '
                where ( 1=0 ';
                l_runquery := FALSE;
                l_case := NULL;
                for y in ( select *
                from user_tab_columns
                where table_name = x.table_name
                and data_type in ( 'VARCHAR2', 'CHAR' )
                )
                loop
                l_runquery := TRUE;
                l_query := l_query || ' or upper(' || y.column_name ||
                ') like userenv(''client_info'') ';
                l_case := l_case || '||'' ''|| case when upper(' || y.column_name ||
                ') like userenv(''client_info'') then ''' ||
                y.column_name || ''' else NULL end';
                end loop;
                if ( l_runquery )
                then
                l_query := replace( l_query, '$$', substr(l_case,8) ) || ')';
                begin
                open l_cursor for l_query;
                loop
                fetch l_cursor into l_tname, l_cname;
                exit when l_cursor%notfound;
                dbms_output.put_line
                ( 'Found in ' || l_tname || '.' || l_cname );
                end loop;
                close l_cursor;
                end;
                end if;

                end loop;
                end;
                • 5. Re: writing a search function
                  SShubhangi
                  see might be useful for you...

                  create or replace procedure find_string( p_str in varchar2 )
                  authid current_user
                  as
                  l_query long;
                  l_case long;
                  l_runquery boolean;
                  l_tname varchar2(300);
                  l_cname varchar2(300);
                  type rc is ref cursor;
                  l_cursor rc;
                  begin
                  dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );

                  for x in (select * from user_tables )
                  loop
                  l_query := 'select distinct ''' || x.table_name || ''', $$
                  from ' || x.table_name || '
                  where ( 1=0 ';
                  l_runquery := FALSE;
                  l_case := NULL;
                  for y in ( select *
                  from user_tab_columns
                  where table_name = x.table_name
                  and data_type in ( 'VARCHAR2', 'CHAR' )
                  )
                  loop
                  l_runquery := TRUE;
                  l_query := l_query || ' or upper(' || y.column_name ||
                  ') like userenv(''client_info'') ';
                  l_case := l_case || '||'' ''|| case when upper(' || y.column_name ||
                  ') like userenv(''client_info'') then ''' ||
                  y.column_name || ''' else NULL end';
                  end loop;
                  if ( l_runquery )
                  then
                  l_query := replace( l_query, '$$', substr(l_case,8) ) || ')';
                  begin
                  open l_cursor for l_query;
                  loop
                  fetch l_cursor into l_tname, l_cname;
                  exit when l_cursor%notfound;
                  dbms_output.put_line
                  ( 'Found in ' || l_tname || '.' || l_cname );
                  end loop;
                  close l_cursor;
                  end;
                  end if;

                  end loop;
                  end;

                  Edited by: SShubhangi on Jan 18, 2013 1:49 PM
                  • 6. Re: writing a search function
                    Paul  Horth
                    Pretty much a direct copy of

                    http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5203164092530

                    It is quite OK to use other people's code in your reply but you should say where it comes from and not try to pass it off as your own code.
                    • 7. Re: writing a search function
                      ArunkumarGunasekaran
                      Hello Karthick,

                      This code was very helpful.
                      function search (search_string_list search_string_table) return datacursor
                      as
                        dataset datacursor;
                      begin
                         open dataset for 
                         select distinct prod_key
                              , prod_name 
                           from dim_product
                           join (
                                   select column_value
                                     from (table(search_string_list))
                                )
                             on contains (prod_keyword, concat(concat('%',column_value),'%')) > 0 
                             or contains (prod_desc   , concat(concat('%',column_value),'%')) > 0; 
                         
                         return dataset;
                      end search;
                      It works perfect in my package. Except that when I use the hyphen symbol '-' , the search result is messed up. I am getting a list of products as search result which seems to have no relation to the search string(s) I entered. I thought the hyphen '-' is like a wild card first, but the results are not in line with that assumption either. Any thoughts on that?

                      Just for clarity : The search string that I entered was 're-route' which has a hyphen in it. When I do the search in word or excel I am having no problem.

                      Thanks!
                      Arun

                      Edited by: Arunkumar Gunasekaran on Jan 21, 2013 1:30 PM