I am trying to setup an adapter notification that calls a store procedure in Oracle 10g. The store procedure has only OUT parameter of type REF CURSOR. Whatever I do I am not able to get the whole resultset returned in the notification publishable document. It allways returns only one row which is not acceptable for me. When I setup a regular adapter service for calling that stored procedure, it returns the whole resultset, but if I call it from the notification service - It wont return more than one row! Am I doing something wrong or just there is a bug in JDBC Adapter? Could anybody tell me whether you have done this with adapter notification?
My IS is 7.1, JDBC Adapter is 6.5, DB is Oracle10g, IS server is running on Solaris.
Is there any way at all to return multiple rows in a single adapter notification publishable document?
Come on … so many experts here … someone must have done this.
I am thinking for two possible workarounds:
Schedule in wm scheduler normal adapter service for stored procedure call. Get as many rows as needed, create and publish document that holds the multiple records, delete the rows after publishing the guaranteed document.
Use Adapter notification and in the service that receives the adapted notification published doc just ignore the data from the document and call stored procedure that returns ref cursor. After that, the same as 1).
Any suggestions or advices what scheduling option would be better and why. Any difference if I have a clustered environment?