ORA-01403: nodatafound and selectaintob null values

ORA-01403: nodatafound and selectaintob null values

ORA-01403: no data found and select a into B null values

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.