Re: row level lock

Shishir Thu, 07 Mar 2002 22:42:02 -0800

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 .

***********************************************************************************************************

procedure POSTINVOICES ( pInvIDList in varchar2 ) is
InvIdList varchar2(1048);
InvId number;
Invstr varchar2(100);
StartPos Integer := 1;
CommaPos Integer := 0;
ar_date Date;
ap_date Date;
tmp_date Date ;
invNbr varchar2(15);
vunpostbal NUMBER(15,2);
vSusCash char(1);
vCashcheckId number ;
vCCBal number(15,2);
invNbrSeq number ;
VinvId number;

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;
    end Loop;

    commit;

end POSTINVOICES;

******************************************************************

i want as soon as program controls comes in above program before doing those calculation it should lock the all those records of INV table

inv_id of which i am passing in procedure .

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

It is my understanding that Shishir is SELECTing a record, viewing it, making a change to the data and then updating it. Normally, another user can slip in and update the row, so he wants to make sure that no one else does.

For example, in a hotel room reservation system, you would prompt the system for an available room. You would then probably reserve that room for a particular customer. If there was no lock on that room (record) then your guest might discover someone else in the room. Ops.

-----Original Message-----
From: Ora NT DBA [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 07, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: row level lock

Hi Shishir,

This is oracle's default behaviour. As you update , insert or delete a row oracle places a lock at the row level.

John HOugh

[EMAIL PROTECTED] wrote:

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

Re: row level lock

Reply via email to