CODEWITHSUNDEEP
sqloracle11g
name_masked
name_masked

Reputation: 9794

Search string using Function - Oracle

I am trying to search a string using the input values passed to my Oracle function. Following is the code[modified since I cannot paste the original code]:

CREATE OR REPLACE FUNCTION uf_search_address
(
   street  IN VARCHAR2,
   city    IN VARCHAR2
)
RETURN NUMBER
IS RCVD_PERSON_ID NUMBER;
BEGIN
   SELECT PERSON_ID INTO RCVD_PERSON_ID 
   FROM T_PARTICIPANTS
   WHERE COMPLETE_ADDRESS LIKE street[?] OR COMPLETE_ADDRESS LIKE city[?]
   RETURN (RCVD_PERSON_ID);
END;

But the query fails to return anything(it compiles though).

How can I use the LIKE functionality with the parameters passed?

Upvotes: 0

Views: 101

Answers (1)

Ben
Ben

Reputation: 52863

The way to use like with parameters is to use the concatenation operator || so your function would be like the following, which would return anything that begins with street or city:

create or replace function uf_search_address ( P_street IN varchar2
                                             , P_city IN varchar2
                                               ) return number is

   rcvd_person_id number;

begin

   select person_id
     into rcvd_person_id
     from t_participants
    where complete_address like P_street || '%'
       or complete_address like P_city || '%'
          ;

   return rcvd_person_id;

exception when no_data_found then
   return null;

end;
/
show error

However If there's more than one value that matches this query it will fail. As the chances are you have more than one person per city it makes it fairly likely that what you're trying to do will never work. Note also the exception handler as if there's no matches for your query Oracle will throw and error as well.

You will need to make some sort of decision in the code:

This can either be done in SQL, for instance by using rank. In the example below timestamp would be the date of entry into the table for a very simplistic ranking system:

select person_id
  into rcvd_person_id
  from ( select person_id
           from ( select person_id
                       , rank() over ( order by timestamp desc ) as rnk
                    from t_participants
                   where complete_address like P_street || '%'
                      or complete_address like P_city || '%'
                         )
          where rnk = 1
                )

Alternatively you could make your decision in PL\SQL by bulk collecting everything into a type and doing some looping.

Upvotes: 1

Related Questions