Exception Handling
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Exception Handling

  1. #1
    Join Date
    Apr 2002
    Posts
    41

    Exception Handling

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
    Cheers!
    OraKid.

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    check the spelling commit. its "oommit;" in ur code
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width