Home » SQL & PL/SQL » SQL & PL/SQL » cannot perform a DML operation inside a query (Oracle 10g, HP Unix)
cannot perform a DML operation inside a query [message #361317] Tue, 25 November 2008 22:42 Go to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Dear Guys,
I have a function which I need to call in a query. The problem is that i want to store the value calculated in the function for the future use and for that I use the insert statement which it will not allow.

Kindly help me how i can store the value and still call the function in the query.

Regards,
Muhammad Usman
Re: cannot perform a DML operation inside a query [message #361318 is a reply to message #361317] Tue, 25 November 2008 22:59 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Muhammad Usman,

You cannot/shouldn't use a function in a query if it does DML. There reason is simple and a very solid one too: SELECT statements are supposed to be kind of READ-ONLY. Functions that do DML violate this principle, so Oracle refuses to run them.

If we want to do DML in a function we should write a specific function to do so. We don't want a function that does DML to be used in a SELECT statement (even if it were possible) because sooner or later someone will use that function the wrong way and execute an update/insert when they thought they were just reading some data.

Regards,
Jo

[Updated on: Tue, 25 November 2008 23:06]

Report message to a moderator

Re: cannot perform a DML operation inside a query [message #361322 is a reply to message #361317] Tue, 25 November 2008 23:17 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Ofcousre I understand the point.

What i wanted to say was that the value calcuated in the function against a certain parameter is valuable to me.
I don't want to call the function again to do the same calculation against the same input parameter, instead i will get the value without calling the function.

Is there any way in ORACLE to achive this ?
Re: cannot perform a DML operation inside a query [message #361325 is a reply to message #361322] Tue, 25 November 2008 23:31 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First call the function and return the value into a variable and then use this variable inside the select if you want to (but I don't any reason to do it).
It depends on what does your function do but this is the good way to do.

Regards
Michel
Previous Topic: "continue" in pl/sql
Next Topic: query
Goto Forum:
  


Current Time: Sat Dec 10 12:31:59 CST 2016

Total time taken to generate the page: 0.13849 seconds