I have the following problem: when JDBC adapter of type StoredProcedureWithSignature is changed (even by simply locking and saving it) or DB is restored from backup or similar changes are made, it stops working. Suddenly it stops seeing its procedure/function name, which is for example <.“ESBW.CHANGE_PRODUCT_ADMIN_DATA”>. During the call the underlying DBMS or adapter machinery complains:
ESBW is an Oracle package name.
The problem seems to occur due to double dot at the beginning. If I manage to change the procedure name to <ESBW.CHANGE_PRODUCT_ADMIN_DATA> (by making Procedure Name Pattern empty and putting my own procedure name), after adapter service save, the procedure parameters are wiped out, most likely because ESB adapter no longer sees such procedure name, which leads to this error:
That most likely means the procedure gets improperly called due to unwillingly emptied signature.
The only solution I have found so far is to create new adapter service of type StoredProcedure (without signature this time) and manually put IN/OUT/RETURN parameters, and put procedure name without this fancy <.">. The problem with this solution is that at the end of the day nobody wants to recreate hundreds of services, it would impose a considerable burden and would be error prone and require lots and lots of regression tests as well.
To put it simply, I wonder why ESB JDBC adapter service sees all DB procedures in <.“xxx”> format (in Procedure Name box), but if it is invoked, it fails. Also, how to make it work without huge effort.
I use com.wm.dd.jdbcx.oracle.OracleDataSource driver, but the problem occurs also in some other drivers I’ve tested.
Product webMethods Integration Server
Build Number 228
SSL Strong (128-bit)
Java Version 1.6.0_27 (50.0)
Java VM Name Java HotSpot™ 64-Bit Server VM
Java Build Info 20.2-b06, mixed mode
Java Vendor Sun Microsystems Inc.