Oracle: DML operation inside a query

Question

I'm getting this error:

cannot perform a DML operation inside a query

when i tried to execute the query

select st_atten_up(1,7) from dual;

the code has given below.

create or replace FUNCTION st_atten_up(stu_id IN student_info.id%type,app_mon IN student_attendence.month%type) 
RETURN NUMBER 
IS 
att1 NUMBER;
BEGIN SELECT ATTENDANCE into att1 FROM student_attendence 
WHERE student_attendence.id = stu_id and student_attendence.month = app_mon; 
att1 := att1 + 1;
UPDATE student_attendence SET ATTENDANCE = att1 
where id = stu_id and month = app_mon;
return att1;
END;

Thanks in advance.

Use `pragma autonomous_transaction;` inside your function to make it callable from `select` statement. — Egor Skriptunoff, Jul 26 '13 at 19:06

score 5 · Accepted Answer · answered Jul 26 '13 at 19:10

You can technically execute DML inside a select if the function called has been declared as PRAGMA AUTONOMOUS_TRANSACTION (Link). However, it's rarely a good idea to execute DML from within a SELECT statement for a number of good reasons (including mutating tables, performance degradation.) But, to answer your question, you can write your function with the PRAGMA:

create or replace FUNCTION st_atten_up(stu_id IN student_info.id%type,app_mon IN student_attendence.month%type) 
RETURN NUMBER 
IS
PRAGMA AUTONOMOUS_TRANSACTION;
att1 NUMBER;
BEGIN SELECT ATTENDANCE into att1 FROM student_attendence 
WHERE student_attendence.id = stu_id and student_attendence.month = app_mon; 
att1 := att1 + 1;
UPDATE student_attendence SET ATTENDANCE = att1 
where id = stu_id and month = app_mon;
return att1;
END;

Substitute rarely for never... call the function multiple times in a SQL statement and what happens? The student ends up with 150% attendance... — Ben, Jul 26 '13 at 22:30
@wolf thanks it's working. Just i need to add "commit" to prevent the >>ORA-06519:Active autonomous transaction detected and rolled back : — nixon1333, Jul 29 '13 at 22:28

score -1 · Answer 2 · answered Jul 26 '13 at 18:59

-1

You cannot call a function in DML if the function in itself is having a DML operation.

Here you would be trying to call the function in SELECT/UPDATE.. and thats why its giving the error. If you want to do so please create a procedure.

answered Jul 26 '13 at 18:59

Harshit

560
1
5
15

Oracle: DML operation inside a query

2 Answers2