Pega should be closing the connection and thereby close the cursor once it is done executing the Connect-SQL rule. Are you sure the number of open cursors is increasing over time because Pega is not closing the connections?
If that is the case, the problem needs to be investigated by Pega support team.
We are sure,it does not closes cursors as we were in parallel monitoring cursor count in database.raised an SR with pega but as usual some irrelevant details they asked for which is no way related to my question.
Response from Pega:-
Hello Shyam, There's no command to close the cursor because Pega should do so automatically. As it is not doing so, something is wrong. I have some ideas on how to find the root cause. For now, if possible, you should try increasing the number of cursors that can be open to help the issue while we continue the root cause analysis. It is better to have slowdown than to have the application crash. Please provide the following or I cannot continue investigation on this case: 1) Settings for maximum open cursors in the database 2) prconfig.xml 3) The documents you have showing that the cursors are not being closed. 4) PegaRULES log (there might be a stack trace that shows the issue). 5) ulimit settings (OS level) 6) How often is this stored procedure run?