It's been an awfully long time since I ran into a problem that turned out to a bug with the Oracle database itself. The database has evolved to become so rock-solid that it's a pretty rare event for me, although I'm constantly banging on it in my daily tasks to help customers make effective use of JDeveloper and our ADF framework. In fact, checking our internal bug database, the last database bug I filed was in June of 1997.
But yesterday I was looking into a user Fred's claim that ADF Business Components was mishandling TIMESTAMP-valued attributes using our Refresh-on-Insert or Refresh-on-Update feature.These are simple attribute properties that you set on those entity object attributes that you know will be updated by database-level application logic (like a BEFORE INSERT FOR EACH ROW or BEFORE UPDATE FOR EACH ROW trigger, for example). Fred had a CREATED_ON and MODIFIED_ON column of type TIMESTAMP whose values he was managing using database triggers. He decided against using the ADF entity object's built-in "history attributes" management -- where he could have indicated declaratively that he wanted ADF to manage the values of certain entity object attributes to remember things like CreatedBy, CreatedOn, ModifiedBy, or ModifiedOn -- since he had some other non-Java client applications modifying those same tables. So, for example, his FRED_TABLE had a trigger like this:
CREATE TRIGGER maintain_modified_by BEFORE UPDATE ON fred_table FOR EACH ROW
BEGIN
:new.modified_on := SYSTIMESTAMP;
END;
Trouble is, after updating some data in his Fred entity and committing, the value of the ModifiedOn attribute was getting corrupted into some very weird-looking timestamp value. With a little bit of good ol' simplifying, I was able to reproduce the problem using only a simple SQL statement like this:
UPDATE fred_table
SET some_column = 'new value'
WHERE id = 1
RETURNING modified_on INTO :sqlplus_var;
ADF Business Components by default uses Oracle's RETURNING INTO clause to retrieve the Refresh-on-Insert and Refresh-on-Update attribute values without having to perform two round-trips to the database. It turns out that the particular combination of:
- Updating a TIMESTAMP column with a BEFORE [INSERT | UPDATE] FOR EACH ROW trigger, and
- Retrieving this newly assigned TIMESTAMP value using the RETURNING INTO clause
Was a recipe for somehow corrupting the value of the TIMESTAMP data. In fact, after performing my test SQL above, a simple:
SELECT modfied_on FROM fred_table WHERE id = 1
was giving me an ORA-1877 error, "String too long for buffer". So, I filed a bug to get the issue fixed in the database, and then explained to Fred how he could work around the problem by indicating that his entity should not use the RETURNING INTO clause.
The way to do this is:
- Add a custom EntityDefImpl class for your entity object on the "Java" panel of the EO Editor
- Override the method like this to return false:
// In your custom YourEntityDefImpl.java class
public boolean isUseReturningClause() { return false; }
That keeps the database-maintained timestamp values happy.
5:51:41 PM 
|
