Grid Editor problem with CLOBs and Triggers

This is a fairly detailed issue, with 2 problems coming from it. I've attached a script that creates the 2 tables needed, the trigger, and an insert statement. It also has additional comments for testing purposes. Happens the same on Oracle 10g or 11g database. When the Grid Editor is used to update or insert a CLOB field, it causes the associated trigger to fire twice, AND loses data from the :NEW value in the Trigger. This is NOT a problem with the trigger, as it works fine using a simple update or insert statement. 1) Create the tables and trigger 2) Run the insert statement and then select * from sub_aud. You'll see only one entry for the insert, as it should be. 3) Run the update statement in the file. The sub table will be properly updated, and the sub_aud table will have 2 more entries (B-Before and A-After) for the change. 4) Select * from sub, double-click sl_criteria, change it, and save. *THIS IS WHERE THE PROBLEM OCCURS!* 5) Select * from sub_aud. You can see that the same update action caused the trigger to fire twice, and the :NEW value for sl_criteria is lost. *THIS DOESN'T HAPPEN WHEN USING A SIMPLE UPDATE STATEMENT!* This issue seems to be related to the Grid Editor component somehow, and only for CLOBs. This action happens on an insert or an update - again, only seeming to affect CLOBs. This happens in the 6.x and 7.x versions.

Hi Sammy, Thanks for the detailed report and example. I have been able to reproduce the problem. It happens because when a CLOB (or BLOB) value is updated, DbVisualizer first updates the column with the value set to empty_clob() and then with the real value. This was the only way to do it using JDBC prior to Oracle 10g. I have, however, found signs that it may be possible to do it in a different way for 10g and later, so I will take a closer look at this. Hopefully we can then avoid the problem for 10g and later versions. Best Regards, Hans

I wanted to see if you'd had a chance to look at this issue any further for 10g and later. This causes a large problem for us, because the scenario I detailed for you is how we handle tracking updates to our tables. We use this mechanism as an audit log. Because this doesn't work, I can't use DBVis for my database work on these tables, because it loses the CLOB value and write the records out twice. It would be a huge benefit to us if this could be corrected in an upcoming release. Thanks, Sammy

Hi Sammy, We have found a solution that will be included in the next feature version, scheduled for release early summer. Best Regards, Hans

Excellent! Thanks for finding a solution to this.