Oracle error: cannot perform DML in a query

Question

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-06512: 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;

Yup, you'll get that error if you try to call this function in a SELECT statement. What's your question? — Dave Costa
– Dave Costa, Commented Sep 5, 2011 at 12:26
how to get the result set as well update operation from same function. — Balmukund
– Balmukund, Commented Sep 5, 2011 at 12:28
Anyway, you have some real problems with your code here. How will the LOOP ever terminate? Why are you returning a cursor that's already been exhausted? How do you think the CLOSE is going to execute after the RETURN? — Dave Costa
– Dave Costa, Commented Sep 5, 2011 at 12:29
OR please let me know how to use var rc refcursor exec :rc := func; in my procedure. — Balmukund
– Balmukund, Commented Sep 5, 2011 at 12:30
Please, please don't do update operations inside a function unless absolutely necessary, i.e. you HAVE to return something. It's just dirty otherwise and causes the errors you're getting. Use a procedure to update; it's then clear what is going on. — Ben
– Ben, Commented Sep 5, 2011 at 15:41

APC · Accepted Answer · 2011-09-05 13:07:34Z

7

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.

answered Sep 5, 2011 at 13:07

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Andrews · Accepted Answer · 2011-09-05 11:35:30Z

3

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

var rc refcursor
exec :rc := func;

answered Sep 5, 2011 at 11:35

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

8 Comments

Balmukund Over a year ago

Thank you for your response.But my question is that how to do the update operation inside loop which i have mentioned in above procedure.

Tony Andrews Over a year ago

Is it? I thought your question was why do you get that error?

Balmukund Over a year ago

Dave can you help me to get the required result.

Tony Andrews Over a year ago

My answer is what I suggest you do instead of select func from dual.

Balmukund Over a year ago

Tony, can you guide me to solve the above mentioned problem and get the desire result.

|

gnom1gnom · Accepted Answer · 2013-11-13 13:59:43Z

2

you can call it

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

answered Nov 13, 2013 at 13:59

gnom1gnom

7527 silver badges15 bronze badges

Comments

Jason Aller · Accepted Answer · 2019-04-25 21:02:31Z

1

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?

edited Apr 25, 2019 at 21:02

Jason Aller

3,66028 gold badges43 silver badges40 bronze badges

answered Sep 5, 2011 at 17:19

Hugh Jones

2,7641 gold badge20 silver badges34 bronze badges

Comments

emcconville · Accepted Answer · 2014-03-20 04:13:06Z

0

you add "PRAGMA AUTONOMOUS_TRANSACTION" for update

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

try it

edited Mar 20, 2014 at 4:13

emcconville

24.6k4 gold badges52 silver badges66 bronze badges

answered Mar 20, 2014 at 3:51

ToanBom

1

Collectives™ on Stack Overflow

Oracle error: cannot perform DML in a query

5 Answers 5

Comments

8 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

8 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related