Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








ORA-01403: no data found tips

Oracle Error Tips by Burleson Consulting


Oracle docs note this about ORA-01403:

       ORA-01403 no data found
Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query.

Action: Terminate processing for the SELECT statement

Many people experience ORA-01403 in association with the SELECT INTO clause.

SELECT INTO clauses are standard SQL queries which pull a row or set of columns from a database, and put the retrieved data into variables which have been predefined. 

If the SELECT INTO statement doesn't return at least on e row, ORA-01403 is thrown.

Here is an example:
SQL> declare

2 v_authName author.author_last_name%type;

3 begin

4 select

5 author_last_name into v_authName

6 from

7 author

8 where

9 author_state = 'FL';

10 dbms_output.put_line('Name: '||v_authName);

11 end;

12 /



ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 4

Because the query is looking to retrieve authors in Florida and there are none, the ORA-01403 error is thrown.  To avoid ORA-01403, the PL/SQL has to contain exceptions , otherwise the query will offer to values to the defined variable.  If the exceptions are not defined properly, you will encounter ORA-01403, and your situation may worsen each time you attempt to access the particular variable. Creating an exception in which only one row can be retrieved would allow the code to appropriately handle the exception without receiving the ORA-01403 error. 

To create this SELECT INTO query that will avoid the ORA-01403 error, and handle the appropriate exceptions, here is an example:
SQL> create or replace function auth_Name

2 ( v_auth_state IN author.author_state%type)

3 return varchar2

4 as

5 v_authName author.author_last_name%type;

6 begin

7 select

8 author_last_name into v_authName

9 from

10 author

11 where

12 author_state = v_auth_state;

13 return v_authName;

14 exception


16 then return 'Too Many Authors in that



18 then return 'No Authors in that State';

19 when others

20 then raise_application_error(

21 -20011,'Unknown Exception in authName


22 end;

23 /

Function created.

Here, the function has handled the exceptions and returns appropriately, or returns the exception back to the calling block.  In doing this, the code above avoids the ORA-01403 error, and works correctly.

You may want to view this article for further information on the SELECT INTO query:

There is also information on ORA-01403 having to do with NOLOGGING clauses in this great article. Here is a common cause of ORA-01403 when attempting operations in NOLOGGING clauses from Oracle docs:

For logical standby databases, when SQL apply operations encounter a redo log record for an operation performed with the NOLOGGING clause, it skips over the record and continues applying changes from later records. Later, if an attempt is made to access one of the records that were updated with NOLOGGING in effect, the following error is returned: ORA-01403 no data found

To recover after the NOLOGGING clause is specified, re-create one or more tables from the primary database, as described in Section 9.1.6.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.



oracle dba poster

Oracle performance tuning software 
Oracle Linux poster