Home » SQL & PL/SQL » SQL & PL/SQL » Why DML operation cannot be performed inside query---ora:14451
Why DML operation cannot be performed inside query---ora:14451 [message #277927] Thu, 01 November 2007 04:27 Go to next message
naresh090183
Messages: 6
Registered: November 2007
Location: Chenai
Junior Member
Hi to all Smile

if i use select statment to return something inside a function,It works fine.. Razz

example:
select function() from dual;

CREATE OR REPLACE function fun return number is
temp number(30);
begin
select code into temp from table1 where id=1;
return temp;
end;
(works fine....)

but when i use other DML statement it returns error cannot perform DML operation inside query...
Shocked
example:
select function() from dual;

CREATE OR REPLACE
function fun return number is
temp number(30);
begin
select code into temp from table1 where id=1;
update table2 set val1 = 100 WHERE VAL1='ABC';
return temp;
end;

(throws error......) Mad
can anyone suggest me why this error occurs here.....

Regards,
Naresh Kumar Murali
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277930 is a reply to message #277927] Thu, 01 November 2007 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

2/
"throws error......" does not help very much to know which error you got

3/
Use SQL*Plus and copy and paste the whole session

4/
DML are not allowed inside a SELECT.

Regards
Michel
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277932 is a reply to message #277930] Thu, 01 November 2007 04:44 Go to previous messageGo to next message
naresh090183
Messages: 6
Registered: November 2007
Location: Chenai
Junior Member
thanks for ur reply michel

am using oracle 8.1.7 version

DML Operations cannot be performed inside a query...this is the error message am recieving when i use function inside the select statement.Kindly suggest why this error occurs..

Regards,
Naresh Kumar Murali
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277933 is a reply to message #277932] Thu, 01 November 2007 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 01 November 2007 10:37

4/
DML are not allowed inside a SELECT.

Regards
Michel

Re: Why DML operation cannot be performed inside query---ora:14451 [message #277935 is a reply to message #277932] Thu, 01 November 2007 05:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have a look at this.

When somebody ask you to post the error, never forget to post the SQLCODE.

By
Vamsi

[Updated on: Thu, 01 November 2007 05:00]

Report message to a moderator

Re: Why DML operation cannot be performed inside query---ora:14451 [message #277942 is a reply to message #277927] Thu, 01 November 2007 05:14 Go to previous messageGo to next message
naresh090183
Messages: 6
Registered: November 2007
Location: Chenai
Junior Member
Hi vamsi

Kindly c the topic which consists of the error message and i have posted even the code in first request.It seems u to miss out of ur sight.

Regards,
Naresh kumar Murali
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277949 is a reply to message #277942] Thu, 01 November 2007 05:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
huh! Subject!! Shocked
Sorry! I missed it.

By
Vamsi
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277950 is a reply to message #277942] Thu, 01 November 2007 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But the error in the topic is wrong:
ORA-14451: unsupported feature with temporary table
 *Cause:  An attempt was made to create an IOT, specify physical attributes,
          specify partition or parallel clause.
 *Action: do not do that.

It should be:
ORA-14551: "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.

This is why I asked:
Michel Cadot wrote on Thu, 01 November 2007 10:37

3/
Use SQL*Plus and copy and paste the whole session

And this should be ALWAYS done as well as the other points.

Regards
Michel
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277954 is a reply to message #277949] Thu, 01 November 2007 05:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel answered the question, but I'm not sure you understood it.
Michel

4/ DML are not allowed inside a SELECT.


DML we are talking about is
update table2 set val1 = 100 WHERE VAL1='ABC';
while SELECT is
select function() from dual;

If evaluated, it would look like this:
SELECT (SELECT some_value  INTO ...; UPDATE table2 SET ...; RETURN some_value) FROM dual
or even
SELECT function's_return_value and, please, UPDATE some table at the same time FROM dual;
which can not be done.
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277960 is a reply to message #277927] Thu, 01 November 2007 05:41 Go to previous messageGo to next message
naresh090183
Messages: 6
Registered: November 2007
Location: Chenai
Junior Member
hi all

thanks for ur reply .i do understand whats the problem now..

Regards,
Naresh Kumar Murali
Re: Why DML operation cannot be performed inside query---ora:14451 [message #277974 is a reply to message #277960] Thu, 01 November 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're welcome and I hope you will follow the guidelines next time.

Regards
Michel
Re: Why DML operation cannot be performed inside query---ora:14451 [message #278022 is a reply to message #277974] Thu, 01 November 2007 08:54 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Thu, 01 November 2007 07:12

You're welcome and I hope you will follow the guidelines next time.



Especially when using words like u, ur and c. I am sure they are proper words in your native tongue, but they are not proper or professional english words, which this forum uses.
Re: Why DML operation cannot be performed inside query---ora:14451 [message #278109 is a reply to message #277927] Thu, 01 November 2007 18:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Actually, insert,update,delete can be done from a select. I dont' like it, and have yet to be faced with a problem that could only be solved with it, and I think it is bad design, but...

It can be done. The trick is to use autonomous_transactions.

I do not remember at what release the autonomous_transaction was added. And I do not have access at the moment to an 8.1.7 version of oracle, so I cannot say that you can do it on your specific instance.

But, if you make the function an autonomous_transaction, the update will work.

Again however, it is ill advised. In fact, as I recall now, I wrote an article for OraFAQ which talks about autonomous_transactions. Go read it. It has I believe an example for you.

http://www.orafaq.com/node/1915

good luck, Kevin
Re: Why DML operation cannot be performed inside query---ora:14451 [message #278148 is a reply to message #278109] Fri, 02 November 2007 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

It can be done. The trick is to use autonomous_transactions.

Actually all seniors posting here know that but, I think and this is my case, it is an option you can't suggest to newbies.
Even if you warn.

Remember, if something seems useful, no warn is read (even if you post it in bold, size 5, red, underline, blinking...) only "yes I can do it with this" is kept in memory.

Regards
Michel
Re: Why DML operation cannot be performed inside query---ora:14451 [message #278248 is a reply to message #278148] Fri, 02 November 2007 09:50 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, I'll try to keep that in mind. Thanks
Previous Topic: How to change the format
Next Topic: PLS-00103: Encountered the symbol ";" when expecting one of the following:
Goto Forum:
  


Current Time: Thu Dec 08 16:10:19 CST 2016

Total time taken to generate the page: 0.26556 seconds