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'
