Evening listers,
Just upgraded the ol' prod database to 12c this week and hit with a real
doozy that we didn't see (and can't replicate) in dev or staging. This is
12.1.0.2 with the April PSU on 64-bit Linux.
We have a trigger on a table that does a check to control which fields our
app allows to be updated. That list if fields is in another table. I'm
going to over simplify it but the logic is loosely like this:
v_current_column := control_pkg.get_current_column();
for c in (
select column_name from column_control_table
where owner='FREEDOM' and table_name='DTS_TEST_TAB'
order by column_name
) loop
if updating(c.column_name) and (c.column_name !=
v_current_column or (some other logic here))
then
raise exception here
end if;
end loop;
The updates on this table are done one column at a time. The logic is
basically to verify that we are updating the column through our controlled
procedure and not allowing a manual update. It isn't a popular setup and
there are plans to move away from it but for now it has claws in a few key
parts of the application.
The problem we're seeing is that updating(c.column_name) will return TRUE
when we are updating a different column. For example, if our UPDATE
statement was:
UPDATE test_tab SET foo='don';
We would see updating('BAR') return true, even though the bar column is not
part of the UPDATE statement. This would result in the first two IF checks
matching and the exception being thrown and the update disallowed, even
though it should have been fine. The real fun is that it only happens on a
specific column for this table. In this case the first three columns
correctly return false, the 4th one returns true. The column we're actually
trying to update is way down the list (48th, sorted alphabetically).
Like I said, we don't see this in the same triggers on dev or staging
(which are clones of prod) otherwise obviously we wouldn't have updated
prod. And when I try to contrive my own example like one with
all_tab_columns, it works as expected as well, no false positives from
UPDATING(field) on either dev, staging or prod.
I've got an SR on it but I thought I'd reach out to see if anyone has seen
anything like this. I feel like my brain is melting out of my ears after
spending all day on this one.
Right now the only workaround is to re-write the trigger to manually check
each column to see what's being updated and whether or not to allow it.
Each of the 73 columns in this table. And we're seeing it on a similar
trigger on another table. You can imagine the fun we'll have if/when we
need to add or drop columns. The trigger as it is allowed us to simply loop
through the column list procedurally and do the same comparison logic
without hard-coding anything.
Thanks.
--
Don Seiler
http://www.seiler.us
