Re: Stored Procedure throwing TOO_MANY_ROWS at simultaneous access?

code, gotta have code.

What you describe could not/should not happen.

do you have other select ... into ... statements in the procedure, maybe they are the cause.

do you have a unique index on the key fields you are looking up (to ensure the statement "there are no unique rows" is always true.

recode (temporarily) functions like:

create or replace function part_no( p_part_name in varchar2 ) return number as

        l_part_no number;
begin

	select part_no into l_part_no
          from parts
         where part_name = p_part_name;

	return l_part_no;

To be:

create or replace function part_no( p_part_name in varchar2 ) return number as

        l_part_no number;

	cursor c1( p_part_name in varchar2 ) is 
                     select rownum, rowid, a.* 
                       from parts a 
                      where part_name = p_part_name;
	l_last_rec	c1%rowtype;
begin
	for x in c1(p_part_name)
	loop
		if ( x.rownum > 1 ) then
		  insert into log_table ( part_rowid, part_no, part_name, ... )
		  values ( x.rowid, x.part_no, x.part_name, .... );
		  insert into log_table ( part_rowid, part_no, part_name, ... )
		  values ( l_last_rec.rowid, l_last_rec.part_no, ..... );
		  commit;
	          raise TOO_MANY_ROWS;
		end if;
		l_last_rec := x;
	end loop;
	return l_last_rec.part_no;

That way you can do a post mortem on it the next time it happens.

On Sat, 15 Jun 96 12:27:45 GMT, rharris_at_ties.org (Richard W. Harris) wrote:

>Hi,
>
>I have a very simple stored procedure that is part of an application.
>
>Given a part name, it looks up the part_id and returns it. Very simple
>query, and I've checked the parts table, and the rows are unique. Here's the
>problem:
>
>Once in a blue moon two people call the stored procedure at the exact same
>time looking up the same part number. When that happens, the stored procedure
>throws a TOO_MANY_ROWS exception. What?!
>
>To me, since it is just reading the table, this shouldn't happen. Is this a
>bug? It is some sort of busy/retry problem I can adjust?
>
>Any clues as to what is going on would be greatly appreciated.
>
>Thanks,
>
>--------------------------------
>Richard Harris
>rharris_at_ties.org
>http://www.city-net.com/~rharris

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Jun 15 1996 - 00:00:00 CEST