Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    38

    Unanswered: Exception handling in User Defined Function

    Hi All,

    I am writing an User Defined function. I am trying to handle exceptions, but unable to do so. I have few queris:
    1.)Is exception handling is possible in DB2 UDF.
    2)If possible how can I wirte a WHEN NO DATA FOUND excption(oracle's eqivalent) in db2.
    3.)Can anyone please figure out where I am missing in the following code :

    CREATE FUNCTION DOM9OCT.xyz( lo_sal integer )

    RETURNS INTEGER

    LANGUAGE SQL

    READS SQL DATA

    ------------------------------------------------------------------------

    -- SQL UDF (Scalar)

    ------------------------------------------------------------------------

    F1: BEGIN ATOMIC

    declare l_var integer;

    declare l_flag char(1);

    DECLARE GENERATEQUERYEXCEPTION CONDITION FOR SQLSTATE 'OR000';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING

    BEGIN

    -- SET l_var=5;

    END;

    set l_flag='N';

    set l_var=0;

    while l_flag='N' do

    Select PARENTENTITYID as l_var from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121;

    end while;

    --end;

    RETURN l_var;

    END





    Thanks
    Raj Shekhar

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: Exception handling in User Defined Function

    test this:

    Select PARENTENTITYID into l_var from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121;

  3. #3
    Join Date
    Jun 2003
    Posts
    38

    Re: Exception handling in User Defined Function

    Originally posted by achiola
    test this:

    Select PARENTENTITYID into l_var from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121;
    Hi,

    The select into clause doesnot work inside a function.
    Secondly , I wanted to know whether exception handling is possible in udfs and if yes then how can I do that. In my code snippet that I have posted, I am getting errors while handling exceptions. whereas similar things work in procedures.

    Thanks,
    Raj
    Raj Shekhar

  4. #4
    Join Date
    Jun 2003
    Posts
    38

    Re: Exception handling in User Defined Function

    Originally posted by Raj Shekhar
    Hi,

    The select into clause doesnot work inside a function.
    Secondly , I wanted to know whether exception handling is possible in udfs and if yes then how can I do that. In my code snippet that I have posted, I am getting errors while handling exceptions. whereas similar things work in procedures.

    Thanks,
    Raj
    can I do a select into clause in an UDF. Its giving me error.How to do that.
    Raj Shekhar

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: Exception handling in User Defined Function

    Raj,
    You cannot use the SELECT INTO, but you can do:

    SET l_var = (Select PARENTENTITYID from umlcompositestate where compositestateid = l_var and modelid =2 and parententitytype = 121);

    HTH

    Andy

    Originally posted by Raj Shekhar
    can I do a select into clause in an UDF. Its giving me error.How to do that.

Posting Permissions

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