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.