closing explicit cursors in pl/sql

From: Pascal Glauser <glauser_at_my-deja.com>
Date: Sat, 12 Feb 2000 23:41:14 GMT
Message-ID: <884r2q$pdf$1@nnrp1.deja.com>

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):

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 Sat Feb 12 2000 - 17:41:14 CST