Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: NO DATA FOUND ERROR IN FUNCTION USING INTO? ORA-01403
jobs (jobs_at_webdos.com) wrote:
: I've got this simple select in my function
: select m.mcman_cdcli, into clientid
: from mcman m
: where m.mcman_cdani=v_ani;
: when no rows are found, I get ORA-01403 No Data found.
: Do I have to trap this? any way to just set to a default with NVL or
: something, Iv'e tried that and it did not work.
Trapping it might be best because the meaning is clear. Wrap it with its own begin/exception/end so as not to clutter up the rest of the procedure
procedure exampl begin blah blah BEGIN SELECT x INTO Y etc EXCEPTION WHEN NO_DATA_FOUND THEN Y:=default_value; END; blah blah exception when real_errors_only end;
Or change the query to something like
select NVL( MAX( m.mcman_cdcli ) , default_id ) into clientid -- ^^^ ^^^ from mcman m where m.mcman_cdani=v_ani;
but make sure you comment it to make the meaning clear, and notice that this also prevents trapping TOO_MANY_ROWS, which is probably an unexpected error you probably shouldn't be preventing here. Received on Fri Jul 13 2007 - 14:19:04 CDT
![]() |
![]() |