I am trying to call a stored procedure in a user defined function.
Expand|Select|Wrap|Line Numbers
- 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
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