Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    30

    Unanswered: Why can't call a user defined function within stored procedures?

    Hi there,

    I have built a stored procedure, say sp_proc1. Within the stored procedure, I call a user defined function, say my_udf1. I get the following error message:

    Error: SQL0440N No authorized routine named "MY_UDF1" of type "FUNCTION" having compatible argument was found. SQLSTATE=42884 (State:42884, Native Code: FFFFFE48)

    It seems that the UDF my_udf1 with right argrument either not exist or I have no right to access. However, sp_proc1 and my_udf1 are under the same data schema, and I have full rights to access both of them. Do anyone has idea or hints for me to shoot this problem?

    Thanks in advance.
    Athens Yan.

    P.S.: The DB server version is 8.1.4 (AIX), and font-end client version is 8.1.0 (Windows).

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

    Re: Why can't call a user defined function within stored procedures?

    Hi.
    Please, check the numbers and data type of your paramenters.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Why can't call a user defined function within stored procedures?

    Also, does it work from a command line

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Feb 2004
    Posts
    30

    Re: Why can't call a user defined function within stored procedures?

    First, thanks all your help.

    To: achiola

    I have searched the IBM web site, and obtained some hints. However, they don't give me any help to overcome my problem even I follow their ways. So, I would like anybody have met the same problem to give me a practicle advise.

    To: sathyaram_s

    The same error message is appeared on CLP and Command Center. Any idea?

    Moreover, in accordance with IBM's official supporting information, the problem may be caused by client settings on DB2CLI.INI (a parameter CURRENTFUNCTIONPATH). I have made the change on it as follows:

    CURRENTFUNCTIONPATH="DB2HK"

    Note: "DB2HK" is the schema of my database.

    The result is unchanged, and error SQL0440N still exists.

    Regards,
    Athens.

  5. #5
    Join Date
    Feb 2004
    Posts
    30

    Re: Why can't call a user defined function within stored procedures?

    One more fact: I can call all built-in UDF of DB2. For example, the following SQL command is worked very well:

    select UCASE(my_field) from my_table;

    Athens.

  6. #6
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question explicit quailifier

    Hav you tried calling the function with qualifer?

    i.e. adding the specifik schema in front of your
    function inside the SP?
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  7. #7
    Join Date
    Feb 2004
    Posts
    30

    Re: explicit quailifier

    To Tank:

    Yes, I have tried this way before. Any further suggestion?

    Regards,
    Athens.


    Originally posted by Tank
    Hav you tried calling the function with qualifer?

    i.e. adding the specifik schema in front of your
    function inside the SP?

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: explicit quailifier

    Can you post your function definition and the output of
    "select p.routineschema,p.routinename,ordinal,typename from syscat.routines r,syscat.routineparms p where p.routineschema=r.routineschema and p.routinename=r.routinename and p.routinename='<your routine name>' and routinetype='F'"

    Plus, of course, you exact function call ...

    Someone may be able to help

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Feb 2004
    Posts
    30

    Re: explicit quailifier

    Hi Sathyaram,

    After executing your suggestion SQL command, I get the following result:

    ROUTINESCHEMA ROUTINENAME ORDINAL TYPENAME
    -------------------- ----------------------- ----------- ------------
    DB2HK SF_GET_CDE_DESC 0 VARCHAR
    DB2HK SF_GET_CDE_DESC 1 CHARACTER
    DB2HK SF_GET_CDE_DESC 2 CHARACTER
    DB2HK SF_GET_CDE_DESC 3 CHARACTER

    This function is defined as follows:

    CREATE FUNCTION SF_GET_CDE_DESC(
    PIC_COMPANY_NUM CHARACTER(1)
    ,PIC_CDE_TYP CHARACTER(5)
    ,PIC_CDE_ITM CHARACTER(8)
    )
    RETURNS VARCHAR(2000)
    F1: BEGIN ATOMIC
    RETURN
    SELECT cde_desc
    FROM cde_desc
    WHERE company_num = pic_company_num
    AND cde_typ = pic_cde_typ
    AND cde_itm = pic_cde_itm;
    END

    And the function 'SF_GET_CDE_DESC' is called within a stored procedure which is defined as follows:

    CREATE PROCEDURE sp_eserv_get_clt_policy_list (
    IN pic_company_num char(1)
    ,IN pic_client_num char(8)
    ,IN pic_adviser_num char(10)
    ,INOUT pioi_sqlcode integer
    )
    DYNAMIC RESULT SETS 3
    BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
    SELECT distinct
    '2' as dummy_col
    ,c.contract_num as policy_num
    ,SF_GET_CDE_DESC(pic_company_num, 'EPOS', c.contract_status) as policy_status
    ,c.currency_cde as currency
    ,SF_GET_CDE_DESC(pic_company_num, 'MTHD', c.payment_method) as payment_method
    ,c.commence_dte as commence_date
    ,b.short_description as benefit_desc
    ,SF_GET_CLT_BEN_AMT(pic_company_num, c.contract_num, pic_client_num, a.comp_cde, a.comp_option) as benefit_amount
    ,SF_GET_CLT_PRM_AMT(pic_company_num, c.contract_num, pic_client_num, a.comp_cde, a.comp_option) as premium_amount
    ,( SELECT case when count(1) > 0 then 'Y' else 'N' end
    FROM contract_extra_premium
    WHERE company_num = pic_company_num
    AND contract_num = c.contract_num
    AND comp_cde = a.comp_cde ) as premium_loading
    , a.comp_option
    , a.sum_assured
    , a.basic_instalment_premium
    , b.present_order
    FROM contract_header c
    ,life_comp a
    ,component_master b
    WHERE c.company_num = a.company_num
    AND c.contract_num = a.contract_num
    AND a.client_num = pic_client_num
    AND a.comp_cde = b.comp_cde
    AND c.contract_status IN ('40', '41', '50', '51', '52', '53', '54')
    AND a.comp_cde NOT IN ('PFUSD', 'PFHKD')
    AND a.comp_cde <> 'RTU'
    AND ( ( pic_adviser_num is not null AND
    EXISTS ( select 1
    from contract_adviser s1
    where s1.company_num = a.company_num
    and s1.contract_num = a.contract_num
    and s1.adviser_num = pic_adviser_num ) )
    OR pic_adviser_num is null
    )
    ORDER BY c.contract_num, b.present_order
    ;

    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION
    set pioi_sqlcode = -1;

    if pic_adviser_num = '' then
    set pic_adviser_num = null;
    end if;

    open c1;
    END;
    END


    The procedure 'sp_eserv_get_clt_policy_list' always return nothing due to the said error by calling the embraced UDF 'SF_GET_CDE_DESC'.

    I have recreated all the related UDFs, but I am still caught by the error. This error almost drives me crazy, if anybody gets solving solution, please help.


    Thanks,
    Athens Yan.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: explicit quailifier

    If you have chars as input parms, you will be to explicitly cast each input parm as a char of that length ...

    ie

    SF_GET_CDE_DESC(char(pic_company_num,1), char('EPOS',8), char(c.contract_status,10)) as policy_status

    You may have to create your function to accept VARCHAR as the input parms instead of CHARs , then you can use the function as you have used in the proc ...

    There may be better ideas ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Feb 2004
    Posts
    30

    Re: explicit quailifier

    Hi Sathyaram,

    It works!! Thank you so much. However, it also confuses me. Is 'char' not compatible with 'character'? I always treat them as the same thing on my SQL stored procedures/functions.

    Regards,
    Athens.

Posting Permissions

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