Re: Problem with TOO_MANY

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 03 Jan 2000 16:23:56 -0500
Message-ID: <dk427s8g0avftornjhs747cq6cvne41if4@4ax.com>

A copy of this was sent to "Janet Wroby" <jwroby_at_morpace.com> (if that email address didn't require changing) On Mon, 3 Jan 2000 14:24:35 -0500, you wrote:

>hello-
>
>I am new to working with Oracle and do not understand why I am getting the
>TOO_MANY_ROWS error in a Function I've created.
>
>The table time_daily has the 2 fields: time_daily.TSLine_id and
>time_daily.day_nr as a composite primary key - there is only at most one row
>that should match a set of values passed in.
>
>My function is:
> CREATE or REPLACE FUNCTION get_timesheet_comment
> (line_id IN NUMBER,
> day_nr IN NUMBER) RETURN VARCHAR2 IS
> ts_comment VARCHAR2(200);
> BEGIN
> SELECT desc_tx INTO ts_comment
> FROM time_daily
> WHERE ((time_daily.TSLine_id = line_id)
> AND (time_daily.day_nr = day_nr));
> RETURN ts_comment;

that query is the same as:

select desc_tx into ts_comment
from time_daily
where ((time_daily.tsline_id = line_id) and (time_daily.day_nr IS NOT NULL));

the predicate on day_nr is not really being used (except to check for non-null day_nr fields)... The scoping rules for SQL in PLSQL is that SQL 'wins' -- since there is a column day_nr that is in scope in the query -- plsql does not see that as a bind variable -- its the database column.

I like to prefix all plsql variables with one of:

p_  for parameters...
l_  for local variables....
g_  for global package variables....

that way, I can avoid confusion with table columns and local variables. A quick solution for you might be:

> SELECT desc_tx INTO ts_comment
> FROM time_daily
> WHERE ((time_daily.TSLine_id = line_id)
> AND (time_daily.day_nr = get_timesheet_comment.day_nr));

                                   ^^^^^^^^^^^^^^^^^^^^^

qualify the plsql variable....

[snip]

>
>But when I try to call the function - I get the message "too many rows"
>
>ex:
> SELECT get_timesheet_comment(847802, 3) FROM dual;
>
>Any help would be much appreciated.
>Thanks,
>Janet
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 03 2000 - 15:23:56 CST

Re: Problem with TOO_MANY_ROWS