Different outcome based on function execution method

functionsoracletoad

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:

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

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.

Best Answer

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).

Related Question