Alter rollback segments

From: Patrik <pama_at_unitema.se>
Date: 11 Feb 2004 03:21:20 -0800
Message-ID: <377af206.0402110321.15245ca@posting.google.com>

Hi,

I want to add "alter rollback segments" and "set transaction use rollback..."
in my otherwise functioning procedure. I have to control which rollbackseg that the proc uses when it updates due to very large transactions.

It seems like one can't do it like one does in a script just add alter rollback segments and so on..how do you do it in a procedure???

I've looked around and found examples using dbms.sql-package - the example wasn't spot on and think I have to rewrite a lot.

Is this the only way using dbms.sql or is there easier ways to implement what I want?

My proc lookes like this:

CREATE OR REPLACE PROCEDURE UTA_UPDATE_MASUPTR     AUTHID
    CURRENT_USER
as

begin

declare

Cursor SupTrInv
  is
Select ST1.JNo, ST1.EntNo,ST1.ValDt
 from MaSupTr MT, SupTr ST1, SupTr ST2
 where MT.Jno = ST1.JNo

 and MT.EntNo = ST1.EntNo
 and MT.AgJno = ST2.JNo
 and MT.AgEntNo = ST2.EntNo

 and ST1.Cr = 1
 and (ST1.VoDt != ST1.ValDt or ST1.ValDt >= ST2.ValDt)  and MT.PayedDt != ST1.ValDt
 and MT.PayedDt = ST1.VoDt
 and ST1.ValDt >= ST2.ValDt;

SupTrInv_Rec SupTrInv%ROWTYPE;

Cursor SupTrPay
  is
Select ST2.JNo, ST2.EntNo,ST2.ValDt
 from MaSupTr MT, SupTr ST2, SupTr ST1
 where MT.AgJno = ST2.JNo

 and MT.AgEntNo = ST2.EntNo
 and MT.Jno = ST1.JNo
 and MT.EntNo = ST1.EntNo

 and ST1.Cr = 1
 and ST2.VoDt != ST2.ValDt
 and MT.PayedDt != ST2.ValDt
 and ST2.ValDt >= ST1.ValDt;

SupTrPay_Rec SupTrPay%ROWTYPE;

rowsfetchedInv number;

rowsfetchedPay number;

BEGIN
/* Script för att uppdatera 'Cr' fältet i SupTr */

Update F0004.SupTr
set Cr = 1
where Am < 0;

commit;

Update F0004.SupTr
set Cr = 0
where Am > 0;

commit;

/* Behandling av fakturarader */

 open SupTrInv;  

 loop

  fetch SupTrInv into SupTrInv_Rec;
  exit when SupTrInv%NOTFOUND;

rowsfetchedInv := SupTrInv%ROWCOUNT;

IF rowsfetchedInv > 0
THEN UPDATE F0004.MaSupTr MT
  set MT.PayedDt = SupTrInv_Rec.ValDt
  where MT.JNo = SupTrInv_Rec.JNo
  and MT.EntNo = SupTrInv_Rec.EntNo;   

  commit;
END IF; end loop;   

close SupTrInv;

/* Behandling av betalrader */

open SupTrPay;

loop

  fetch SupTrPay into SupTrPay_Rec;
  exit when SupTrPay%NOTFOUND;

rowsfetchedPay := SupTrPay%ROWCOUNT;   

IF rowsfetchedPay > 0
THEN   UPDATE F0004.MaSupTr MT

  set MT.PayedDt = SupTrPay_Rec.ValDt
  where MT.AgJNo = SupTrPay_Rec.JNo
  and MT.AgEntNo = SupTrPay_Rec.EntNo;

  commit;

END IF;  end loop;

close SupTrPay;  

END; END; thanks in advance.

/Patrik Received on Wed Feb 11 2004 - 05:21:20 CST