Identity Field issued with DBAdapter - Oracle SOA Suite 11g

Recently I was trying to execute a Stored Procedure from a SQL Server 2008 Database, but I have faced the error below:

Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'DBAdapter_NAME' failed due to: RowSet Column Type Mismatch Error. The SQL type in the XSD (INT) does not match the SQL type in the RowSet (int identity) for column COLUMN_NAME of parameter RowSet0. This procedure returns a RowSet which could in theory return any arbitrary result. However you chose at design time to generate a single strongly typed XSD describing this result set in all cases, based on a test execution of the stored procedure. This makes later XML transforms easier. You may have also edited the XSD directly. Test this procedure to see if it this result set returns different columns given different inputs. If so you may need to revert back to using a weakly typed XSD. Or the strongly typed XSD might simply need correcting. This exception is considered not retriable, likely due to a modelling mistake. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.

So, the first analysis I have done was check the Stored Procedure code and the tables related with the results of the Stored Procedure. Then I identified the COLUMN_NAME as a field with the definition as per below:

• [COLUMN_NAME] [int] IDENTITY(1,1) NOT NULL

The schema generated with the DBAdapter wizard fetching the Stored Procedure got me the element definition into the result RowSet definition related with this field:

• <element name="COLUMN_NAME" type="int" db:type="INT" minOccurs="0" nillable="true"/>

Then, I just changed the db:type attribute from INT to int identity manually - you can do it just after fetching the Stored Procedure results in the DBAdapter wizard or after this, changing the schema manually. So, the definition will be:

• <element name="COLUMN_NAME" type="int" db:type="int identity" minOccurs="0" nillable="true"/>

In this case, I had to change four more fields, which in its Table Definition, they are a identity field.

The wizard was not enabled to fetch this detail into design time, so, if you have this error with different DBTypes which isn't an ANSI type (NVARCHAR2, for example), you can proceed on the same way, changing manually only the db:type attribute.

In the next post I'll show you how to discover in a easier way the changes you should do into the schemas!