ORA-01403: no data found for Select into

Question

I am getting ORA-01403: no data found exception 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

Although you have put a WHERE condition, a better way would be to handle case of record not found or 'No Data Found' error. I would write above code with wrapping the SELECT statement with it's own BEGIN/EXCEPTION/END block.

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 to much overhead (like in my case) and you're fine with a NULL or some individual <not found> value), you might just transform 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 e.g. 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

Probably because your Query

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

is not returning any rows