Re: row level lock

Hi List !

   i got ur replies then i came to realise that i did not explain my problem in clearer way . Now i am posting the code of my procedure and i hope u people make me understand whether my approach is right ot wrong . There is a table INV ( for invoice ) and from GUI is using different invoice_id (which is PK of INV table) . Then he clicks on post button on form which will call my following procedure by passing inv_id list (as string from my Delphi application) . Inside the procedure i am doing lots of calculations and table will have huge number of record and these processing may take time .

  cursor suspcash(vinvid in number ) is     select distinct cs.cash_check_id
    from cash_susp cs
    where cs.inv_id = vinvid
    union
     select distinct cs.cash_check_id
    from cash_susp cs,inv i
    where cs.quote_id= i.quote_id
    and i.inv_id = vinvid ;
 begin      

    /*lock table inv in
    row share
    mode ;
   Before going down i want to lock all those records invoice id are being passed as argument in my procedure as string */

     if Instr( pInvIDList, ',',-1,1) <> 1 then
          InvIdList := pInvIDList || ',';
    end if;
     

    Loop     

      CommaPos := INSTR( InvIdList, ',', StartPos, 1 );
      if CommaPos = 0 then
        Exit;
      end if;
      Invstr :=substr( InvIdList, StartPos, CommaPos - StartPos ); 
      
      invId := TO_NUMBER(InvStr);
      StartPos := CommaPos + 1;           
      select INV_NBR_SEQ.NEXTVAL into invNbrSeq  from dual;
      
       select         
        AMS_DUE_DT.GET_AP_VCHR_DUE_DT(i.eff_dt,'Q',i.quote_id),
        Decode(i.ar_due_input,'F',AMS_DUE_DT.GET_AR_INV_DUE_DT('I',invId),'T',i.ar_due_dt),
        i.susp_cash, Decode(i.susp_cash ,'F',i.unpost_bal),
        TO_CHAR(i.acct_dt,'YYYY') ||TO_CHAR(i.acct_dt,'MM')||TO_CHAR(invNbrSeq)                     
        
      into   ap_date,ar_date ,vSusCash, vunpostbal,invNbr
      from inv i where i.inv_id = invId;
      if vSusCash ='T' then
         open suspcash(invId);
         loop
           fetch suspcash into vCashcheckId ;
           exit when suspCash%notFound ;
           select (cc.susp_bal - cs.amt) into vCCBal
             from cash_check cc,cash_susp cs
             where cc.cash_check_id=cs.cash_check_id
             and  cs.cash_check_id =vCashcheckId;
           update cash_check 
           set  susp_bal= vCCBal
           where cash_check_id = vCashcheckId;   
         end loop; 
         close suspcash;         
      end if ;  
      
      update INV   set  
        inv_nbr   = invNbr,
        post_dt   = sysdate,
        ar_due_dt = ar_date,
        mkt_ap_due_dt = ap_date,
        unpost_bal  = vunpostbal,
        ar_due_input = 'T'               
      where inv_id = invId;               

    commit;     

 end POSTINVOICES;

seeking for help .....
Shishir Kumar Mishra
Agni Software (P) Ltd.,
Bangalore-560055, India
www.agnisoft.com

• Original Message ----- From: Grabowy, Chris To: Multiple recipients of list ORACLE-L Sent: Thursday, March 07, 2002 11:08 PM Subject: RE: row level lock

      HI  list!
        I want to lock a table on row level ( not table level) . how do we pass the pass that value in syntax which will lock only those rows. plz make me understand by giving an example.

      thanx in advance..
      Shishir Kumar Mishra
      Agni Software (P) Ltd.,
      Bangalore-560055, India
      www.agnisoft.com 

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Shishir
  INET: shishir_at_agnisoft.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------