Re: Select doesn't work correctly with user-defined function in select list

In article <41a92g$sjv_at_dartvax.dartmouth.edu>, Marty Himmelstein <marty.himmelstein_at_valley.net> writes:
>> I have a user-defined function that I call from a select list. The
>> user-defined function does a singleton select and returns the value to
>> the calling point. The problem is, if no data is found by the function,
>> the calling select query doesn't work. It returns one row, instead of
>> 22, or whatever. This is the case even when I write an exception handler
>> to catch the no_data_found.
>>
>> SELCT FRAGMENT:
>>
>> select
>> tab.key,
>> GetVectorItem( tab.key, 1) colAlias -- first of a repeating group
>> from
>> tab tab
>> where
>> tab.first_dt between (sysdate-5000) and (sysdate-100)
>>
>>
>> FUNCTION:
>>
>> create or replace FUNCTION GetVectorItem
>> (
>> inKey in number,
>> inSubscript in number
>> ) RETURN char AS
>>
>> result char(5) := 'INITX';
>>
>> BEGIN
>>
>> select
>> vct.item into result
>> from
>> vector_tab vct
>> where
>> vct.key = inKey
>> and vct.subscript = inSubscript;
>>
>> EXCEPTION
>> when NO_DATA_FOUND THEN return 'NONEX'; --for debugging
>> when OTHERS THEN return 'EXCPT'; --for debugging
>>
>> END GetVectorItem;
>>
>>
>> Any help, experiences, or hints are appreciated. Also a warning that a
>> tempting feature seems to have gotchas.
>>
>> Thanks,
>> Marty Himmelstein
>> marty.himmelstein_at_valley.net
>>

I have been successfull with user defined functions on a select statement. You could change your routine to do the actual open,fetch,close instead of select ... into .

This way you completely avoid the exception .

-- 
*****************************************************
* Burt Peltier Shell Offshore    ep94bvps_at_shell.com *
* (504) 588-0179 701 Poydras #1660 New Orleans 70139*
*****************************************************