Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetch returns more rows

700833Nov 24 2009 — edited Nov 24 2009
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.
This post has been answered by Frank Kulash on Nov 24 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 22 2009
Added on Nov 24 2009
2 comments
283 views