CODEWITHSUNDEEP
oracle-databaseselectsql-update
Travis
Travis

Reputation: 1095

Oracle Select Works, Update Doesn't, Same WHERE Clause

FINAL UPDATE:

Turns out, in our case anyway, that this was not a bug. Hidden away deep in the function call was an exception handler that was obfuscating an error message indicating that the function was mutating. The handler hid it away and caused the function to return what is essentially a valid value. So it's on us, this time. :)

Original Post:

First this is really more of a curiosity, now, as I've worked around the issue with a PL/SQL block. However, neither I nor any of my coworkers can figure out this update won't work. Does anyone have any ideas?

I am trying to update a new column with appropriate values. Previously, the status of the record was determined using a function which computed status on demand, and we just converted it to use a dedicated table, allowing status to be stored and improving future flexibility.

The below select works great - it pulls 14 records:

select * 
from QUERY_TABLE QT1
where QT1.P_ID in
  (select QT2.P_ID opi
    from QUERY_TABLE QT2
    where F_GET_STATUS(QT2.FUNC_VAL_1, 
      (select RT.FUNC_VAL_2 from RELATED_TABLE RT where RT.RELKEY = QT2.RELKEY)) = 'Value');

However, the below update, using the same WHERE clause, updates 0 records:

update QUERY_TABLE QT1
set QT1.STAT_ID = 1
where QT1.P_ID in
  (select QT2.P_ID opi
    from QUERY_TABLE QT2
    where F_GET_STATUS(QT2.FUNC_VAL_1, 
      (select RT.FUNC_VAL_2 from RELATED_TABLE RT where RT.RELKEY = QT2.RELKEY)) = 'Value');

Not sure if it will help, but the below PL/SQL block works fine to handle the update:

begin
for x in (
  select QT2.P_ID opi
    from QUERY_TABLE QT2
    where F_GET_STATUS(QT2.FUNC_VAL_1, 
      (select RT.FUNC_VAL_2 from RELATED_TABLE RT where RT.RELKEY = QT2.RELKEY)) = 'Value')

loop
  update QUERY_TABLE QT1
  set QT1.STAT_ID = 1
  where P_ID = x.opi;
  end loop;
end;
/

I've performed the update using the schema owner and another user with the appropriate privileges. There is not a trigger on the table invalidating the update. The function doesn't do anything weird, and the function and update don't eat each other (it's a brand new column - function is syntactically independent from the column's value). It doesn't give any error messages - it just updates 0 columns.

UPDATE 1 For those who may have the same issue, we've contacted Oracle, and it turns out that this is a new bug. It has been logged as Bug 17015253 : UPDATE STATEMENT WITH FUNCTION IN SUBQUERY DOES NOT UPDATE ROWS, though I don't see it in the knowledge base, yet.

Upvotes: 3

Views: 2124

Answers (2)

Kevin
Kevin

Reputation: 11

My manager is great!! She suggested that by adding:
pragma autonomous_transaction;
after the 'is' in the function body would solve the problem.
See http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/autotransaction_pragma.htm for details.

Ex.
Function xyz (...)
  return varchar2
  is
   pragma autonomous_transaction;
   something varchar2(10);
   .
   .
   .
begin
.
.
.
  return something;
end xyz;

Upvotes: 1

Travis
Travis

Reputation: 1095

Just so there's an answer to the question - an exception handler in the function was obfuscating an error message informing us that the function was mutating. This made it appear as though the function and syntax were valid.

Upvotes: 0

Related Questions