• Increase font size
  • Default font size
  • Decrease font size

Oracle exceptions I

Here is example of 3 situations which can happen when using statement 'select column into variable where .. '

1. Create test table: create table tmp(a varchar2(255));

2. Insert into column 'a' following values: 1,1,2

3. Run following procedure:

declare
lv_str         varchar2(255);
 gv_err_msg     varchar2(255);
 begin
 
 select   a 
 into     lv_str
 from     tmp 
 where    a = '2';
 dbms_output.put_line('lv_str:'||lv_str);
 EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 gv_err_msg := replace(substr(SQLERRM,1,200),'ORA-20001: ',NULL);
 dbms_output.put_line('gv_err_msg: '||gv_err_msg);
end;

The result from procedure is 'lv_str:2'

declare
lv_str         varchar2(255);
gv_err_msg     varchar2(255);
begin
select   a
into     lv_str
from     tmp
where    a = '1';
dbms_output.put_line('lv_str:'||lv_str);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
gv_err_msg := replace(substr(SQLERRM,1,200),'ORA-20001: ',NULL);
dbms_output.put_line('gv_err_msg: '||gv_err_msg);
end;

The result from procedure is 'gv_err_msg: ORA-01422: exact fetch returns more than requested number of rows'

declare
lv_str         varchar2(255);
gv_err_msg     varchar2(255);
begin
 
select   a
into     lv_str
from     tmp
where    a = '10';
dbms_output.put_line('lv_str:'||lv_str);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
gv_err_msg := replace(substr(SQLERRM,1,200),'ORA-20001: ',NULL);
dbms_output.put_line('gv_err_msg: '||gv_err_msg);
end;

The result from procedure is 'gv_err_msg: ORA-01403: no data found'

 

 

Add comment

No bad words.


Security code
Refresh


New articles

Differential Equations - Simple spring model Machine Learning and Robotics
Read More 1962 Hits
Kalman Filter implementation Machine Learning and Robotics
Read More 2780 Hits
Inverse kinematics Machine Learning and Robotics
Read More 3651 Hits
gpsim tutorial Machine Learning and Robotics
Read More 8877 Hits

Most read content

A* search algorithm Machine Learning and Robotics
Read More 11225 Hits
gpsim tutorial Machine Learning and Robotics
Read More 8877 Hits

New articles

Differential Equations - Simple spring model Machine Learning and Robotics
Read More 1962 Hits
Kalman Filter implementation Machine Learning and Robotics
Read More 2780 Hits
Inverse kinematics Machine Learning and Robotics
Read More 3651 Hits
gpsim tutorial Machine Learning and Robotics
Read More 8877 Hits
Design by i-cons.ch / etosha-namibia.ch