Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> closing explicit cursors in pl/sql

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

  1. 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 ; Pascal Glauser

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 12 2000 - 17:41:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US