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

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 Received on Mon Aug 21 1995 - 00:00:00 CEST