This discussion is archived
7 Replies Latest reply: Jan 21, 2013 1:33 PM by ArunkumarGunasekaran RSS

writing a search function

ArunkumarGunasekaran Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points