CODEWITHSUNDEEP
databasemulesoftdatabase-cursorora-01000
Kiran
Kiran

Reputation: 29

MuleSoft 4: ORA-01000: maximum open cursors exceeded

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.

Upvotes: 0

Views: 470

Answers (1)

aled
aled

Reputation: 25699

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.

You could also use a third party pool library that allows to configure a maximum time to live for connections, so they are reclaimed eventually, however that method though good to catch some leaking connections doesn't guarantee that the connections will be returned in time to be reused.

Upvotes: 0

Related Questions