Re: closing explicit cursors in pl/sql

Hi Pascal,

Method c) is the best one IMHO with regards to safety and modularity because you use local cleanup (you cleanup where shit happens). It makes sure there will not be any memory leaks from your cursors.

We do all in c). We use a small preprocessor which creates the complete procedure scripts out of templates and inserts the according exception body.

Martin

Pascal Glauser wrote:
>
> I wonder if explicit cursors opened in pl/sql-blocks are implicitly
> closed when the block terminates (the cursor-variable stops beeing
> visible).
>
> What is the best practice within the folloging statements to close the
> cursor (free the resources) for sure (I ignore the question wether its a
> good practice to have more than one exit; often it is very convienient,
> as long as the code is clear enough):
>
> a) exit without closing the cursor
> DECLARE
> cursor curEmp is
> select * from emp ;
> recEmp curEmp%rowtype ;
> BEGIN
> open curEmp ;
> fetch curEmp into recEmp ;
> if curEmp%notfound then
> return ; -- nothing to do
> end ;
>
> insert into ... ; -- something meaningful
>
> loop while curEmp%found
> .. do something meaningful
> fetch curEmp into recEmp ;
> end loop ;
> close curEmp ;
> END ;
>
> b) closing the cursor on any account, but what if the block terminates
> with an exception ?
> DECLARE
> cursor curEmp is
> select * from emp ;
> recEmp curEmp%rowtype ;
> BEGIN
> open curEmp ;
> fetch curEmp into recEmp ;
> if curEmp%notfound then
> close curEmp ; -- ***************
> return ; -- nothing to do
> end ;
>
> insert into ... ; -- something meaningful
>
> loop while curEmp%found
> .. do something meaningful
> fetch curEmp into recEmp ;
> end loop ;
> close curEmp ;
> END ;
>
> c) with a resource-protection-block (who does it really like this?)
> DECLARE
> cursor curEmp is
> select * from emp ;
> recEmp curEmp%rowtype ;
> BEGIN
> BEGIN
> open curEmp ;
> fetch curEmp into recEmp ;
> if curEmp%notfound then
> close curEmp ;
> return ;
> end if ;
>
> insert into ... ; -- something meaningful
>
> loop while curEmp%found
> .. do something meaningful
> fetch curEmp into recEmp ;
> end loop ;
> -- the normal termination: free the resource
> close curEmp ;
> EXCEPTION
> -- if the block raises an exception: free open resources and
> -- reraise the exception
> WHEN OTHERS THEN
> if curEmp.isopen then
> close curEmp ;
> end if ;
> RAISE ;
> END ;
> END ;
>
> Pascal Glauser
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Feb 13 2000 - 15:10:15 CST