Exception Handling

[dksuresh]

The following code is returning an error

create or replace procedure sk_test
as
v_sk number(3);
v_errm varchar2(100);
begin
select 5/0 into v_sk from dual;
insert into sk values(v_sk);
commit;
exception
when zero_divide then
begin
v_sk:=0;
SELECT 5/1 into v_sk from user_objects ;
exception
when too_many_rows then
insert into sk values(0);
oommit;
end;
end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "REBOOKER.SK_TEST", line 16
ORA-01422: exact fetch returns more than requested number of rows
ORA-01476: divisor is equal to zero
ORA-06512: at line 1

The code within the second exception is not executed.Can I have a method wherein the code with the second exception is executed.

Thanks is advance

[balajiyes]

hi this is wht i did and worked fine:

just try increasing the size of variable and check the table structure (column size)

v_sk number(3);
v_errm varchar2(100);



19:18:46 SQL> create table sk(a number);

Table created.

Elapsed: 00:00:00.02
19:18:57 SQL> create or replace procedure sk_test
19:19:09 2 as
19:19:09 3 v_sk number(3);
19:19:09 4 v_errm varchar2(100);
19:19:09 5 begin
19:19:09 6 select 5/0 into v_sk from dual;
19:19:09 7 insert into sk values(v_sk);
19:19:09 8 commit;
19:19:09 9 exception
19:19:09 10 when zero_divide then
19:19:09 11 begin
19:19:09 12 v_sk:=0;
19:19:09 13 SELECT 5/1 into v_sk from user_objects ;
19:19:09 14 exception
19:19:09 15 when too_many_rows then
19:19:09 16 insert into sk values(0);
19:19:09 17 oommit;
19:19:09 18 end;
19:19:09 19 end;
19:19:09 20 .
19:19:11 SQL> ed
Wrote file afiedt.buf

1 create or replace procedure sk_test
2 as
3 v_sk number(3);
4 v_errm varchar2(100);
5 begin
6 select 5/0 into v_sk from dual;
7 insert into sk values(v_sk);
8 commit;
9 exception
10 when zero_divide then
11 begin
12 v_sk:=0;
13 SELECT 5/1 into v_sk from user_objects ;
14 exception
15 when too_many_rows then
16 insert into sk values(0);
17 commit;
18 end;
19* end;
19:19:18 SQL> /

Procedure created.

Elapsed: 00:00:00.03
19:19:18 SQL> exec sk_test

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
19:19:32 SQL> select * from sk;

A
----------
0

Elapsed: 00:00:00.00

[balajiyes]

check the spelling commit. its "oommit;" in ur code