I am mainlining a legacy system and realize that the answer to this may take a deeper explanation of the code but before I go that far I wanted to make sure the solution wasn't something more simple.

Here is the situation:

the function/s in question just generate and return a new recID in our system, nothing really that complicated at all just some pretty situation-specific business logic. One thing this function (in this case the update I am about to describe happens in another function called by the first. this second function just contains the update statement, that is all.) is supposed to update a status record from active to complete. very simple.

I am seeing a strange difference in this update based on how the function is executed.

In some places in the code the function is executed with: SELECT function(X) FROM DUAL;

In other places in the code the function is executed with:

  RetVal number;
  RetVal := function(X);
  DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal));

In places where the first example is used, the status doesn't get changed.

When the second example is executed, the status does change.

Could this be something at a higher level that I don't understand/remember about how these two examples execute? Any Ideas? I can't imagine that it matters but I am running these through Toad.


If you have a function that does an UPDATE and you try to call that from a SELECT statement, you should get an Oracle error saying that you cannot do DML inside a query. So your SELECT statement should be throwing an error. My first guess would be that you have an exception handler somewhere that, intentionally or not, is catching and discarding the error. My next guess would be that the function was declared using an autonomous transaction pragma, which is almost certainly a bug because that would mean that the function was running in a separate transaction scope from the calling code and could not see uncommitted changes made by the caller (such as, for example, inserting the row that the function is trying to update).

In general, if you have a piece of code that is doing an update, that should be a stored procedure not a stored function. That would mean that it cannot be called from SQL (just like a function that does DML cannot be called in a SQL statement). But that is exactly what you want. You don't want a function called in a SQL statement to have a side effect because you cannot control things like how many times the function is called. Even in a SELECT from dual, the function could be called multiple times by Oracle (though that is unlikely).

| improve this answer | |
  • ah yes. those autonomous transaction pragma statements are everywhere. So their use is generally a bad thing? I think in our code in some places it was a way to write everything in functions.. which I don't pretend to understand. So if i re-write that function as a stored procedure it should work fine? – Andrew Jul 25 '14 at 7:41
  • On a similar note if you had a function using this autonomous transaction pragma would that make it a lot easier to get in a deadlock with itself? – Andrew Jul 25 '14 at 7:45
  • @Andrew - When I see an autonomous transaction, I read that as "Please note that this code is about to do something incredibly stupid that will have a large number of poorly understood side effects". If you are doing anything other than writing to a log that you want to persist the logging if the parent transaction fails, you're doing something wrong. Among other things, yes, you'd expect to get a metric crud load of deadlocks if you have autonomous transactions all over the place. – Justin Cave Jul 25 '14 at 18:59
  • I literally just laughed out loud. Your description is completely perfect. I will have to just re-write it all. Honestly, that's probably a good thing. As I am sure you could probably already assume logging is NOT what the original author wrote in this case. Thanks for the explanation. – Andrew Jul 25 '14 at 19:23
  • @Andrew - If there was one feature that I could remove, or at least require an extensive examination before it was used, it would be autonomous transactions. They are very useful in a couple of very specific situations. But they are giant "manual override" lets you overrule Oracle when it attempts to stop you from doing something stupid. And the vast majority of people using them are not smarter than the database. – Justin Cave Jul 25 '14 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.