ORA-01403: data to choose from

Question

I get an exception ORA-01403: no data found for the following query. What are the possibilities of this error?

 SELECT trim(name) INTO fullname FROM ( SELECT n.name FROM directory dir, store n WHERE dir.name = n.name AND dir.status NOT IN ('F', 'L', 'M') ORDER BY n.imp, dir.date) WHERE rownum <= 1; 

How can I handle this error?

Answer 1

Despite the fact that you set a condition, the best way would be to process the register of the record not found or "No data". I would write the code above wrapping the SELECT statement with my own BEGIN / EXCEPTION / END block.

The code could be something like this:

  BEGIN SELECT trim(name) INTO fullName FROM (SELECT n.name FROM directory dir, store n WHERE dir.name = n.name AND dir.STATUS NOT IN ('F','L','M') ORDER BY n.imp, dir.date ) WHERE rownum <= 1; EXCEPTION WHEN NO_DATA_FOUND THEN fullName = NULL; END; 

Answer 2

If the standard exception handling described by Sandeep seems too complicated (as in my case) and you are fine with NULL or some individual <not found> value ), you can simply convert it like this:

 select col into v_foo from bar where 1=0 -- would provoke ORA-01403 

=> no ORA-01403 raised:

 -- if NULL would be fine: select (select col from bar where 1=0) into v_foo from dual -- if individual "NOT_FOUND" value should be set to avoid standard exception handling: -- (it depends on your col type, so it could eg be 'NOT_FOUND' or -1 -- or to_date( 'yyyy-mm-dd', '2100-01-01') ) select nvl( (select col from bar where 1=0), 'NOT_FOUND' ) into v_foo from dual 

Answer 3

Perhaps because your request

 SELECT n.name FROM directory dir, store n WHERE dir.name = n.name AND dir.STATUS NOT IN ('F','L','M') ORDER BY n.imp, dir.date 

does not return rows

Answer 4

If dir.status has any zeros in your table, then not in probably does not do what you think. You can get zero rows even if you think you need to get one or more rows. You can switch to and not (dir.status in ('F,'L','M')) instead of dir.status not in ('F','L','M') .

See this for reference.