The code as it is (not my pseudo code example) worked perfectly prior to
upgrade.
And yes we isolated the upgrading() call and saw that it was returning true.
Don.
On Aug 19, 2015 9:56 PM, "Andrew Kerber" <andrew.kerber@xxxxxxxxx> wrote:
Are you sure you don't have a problem with your conditions? That or
condition seems to be missing parentheses that may cause it to evaluate as
true more than you might expect.
Sent from my iPad
On Aug 19, 2015, at 8:59 PM, Don Seiler <don@xxxxxxxxx> wrote:
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
