Mule returns oracle cursor issue

Question

My requirement is to handle multiple cursors returned from the stored procedure.

Here is my flow, can you suggest me how to do it in mule?

Exception

Root Exception stack trace: java.sql.SQLException: Invalid column index at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)

---

<flow name="get:/snapshot/shippingtable/{identifier}:shippingtable-config" doc:name="get:/snapshot/shippingtable/{identifier}:shippingtable-config"> 
  <set-payload value="#[flowVars.identifier]" doc:name="Set Payload">
  </set-payload>  
  <db:stored-procedure config-ref="Oracle_Configuration" doc:name="Database">
      <db:parameterized-query><![CDATA[{ call p_sample_proc(:after) }]]></db:parameterized-query>
            <db:in-param name="after" type="VARCHAR" value="SQA"/>
            <db:out-param name="r1" type="REF"/>
            <db:out-param name="r2" type="REF"/>
   </db:stored-procedure>
   <custom-transformer class="com.guthyrenker.shippingtable.parser.ShippingTableChangedAfterParser" doc:name="Java"/>
</flow>

Answer 1

I think the problem is because the returned cursors are not parameters, or at least you didn't include them in the p_sample_proc call. If the stored procedure returned two result sets, try removing the db:out-param lines and take the result sets from the payload. The payload should contain a map from string to result sets, with keys resultSet1 and resultSet2. HTH, Marcos.