Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: Calling a stored procedure in a user defined function

    Hello,

    I am trying to call a stored procedure in a user defined function.

    CREATE FUNCTION test ()
    RETURNS DECIMAL (15,4)
    LANGUAGE SQL

    READ SQL DATA
    EXTERNAL ACTION



    F1: BEGIN
    DECLARE VAR1 DECIMAL(15,4);
    DECLARE VAR2 VARCHAR(256);

    call test1('C','F',545,VAR1,VAR2);

    RETURN var1;


    END

    The Stored procedure 'test1' does a lot of SQL work inside it and finally gives two output parameters and does not any DML operation inside it.

    Inside UDF 'test' when this stored procedure is called, following error is seen.

    SQL0577N User defined routine <> (specific name
    "") attempted to modify data but was not defined as MODIFIES SQL DATA or was
    used in a context that does not allow MODIFIES SQL DATA. LINE NUMBER=29.
    SQLSTATE=38002

    I understand that Maximum allowed SQL access level in scaler UDF is READS SQL DATA and this is what my code is doing.

    http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/inde x.jsp?topic=%2Fcom.ibm.db2.luw.apdv.routines.doc%2 F doc%2Fr0020478.html

    Problem here is I could solve this in my Development Env and codes works really fine as expected, but when I was trying to deploy this call in other Enviroment, I got the above error. It proves conceptually and logically there is no problem in the code.

    More or less looks to be an Environment Issue. Have done a rebind to the package got created for Stored Procedure.

    Am I missing something.

    OS: RHEL 5.5
    DB2 - 9.7 FP4

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the definition of the stored procedure?

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    Hello,

    Procedure reads some data from some tables and apply some logic and gives away the values.

    No DML statements inside procedure just reading of data based on some business logic.

    As mentioned, code really works fine and does not work in other env

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you please post the DDL for the stored procedure. Without it I cannot help.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It would also help to see how you (@mohitgyl) call the function in both environments. Copy/paste the session output instead of vaguely describing it in your own words.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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