Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Thumbs up Unanswered: inside savepoint to

    hi,

    I've a package with autonomous transaction.
    From thr im calling different procedures depends on some conditions.
    Inside those procedures im not doing any commit explicitly.
    Here i've declared a savepoint, called dbr. So if any where exception occurs, im rolling back to that savepoint.
    But in the following procedure whn im egtting error in DBR310, only that is rolling back, DBR300, which got performed before tat is not rollingback..
    SO i wanna y it is getting commited even if im rollingback till the savepoint.

    see my package:


    Create or replace package body Pk_Initial_Xml
    as
    Procedure Load_Return(set_trn_id varchar2)

    is
    lc_flag varchar2(1) := 'T';

    e1 exception;

    pragma autonomous_transaction;

    nc number;

    Begin

    dbms_output.put_line('inside PK...'||set_trn_id);

    for i in (select extract(RETURN_XML,'//HEADER/CALLREPORT_ID/text()').getstringval() as cr_id,
    file_id
    from apexmaster.tbsd_xmlrepository
    where set_trn_id = set_trn_id)
    loop

    if ( substr(i.cr_id,1,3) = 'DBR' ) then

    begin

    savepoint dbr;

    if ( i.cr_id = 'DBR300' ) then

    dbms_output.put_line('calling dbr300');

    Pk_Dbr_xml.dbr300(i.file_id, lc_flag );

    dbms_output.put_line(' Flag after insert in 300:'||lc_flag);

    if (lc_flag = 'F' ) then

    raise e1;

    end if;

    elsif ( i.cr_id = 'DBR310' ) then

    dbms_output.put_line('calling dbr310');

    Pk_Dbr_xml.dbr310(i.file_id, lc_flag );

    dbms_output.put_line(' Flag after insert in 310:'||lc_flag);

    if (lc_flag = 'F' ) then

    raise e1;

    end if;

    end if;

    exception

    when e1 then

    rollback to dbr;

    dbms_output.put_line('Rolled back DBR Transaction');

    commit;
    end;

    end if;

    end loop;

    End Load_Return;

    End Pk_Initial_Xml;
    /

    if u need any more clarification in my qstn, plz ask
    for any kind of solution i'll be so grateful...
    so plz...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: inside savepoint to

    Each time round the loop you run either DBR300 or DBR310, not both. And each time round the loop you reset a SAVEPOINT dbr before calling either.

    If either procedure raises an exception, you trap it and rollback to the savepoint, i.e. you undo the changes made in that iteration of the loop only.

    If you added this debug message after the SAVEPOINT DBR statement:
    dbms_output.put_line('setting savepoint dbr');

    then you would see output something like this:

    setting savepoint dbr
    calling dbr300
    Flag after insert in 300:T
    setting savepoint dbr
    calling dbr300
    Flag after insert in 300:T
    setting savepoint dbr
    calling dbr310
    Flag after insert in 310:T
    setting savepoint dbr
    calling dbr300
    Flag after insert in 300:T
    setting savepoint dbr
    calling dbr310
    Flag after insert in 310:F
    Rolled back DBR Transaction

    As you can see, all that will be rolled back is the work done by the last call to dbr310.

    BTW, your procedure needs a COMMIT after the END LOOP, as you cannot exit from an autonomous_transaction procedure without committing or rolling back the changes it made. And you don't want or need the COMMIT in the exception handler.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •