01403. 00000 'No data found'

Question

When I want to update or insert a row in which its zip is not in the zipcodes table, I got the 'no data found' error. I am confused because I already assigned a default value 67226 to validzip variable, how can they find no data? Thanks.

thats good, accept and upvote my answer if you found it useful — Moudiz, Feb 06 '16 at 16:46
Just as a side observation to your immediate issue, I can't know for sure what datatype is used by employee.zip, but you are assigning it a number. In reality zip codes are not numbers, They are character strings that, by convention (in the US, anyway) only use numeric characters. If you start dealing with zip codes of other countries (like Canada, Ireland, Britain) you will discover the difference. Storing them as numbers will also present problems when you have to deal with the codes that have leading zeros. — EdStevens, Feb 06 '16 at 17:24

score 2 · Answer 1 · answered Feb 06 '16 at 16:43

2

If a SELECT ... INTO ... statement returns no rows, it raises the NO_DATA_FOUND exception. Whether the target of the INTO has been previously initialized or not is irrelevant.

You need to add an exception handler to catch the NO_DATA_FOUND exception. Or, an alternative would be to change the query so it will always return a row; for example you could SELECT COUNT(*) which would return either 0 or 1.

answered Feb 06 '16 at 16:43

Dave Costa

47,262
8
56
72

Hi, I agree to use the exception handler, but since validzip is a variable rather than table, I think it might be able to use the select count(*) here. I tried use count(*) before but failed. – smart_cookie Feb 06 '16 at 16:51

score 0 · Answer 2 · answered Feb 06 '16 at 16:37

I guess your problem is here :new.zip when you insert or update data , is the column zip have value 67226 ?

 select zipcodes.zip 
      into validzip 
  from zipcodes 
  where (:new.zip=zipcodes.zip);

and what do you mean by this :new.zip := validzip; ?

I guess the below might work( I am not sure because you have to specify you condition, its better to assign a default value unless you specify a condition )

update zipcodes set  :new.zip=67226 where zip is null

score 0 · Answer 3 · edited May 23 '17 at 11:52

Your trigger needs to catch the NO_DATA_FOUND exception and substitute the default value in for the value in :NEW.ZIP:

create or replace trigger employees_bef_ins_upd_row
before insert or update of zip on employees
for each row
declare
  validzip employees.zip%type;
begin
  select zipcodes.zip 
      into validzip 
  from zipcodes 
  where :new.zip = zipcodes.zip;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :NEW.ZIP := 67226;
end;

That should satisfy your assignment, but in The Real World (tm) if you handed me this design for review I'd toss it back at you with a note on it saying "No business logic in triggers!". As explained here triggers should never be used to enforce business requirements - that's what application code is for.

Best of luck.

01403. 00000 'No data found'

3 Answers3