Search My Ramblings

Monday, April 27, 2009

Oracle BPEL Database Adapter DMLTypes

First of all, I'm not an Oracle software engineer on the BPEL Process Manager product line, but having worked with it for a couple of years, I will at least provide what I think is going on with the DML Types available for use in the database adapter, since I have yet to find a spot where they've documented them clearly.

What I'm talking about here are the choices available when creating a database adapter partner link in BPEL using a table operation, not using direct SQL. I think the obvious ones that are well understood are Insert Only, Update Only, Delete and Select. The one not as clearly documented is the Insert or Update option.

If you select this option, it creates a WSDL for the partner link that includes a couple of DML (database manipulation language) operations called Write and Merge. The database adapter documentation says it is preferred to use Write over Merge for performance reasons but doesn't elaborate.

On my current project which integrates to a custom table into Peoplesoft, I first tried using the Write operation in my BPEL process. It would consistently create new records in the table since their isn't a primary key or unique index defined using the true primary keys. I then switched it to Merge, and voila it worked. What I suspect is happening is for Write operations, the adapter attempts to insert the record, and if the database errors out due to unique constraint violation it will automatically try the update operation. So if the table doesn't have a unique constraint for your key set, the Write will not work. Conversely, the Merge operation will look at your toplink mappings file and select using the primary key you define in the wizard, and if a row is returned, will perform an update, otherwise perform an insert. The Merge also supports hierarchical database structures, which I wasn't using it for in this case.

Hopefully this helps understand when and why to use the Write and Merge operations of the Database Adapter. Let me know if my assumptions about the inner workings are correct, as this is written from the perspective of an observer and not an inside developer.

2 comments:

Michael A. Fons said...

JLewis,

Thank you for this post! It was exactly what I was looking for. It seems posting on "merge" are a bit sparse on the internet.

Michael F.

Rasia said...

Hi JLewis,

Thanks for the post, it helped me alot.

Rasia.