When Too_Many_Rows

Too_many exception is ora error no ORA-01422 when you issue a select statement into single variable that returns more than one row at that time this error will occur.if you want to handle this situation in your annonymous block or procedure use TOO_MANY_ROWS

exception.
The advantage of this approch is that you can customise your your messages user friendly rather
oracle messages.

Example:- Write a pl sql block to accept first_name from user return his/her last_name.

declare
v_name varchar2(20);
begin
select last_name into v_name from employees
where first_name='&fname';
dbms_output.put_line('First_name of an employee is'||v_name);
Exception
when too_many_rows then
dbms_output.put_line('There are multiple employeesin this department');
end;

The Foll is the output
caught TOO_MANY_ROWS exception..

Enter value for fname: John
old 5: where first_name='&fname';
new 5: where first_name='John';
There are multiple employeesin this department

PL/SQL procedure successfully completed.







No comments: