ORA-01403: no data found for Select into

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
How can I handle this error? 7,624 18 18 gold badges 67 67 silver badges 120 120 bronze badges asked Jan 17, 2014 at 12:47 4,532 29 29 gold badges 92 92 silver badges 155 155 bronze badges ORA-01403: no data found occurs when you SELECT INTO and no rows are returned. Commented Jan 17, 2014 at 12:55

You tell us.. Have you checked for data in directory where STATUS NOT IN ('F','L','M')? Have you checked data in store for name fetched from directory?

Commented Jan 17, 2014 at 12:55 Yes. i have checked both of the values. Getting at least one value. Commented Jan 17, 2014 at 12:59

there seems to be a bug, at least in our Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit environment with it!: function bar return varchar2 as x dual%rowtype; begin select * into x from dual where 1=0; return 'test'; end; normally returns without an error and returning no results :-/ .. just mentioned because catching NO_DATA_FOUND explicitely works here as mentioned in Sandeeps answer: stackoverflow.com/a/21186642/1915920

Commented Mar 22, 2019 at 9:21

5 Answers 5

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
2,371 27 27 silver badges 19 19 bronze badges answered Jan 17, 2014 at 12:59 608 6 6 silver badges 7 7 bronze badges How can I write that for this case? Commented Jan 17, 2014 at 13:00

Tried posting answer in comment, but it is losing indentation when posted as a comment. Hence added code snippet in my answer above.

Commented Jan 18, 2014 at 8:36 Thanks. What happens if we didn't put fullName = NULL; in side the exception block? Commented Jan 18, 2014 at 12:08 That is just a place holder. You can handle this error as per your logic. Commented Jan 21, 2014 at 6:19

It would be better to select count(*) of the query first, and don't query if no rows will be returned back. Both ways work, but count(*) can avoid throwing an exception.

Commented Mar 14 at 21:11

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 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