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