Urgent - Handling 2 exception in one function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Urgent - Handling 2 exception in one function

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    Hi

    I have two small tables
    1) ServiceMaster

    Name Null? Type
    ----------------------------------------- -------- --------------------
    ID NOT NULL NUMBER
    NAME VARCHAR2(20)

    2) Users

    Name Null? Type
    ----------------------------------------- -------- ---------------
    USERID NOT NULL NUMBER(10) (Primary Key)
    USERNAME VARCHAR2(20) (Unique Index)
    PASSWORD VARCHAR2(10)
    SERVICEID NUMBER (References ServiceMaster)

    I am writing a function to insert data into Users Table. And This procedure should not fire any exception on server as it is used by java program. There I want to handle 2 exceptions which occur on web server running.

    My Code of the Function

    create or replace function ins1(uid in number,uname in varchar2,
    pword in varchar2, eno in number, sname in varchar2) return number is
    cl number;
    begin
    select id into cl from servicemaster where name = sname;
    insert into users values(uid,uname,pword,eno);
    return 1;
    exception when no_data_found then return 0;
    exception when dup_val_on_index then return 0;
    end;
    /

    But the function seems cannot handle 2 exceptions at ones. I have written following procedure which works very fine if I handle any one of two exceptions but it does not even compile if I handle both. Can Anyone let me know how to handle multiple exceptions in one

    Amol

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You should write 'exception' only once:
    Exception
    when...
    when....

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    Thanks a lot Raminder
    That was a good timely reply.. it really helped

    Amol

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by amolik
    Thanks a lot Raminder That was a good timely reply.. it really helped
    I am glad I was of help to somebody



    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    exceptions
    when ...
     when ...
    this is sintax rule, what do you want?

    if need many exceptions you can write
    exceptions
    when OTHERS then ...


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