What if I do not Explicitly close the sys_refcursor in oracle?

Question

What if I do not Explicitly close the sys_refcursor in oracle? will it give a open cursor issue, and results in slow speed of application??

Answer 1

As long as the cursor is open it will count against the limit defined by OPEN_CURSORS, so it could cause issues if you repeatedly open cursors and don't close them.

It will also continue to consume some memory until it is closed. I don't think it's likely to degrade performance significantly though.

Answer 2

It should be discarded / automatically closed once it goes 'out of scope'.

However, what 'out of scope' means can vary depending on the client technology (JDBC, PL/SQL, etc). Within PL/SQL, for instance, it can depend on whether the cursor is held as a package variable or local variable.

As Dave's answer suggests, each open cursor will count against the total limit - eventually you will hit this limit and get an application error.

I would say that best practice is to explicitly close when you are done.