Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: function runtime exception

    I use db2 9.7.6 full edition for windows. I need develop functions which can be use on sql select expression. Functions must contain modifying sql data and handling runtime exceptions. There are two variants, but I have problems of implementations all requirments in ever variants. The first variant is implementation of sql table function, e.g.

    *++CREATE FUNCTION func1 (val CHAR(20))++
    ++RETURNS table(result varchar(1000))++
    ++LANGUAGE SQL++
    ++MODIFIES SQL DATA++
    ++BEGIN atomic++
    ++insert into a values(val);++
    ++ return (select result from a);++
    ++END++*
    It work, but I can't implement handling exception how in sql procedures. When I tried to use block "declare exit handler", I got a syntax errors. The second variant is implementation of pl/sql function, e.g.

    *++CREATE OR REPLACE FUNCTION bb++
    ++RETURN varchar2++
    ++MODIFIES SQL DATA++
    ++AS++
    ++BEGIN++
    ++ insert into st values ('a');++
    ++ return 0;++
    ++END bb;++*
    But when I tried to execute this function, I got error "SQLCODE=-740, SQLSTATE=51034 is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked". Help me please. best regards, Turkin Andrew.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How are you trying to invoke the Function?

    Andy
    Code:
    SQL0740N
    
    Routine routine-name (specific name specific-name) is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked.
    Explanation
    
    The routine routine-name (specific name specific-name) is defined with MODIFIES SQL DATA and is therefore not allowed in:
    
        a BEFORE trigger that is not defined using a compound SQL (compiled) statement
        a correlated subquery
        a search-condition (such as a WHERE clause or check constraint)
    
    Module functions and functions defined with a compound SQL (compiled) statement as the function body that are defined with the MODIFIES SQL DATA option can only be the sole expression on the right hand side of an assignment statement in a compound SQL (compiled) statement.
    User response
    
    Ensure that routine resolution resolved to the desired routine. Remove the routine from the failing statement or redefine the routine with an SQL access indicator other than MODIFIES SQL DATA.
    
    sqlcode: -740
    
    sqlstate: 51034

Tags for this Thread

Posting Permissions

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