ORA-01403 (no data found) in the trace

ORA-01403 is the "no data found" code. In a cursor read, it only marks the normal end of the data. That is why the report separates it from the other errors and, when it is the only code in the trace, shows a finding that is only informational.

Symptoms

In the Diagnosis, the finding n ORA-01403 (no data found) (FIM_DE_DADOS) appears, with the text Normal in read loops. and the View errors link. The severity is info, with no percentage of the total time.

The finding appears only when ORA-01403 is the only error code in the trace. If there is another code, the finding for Oracle errors in the trace appears instead, and its count leaves 1403 out. The ORA-01403 occurrences are still listed in the Errors section in both cases.

Cause

The code's card, in the Errors section, sums up the two origins:

• In a select ... into in PL/SQL, the query returned no row, and PL/SQL raises the NO_DATA_FOUND exception.
• In a cursor fetch, it is only the normal end of the data: in read loops, it appears once per cursor traversed.

Resolution

Most of the time, there is nothing to fix. To check:

1. Click View errors.
2. On the ORA-01403 card, check in Where it appeared the Call, the Statement, the Depth and the Occurrences of each place.

   Result: you know which statement generates the code and how many times.

3. Compare the occurrences with the executions of the statement, in Calls in its detail.

It is worth acting in three cases:

• Many occurrences coming from a select ... into that weighs on the time: the code tests existence row by row and uses the exception to control the flow. Replace it with set-based SQL, with a join, exists or merge (row by row in PL/SQL).
• The row should exist, such as a missing record or a wrong key: the error is a sign of inconsistent data. Check the data the statement looks for.
• The PL/SQL hides the error with when others then null: the same block also hides the other errors. Handle NO_DATA_FOUND by name.

How to confirm

If you changed the code, generate the trace of the same operation and compare the ORA-01403 occurrences in Errors and the Inclusive (s) column of the statement that generated it, in SQL statements by contribution. With the loop replaced by set-based SQL, both drop.

In the report

The sample report has no errors, and the Errors section only appears when the trace has some.

• Errors: the ORA-01403 card, with What it is, When it appears, What to do and Where it appeared (errors).
• Diagnosis: the finding and the View errors link (Diagnosis).
• Statement detail: the Errors panel and the Calls table, with the executions of the statement (statement detail).
• SQL statements by contribution: the inclusive time of the statement that generates the code (SQL statements).