Re: Hard One....Bizarre "No Data Found" Anomaly
From: Klaus Zeuch <KZeuch_at_nospamhotmail.com>
Date: 2000/07/11
Message-ID: <8kfnpk$me0$10$1_at_news.t-online.com>#1/1
currently
> displaying the old function without problem, but when I try to use the new
> function, it raises an error :
> ORA-01403: no data found
> ORA-06512: at "DOGFISH.POINTS_CALC_FUNC", line 44
> ORA-06512: at "DOGFISH.POINTS_PROC_JOE", line 62
> points_calc_func is the new function that I am using (the code is inserted
> later....) which works fine outside of the calling procedure (i have it in
> other procs that don't display it to the net, and they work fine)
> points_proc_joe is the same as the points_proc that exists already(see url
> above) except that it calls the new function instead of the old one.
>
> Can anyone offer some insight to this issue? I'm stumped. I've been
at
> it for days (I hope it isn't something simple I'm overlooking....)
> Here's the function:
> create or replace function points_calc_func
> ( in_length inches.inch_number%type
> , in_state record_lengths.state_code%type
> , in_specie_id specie_formulas.specie_id%type)
> RETURN NUMBER
> IS
> co_efficient specie_co_efficients.co_efficient%TYPE;
> points_formula specie_formulas.formula%TYPE;
> var VARCHAR2(100);
> val VARCHAR2(100);
> num NUMBER;
> current_year number := 2000;
> <<snip....cursors for local variables....>>
> BEGIN
> --determine co_efficients based on state
> open c_co_efficient;
> fetch c_co_efficient into co_efficient;
> close c_co_efficient;
> --determine formula
> open c_formula;
> fetch c_formula into points_formula;
> close c_formula;
> --CALCULATE!!!
> SELECT replace(points_formula,'in_length',in_length)
> INTO var
> FROM specie_formulas
> WHERE year LIKE current_year
> AND specie_id LIKE in_specie_id
> ;
> num := round((co_efficient * eval.do_it(points_formula, in_length)),8);
> return num;
> END points_calc_func;
> /
> And the call to the function:
> l_points := Points_calc_Func(p_inch_number,p_specie_id,p_state_code);
>
> Thank you for your help.....all input is appreciated!! If you need
anymore
> info....just drop me a line.
> --
> Joseph Ranseth - Webmaster
> World Cup Fishing
> Phone:(204) 944-8945 Fax: (204) 943-0954
> http://www.worldcupfishing.com
>
> Received on Tue Jul 11 2000 - 00:00:00 CEST
Date: 2000/07/11
Message-ID: <8kfnpk$me0$10$1_at_news.t-online.com>#1/1
[Quoted] [Quoted] As I don't know what's in lines 44/ 62 of your function
[SELECT line, text FROM user_source WHERE name like UPPER ('points_proc') and line in (44,62);]
I can only guess that your query after comment CALCULATE!!! doesn't return [Quoted] any row; you should apply exception handling like
- calculate
[Quoted] begin
select....;
exception
when no_data_found then
- place error handling there
when too_many_rows then
- place error handling there end; .....
- place error handling there
when too_many_rows then
Regards
Klaus
"Joseph Ranseth" <jransethNO_SPAM_at_worldcupfishing.com> schrieb im
Newsbeitrag news:_IIa5.3261$Rz1.18470_at_news1.mts.net...
> Hello gurus,
> I have a function that I've written to calculate a value based on 3
> incoming parameters, and it works fine. : )
> I also have an old function that works fine, but returns the wrong
> values. I don't need to use this one.
>> have a form that is displaying the values of this function. It is
> The problem lies at http://www.worldcupfishing.com/wcf/points_proc I
currently
> displaying the old function without problem, but when I try to use the new
> function, it raises an error :
> ORA-01403: no data found
> ORA-06512: at "DOGFISH.POINTS_CALC_FUNC", line 44
> ORA-06512: at "DOGFISH.POINTS_PROC_JOE", line 62
>
> points_calc_func is the new function that I am using (the code is inserted
> later....) which works fine outside of the calling procedure (i have it in
> other procs that don't display it to the net, and they work fine)
> points_proc_joe is the same as the points_proc that exists already(see url
> above) except that it calls the new function instead of the old one.
>
> Can anyone offer some insight to this issue? I'm stumped. I've been
at
> it for days (I hope it isn't something simple I'm overlooking....)
>
> Here's the function:
>
> create or replace function points_calc_func
> ( in_length inches.inch_number%type
> , in_state record_lengths.state_code%type
> , in_specie_id specie_formulas.specie_id%type)
> RETURN NUMBER
> IS
> co_efficient specie_co_efficients.co_efficient%TYPE;
> points_formula specie_formulas.formula%TYPE;
> var VARCHAR2(100);
> val VARCHAR2(100);
> num NUMBER;
> current_year number := 2000;
>
> <<snip....cursors for local variables....>>
>
> BEGIN
> --determine co_efficients based on state
> open c_co_efficient;
> fetch c_co_efficient into co_efficient;
> close c_co_efficient;
>
> --determine formula
> open c_formula;
> fetch c_formula into points_formula;
> close c_formula;
>
> --CALCULATE!!!
> SELECT replace(points_formula,'in_length',in_length)
> INTO var
> FROM specie_formulas
> WHERE year LIKE current_year
> AND specie_id LIKE in_specie_id
> ;
>
> num := round((co_efficient * eval.do_it(points_formula, in_length)),8);
>
> return num;
> END points_calc_func;
> /
>
> And the call to the function:
>
> l_points := Points_calc_Func(p_inch_number,p_specie_id,p_state_code);
>
> Thank you for your help.....all input is appreciated!! If you need
anymore
> info....just drop me a line.
>
> --
> Joseph Ranseth - Webmaster
> World Cup Fishing
> Phone:(204) 944-8945 Fax: (204) 943-0954
> http://www.worldcupfishing.com
>
> Received on Tue Jul 11 2000 - 00:00:00 CEST