Topic
No replies
SystemAdmin
SystemAdmin
5837 Posts
ACCEPTED ANSWER

Pinned topic handling runtime exception for sql functions

‏2012-10-20T21:49:10Z |
Hello!
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"
best regards, Turkin Andrew.