Re: Alter rollback segments

Hi,

Have you tried EXECUTE IMMIDIATE (Nativ dynamic sql)

Sameer

"Patrik" <pama_at_unitema.se> wrote in message news:377af206.0402110321.15245ca_at_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 - 07:38:36 CST