Re: closing explicit cursors in pl/sql

Hi.

 Explicit cursors are NOT closed by Oracle anf after some time you  will get an error "TOO MANY CURSORS" ( I don't remember the correct  words ) so you better close everything ( even in exception ).

  Use cur_name%ISOPEN to check in EXCEPTION if cursor still open   and close it:

  EXCEPTION
   WHEN ... THEN

     IF emp_crs%ISOPEN THEN
        CLOSE emp_crs;
     END IF;

  HTH. Michael.

In article <884r2q$pdf$1_at_nnrp1.deja.com>,   Pascal Glauser <glauser_at_my-deja.com> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Feb 13 2000 - 15:08:33 CST