Re: problem with user defined functions with select into statements
Date: 1996/05/03
Message-ID: <4mbkli$a3g_at_inet-nntp-gw-1.us.oracle.com>#1/1
Are you having an unhandled NO_DATA_FOUND exception in your procedure???
try:
create function tablelookup(var1 char ,var2 char)
return char is
var3 char;
begin
select field3 into var3 from atable where field1 = var1 and
field2 = var2;
return (var3);
exception
when no_data_found then return NULL;
end;
An unhandled exeception in the function will terminate the query.
c.keay_at_cranfield.ac.uk (Caroline Keay) wrote:
>Before I spend yet another day trying to suss out what is going wrong
>maybe one of you can help me.
>
>The problem is:
>I have created a function such as: (this is just an example of the
>sort of function don't parse it or take it literary)
>create function tablelookup(var1 char ,var2 char)
>return char is
> var3 char,
>begin
> select field3 into var3 from atable where field1 = var1 and
> field2 = var2;
> return (var3);
>end;
>I then use this function in an SQL select statement (or a browser
>define column)
>select field1, field2, tablelookup(field1,field2)
>from anothertable;
>The function works and returns the right values but the select
>statement runs out of steam after only a few (< 100) records, there is
>no error message though the last few records returned have no values
>in them and seem to be hinting at some sort of out of bounds error.
>I haven't yet totally nailed down this affect yet, some times is even
>seems to work properly. Hope someone out there can shed light on it.
>Caroline
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Fri May 03 1996 - 00:00:00 CEST