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