1. ORA-01403 in Stored Procedure: no data found Error
In the stored procedure, select field name into variable from table name where .........;
An error with ORA-01403: no data found occurs if the query is empty
Test Table:
Create table TEST_TABLE
(
T_ID NUMBER,
T_NAME VARCHAR2 (20)
);
Test the stored procedure:
Create or replace procedure pro_test is
V_id test_table.t_id % type;
Begin
Select t_id into v_id from test_table where rownum = 1;
End pro_test;
Error:
2. solution:
The primary cause of the error is that there is no corresponding data in the database. When the query statement is directly used, no error is reported and 0 records are returned.
2.1. Use Aggregate functions for query Fields
Add a min function. This is mainly because 0 is returned when the aggregate function does not find the data, rather than null.
The stored procedure is changed:
Create or replace procedure pro_test is
V_id test_table.t_id % type;
Begin
Select min (t_id) into v_id from test_table where rownum = 1;
End pro_test;
These are Aggregate functions, such as sum, count, max, and min. Its function does not work, such as to_char and substr.
In addition, if nvl is used, that is
Select nvl (t_id, 1) into v_id from test_table where rownum = 1;
Is it ineffective or an exception is reported. Nvl only processes null values, while select t_id into v_id from table returns null records.
Disadvantages: 1. Using a set function may deviate from the value you need to search for; 2. This method will significantly reduce the efficiency when the data volume is large.
Reference: a null value problem occurs when select a into B is used in Oracle.