Oracle error: cannot perform DML in a query

Why do I get the following error?

Error starting at line 1 in command:

select FUNC from dual
Error report:
SQL Error: ORA-14551: cannot perform a DML operation inside a query 
ORA-065开发者_开发知识库12: at "ANONYMOUS.FUNC", line 15
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

FUNCTION:

create or replace function FUNC
return types.ref_cursor
AS
result_set types.ref_cursor;
alarm ofalarmmessages.ALARMID% TYPE;
username ofalarmmessages.USERNAME% TYPE;
alarmmsg ofalarmmessages.ALARMMESSAGE% TYPE;
dvice ofalarmmessages.DEVICEID% TYPE;
begin
OPEN result_set FOR
       SELECT USERNAME,ALARMID,ALARMMESSAGE,DEVICEID
    FROM   ofalarmmessages where newoldflag='N';
LOOP
    FETCH  result_set into username,alarm,alarmmsg,dvice;
        update ofalarmmessages set newoldflag ='Y' where alarmid= alarm;
END LOOP;
RETURN result_set;
CLOSE result_set;
END;
/
show errors;

---

There are two problems here. You are getting the 'ORA-14551' error because you are executing your function in a SELECT statement, and as the error message clearly indicates, we cannot do this when the function executes DML. The solution for this problem is to execute it in PL/SQL or SQL*Plus.

The other problem is that a ref cursor is a pointer to a result set, which can be fetched only once. Your function iterates through the ref cursor and then returns it. This will result in an error when you try to do something with the returned ref cursor, because it is no longer valid.

Oh, and by the way, any code which follows the RETURN statement in a function never gets executed.

---

You can avoid using the function in a SELECT statement like this:

var rc refcursor
exec :rc := func;

---

you can call it

SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.PUT_LINE(your_fn_name(your_fn_arguments))

---

You can achieve what you have described above by doing a simple update query:

update ofalarmmessages 
set newoldflag='Y'
where newoldflag='N';

Is there a reason why you really MUST have a function?

---

you add "PRAGMA AUTONOMOUS_TRANSACTION" for update

create or replace function FUNC
return types.ref_cursor
PRAGMA AUTONOMOUS_TRANSACTION;
/* ... */

try it