Hi,
I have the following table:
ID number, not null, unique
fname varchar2(50),not null
lname varchar2(50), not null
email varchar2(200)not null, unique
and the following procedure that queries the table:
declare
name varchar2(100);
email varchar2(500);
stmt varchar2(4000);
nbr number;
begin
name:=substr('sam wilkins:samw@yahoo.com',1,(instr('sam wilkins:sw@yahoo.com',':')-1));
email:=substr('sam wilkins:samw@yahoo.com',(instr('sam wilkins:sw@yahoo.com',':')+1));
stmt:='select id from tbl where '
|| 'fname ||'' ''||'
|| 'lname='|| ''''||lower(name)||''''||' and email='||''''||lower(email)||''''||'';
execute immediate stmt into nbr;
select id into nbr from tbl where fname||' '||lname = name and email = email;
dbms_output.put_line(stmt);
dbms_output.put_line(nbr);
end;
When I run the procedure with just the execute immediate, the query returns just one id, but when I use the select into, I get the 'Fetch returns more rows' error. Why is this?
Thanks.