Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Hard One....Bizarre "No Data Found" Anomaly

Re: Hard One....Bizarre "No Data Found" Anomaly

From: Tom Zamani <tomz_at_redflex.com.au>
Date: 2000/07/13
Message-ID: <8kitac$qbl$1@perki.connect.com.au>#1/1

As Klaus mentioned you need to put error handling in your code. just check to make sure that you function is actually geting all the values may be you are not passing all the values.

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)

tom
Klaus Zeuch <KZeuch_at_nospamhotmail.com> wrote in message news:8kfnpk$me0$10$1_at_news.t-online.com...
> 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
> any row; you should apply exception handling like
>
> -- calculate
> begin
> select....;
> exception
> when no_data_found then
> -- place error handling there
> when too_many_rows then
> -- place error handling there
> end;
> .....
>
> 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.
> >
> > The problem lies at http://www.worldcupfishing.com/wcf/points_proc
 I
> > have a form that is displaying the values of this function. It is
 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 Thu Jul 13 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US