MuleSoft 4: ORA-01000: maximum open cursors exceeded

Question

Getting an error

ORA-01000: maximum open cursors exceeded

after processing few records from file inside try-catch scope. I've 3 select statements and 5 stored procedure calls(have insert statement inside stored procedure) in side try-catch scope.

Here is my pooling profile config:

<db:pooling-profile maxPoolSize="10" preparedStatementCacheSize="0" /> 

Using default configurations in the stored procedure:

<db:stored-procedure doc:name="insert into SPCHG_SERVICE_RENDERED" doc:id="d5b44d97-0f00-4377-a98d-b35a6b78df9e" config-ref="Database_Config" transactionalAction="ALWAYS_JOIN">
            <reconnect count="3" />
            <db:sql ><![CDATA[{call schema.ARRAY_INSERT_SERVICE(:serviceData,:error_num,:error_msg)}]]></db:sql>
            <db:input-parameters ><![CDATA[#[{"serviceRenderedData" : payload}]]]></db:input-parameters>
            <db:output-parameters >
                                
                                <db:output-parameter key="error_num" type="INTEGER" />
                                <db:output-parameter key="error_msg" type="VARCHAR" />
                            </db:output-parameters>
        </db:stored-procedure>

Runtime: 4.3

Any inputs to fix/avoid this issue.

Note: DBA team not going to increase cursor count, looking for solution from MuleSoft end.

Answer 1

Ensure that you are consuming the output of the stored procedure, even if not expecting a result set, by putting the operation in a separate flow inovked with a VM like the documentation suggest.

Alternatively put a foreach after the db operation.