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>

you have posted 50 questions without accepting a single answer. Accepting an answer is a way of giving back to the community. You improve the quality of the content by pointing out which answer resolved the issue. Please go back and accept the answers where appropriate. If you don't know how, read this. — aioobe

MarcosNC MarcosNC · Accepted Answer · 2015-02-19T15:18:57

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.

Mule returns oracle cursor issue

Exception

1 Answers