I have a query that works when I have fixed values. ie:
select
count(*)
from
address a
where
a.primary_name like upper('cambourne court') and
a.secondary_name like upper('flat 9');
However replace the upper('flat 9')
with a variable which is second_name:=upper('flat 9')
and the search now returns all 111 addresses in 'cambourne court'.
Why would this be?
EDIT: This is the complete address.sql file (with comments removed)
declare
address_details address%rowtype;
current_loc varchar2(32);
prime_name varchar2(255);
prime_number varchar2(255);
second_name varchar2(255);
street_name varchar2(255);
town_name varchar2(255);
success boolean;
the_count number;
begin
prime_name:=upper('&&primary_name');
prime_number:=upper('&&primary_number');
second_name:=upper('&&secondary_name');
street_name:=upper('&&street_name');
town_name:=upper('&&town_name');
success:=true;
-- error checking here (removed for brevity)
if success then
current_loc := 'finding address';
select
count(*)
into
the_count
from
dependency d,
address a,
street s
where
d.dep_obj_id1 = 2 and
d.dep_obj_id2 = 1 and
a.loc_id = d.dep_id1 and
s.loc_id = d.dep_id2 and
a.primary_name like prime_name and
a.secondary_name like second_name and
s.name like street_name and
s.town like town_name;
end if;
dbms_output.put_line('success: address found '||the_count);
exception
when too_many_rows then
dbms_output.put_line('failure: too many rows while '||current_loc);
when no_data_found then
dbms_output.put_line('failure: no rows found while '||current_loc);
when others then
dbms_output.put_line('failure: general error while '||current_loc);
end;
/
Update: I restarted SQL*Plus which seemed to have fixed the break.
Replacing prime_name and second_name with the actual strings means the code runs in less than a second. With variables means it takes more than 2 minutes.