| Table of Contents |
|---|
Version
6.1.1 or below.
The error code and message have been changed for 6.3.1
Explanation
The following error occurs when a user function is used inside the SELECT statement:
| Code Block | ||
|---|---|---|
| ||
iSQL> select func1() from dual; |
| Note |
|---|
[ERR-1105D : Unable to begin a new update statement. |
The error code and message have been changed for 6.3.1 as follows:
| Code Block | ||
|---|---|---|
| ||
iSQL> select func1() from dual; |
| Note |
|---|
[ERR-31386 : Cannot perform a DML, commit, or rollback inside a query. In FUNC1 |
Cause
The following error descriptions can be viewed with the AltiErr utility:
# 6.1.1 or below
| Panel |
|---|
$ altierr 0x1105D # *Cause: Either the statement is read-only, or more than one update child statement has been requested. # *Action: Please make sure that the request to begin a new update statement is valid. |
# 6.3.1
| Panel |
|---|
$ altierr 0x31386 # *Cause : # - The program attempted to perform a DML, commit, or rollback inside a query. # *Action : # - Do not use a DML, commit, or rollback statement inside a query. |
When a user function is used inside the SELECT statement, the function must only include the SELECT statement.
However, this error occurs when DMLs are included in a user function.
Action
Remove the DML statement from a user function inside the SELECT statement.
# In the following example, the error occurs when a function including the INSERT statement is used within the SELECT statement.
| Code Block | ||
|---|---|---|
| ||
iSQL> create or replace function func1() return varchar(10) as c1 varchar(10); 2 begin 3 select c1 into c1 from t1; 4 insert into t1 values(c1); 5 return c1; 6 end; 7 / Create success. iSQL> select func1() from dual; |
| Note |
|---|
[ERR-1105D : Unable to begin a new update statement. |
# In the following example, the above function is successfully executed using the EXECUTE statement
| Code Block | ||
|---|---|---|
| ||
iSQL> select * from t1; C1 -------------- abc 1 row selected. iSQL> var c1 varchar(10); iSQL> exec :c1 := func1(); Execute success. iSQL> print var; [ HOST VARIABLE ] ------------------------------------------------------- NAME TYPE VALUE ------------------------------------------------------- C1 VARCHAR(10) abc iSQL> select * from t1; C1 -------------- abc abc 2 rows selected. |
# In the following example, the function is successfully executed when the INSERT statement is removed from the SELECT statement.
| Code Block | ||
|---|---|---|
| ||
iSQL> create or replace function func1() return varchar(10) as c1 varchar(10);
2 begin
3 select c1 into c1 from t1;
4 return c1;
5 end;
6 /
Create success.
|
| Code Block | ||
|---|---|---|
| ||
iSQL> select func1() from dual; FUNC1 -------------- abc 1 row selected. |
Reference
N/A