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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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 /

declare

*

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

15 when TOO_MANY_ROWS

16 then return 'Too Many Authors in that

State';

17 when NO_DATA_FOUND

18 then return 'No Authors in that State';

19 when others

20 then raise_application_error(

21 -20011,'Unknown Exception in authName

Function');

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:

http://www.dba-oracle.com/t_pl_sql_plsql_select_into_clause.htm


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 training Excel
 
Oracle performance tuning software