When executing queries or statements which invoke functions or procedures, the error “The routine … tries to read SQL data which is not allowed with the current access mode” (error code -14029) may appear. What does this error mean and how can we correct the function declaration to avoid the error?
Let’s look at an example situation where we have a function FUNC which in turn runs a query on the table TAB:
SQL>create databank db;
SQL>create table tab (c1 integer);
SQL>@
1@create function FUNC()
2@returns integer
3@begin
4@ declare R integer;
5@ select max(c1) into R from TAB;
6@ return R;
7@end
8@@
SQL>set ? = FUNC();
Mimer SQL error -14029 in function EXECUTE
The routine SYSADM.FUNC tries to read SQL data which is not allowed with the current access mode
As can be seen the error is returned when the function is invoked.
This is because the function is not declared to read or modify SQL data with the clause READS SQL DATA or MODIFIES SQL DATA.
The clause MODIFIES SQL DATA indicates that the function may modify database data. It may do so by itself or it may call other functions which are.
The clause READS SQL DATA indicates that the function may read database data. It may do so by itself or it may call other functions which are. It may not modify database data and it may not call other functions which are.
If omitted CONTAINS SQL DATA is assumed. This means the function may not read or modify database data and it may not call other functions which are.
In this case, the function reads database data through a SELECT INTO statement. The below showcases a correct declaration.
SQL>create databank db;
SQL>create table tab (c1 integer);
SQL>@
1@create function FUNC()
2@returns integer
3@reads sql data
4@begin
5@ declare R integer;
6@ select max(c1) into R from TAB;
7@ return R;
8@end
9@@
SQL>set ? = FUNC();
?
===========
-
1 row found
SQL>
This article refers to a function but access modes applies to procedures as well.