Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Alternate solution of Autonomous transaction

    I am looking for some alternate solution of Autonomous transaction.

    Below code is using the Autonomous transaction. Can we write the programme in any other way to get the same result without using the Autonomous Transaction?

    create or replace procedure test_sp1
    is
    pragma autonomous transaction;
    begin
    insert into test1 vaues('test1...');
    commit;
    end;
    /

    begin
    insert into test2 values('test2....');
    test_sp1;
    rollback;
    end;
    /

    sql>select * from test1;
    col_1
    -----
    test1...

    sql>select * from test2;
    No rows selected

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure I understood the problem, but - the only thing that comes to my mind at the moment is savepoint:
    Code:
    SQL> select * from test_1 union
      2  select * from test_2;
    
    no rows selected
    
    SQL> insert into test_1 values (100);
    
    1 row created.
    
    SQL> savepoint sp_1;
    
    Savepoint created.
    
    SQL> insert into test_2 values (200);
    
    1 row created.
    
    SQL> select * from test_1 union
      2  select * from test_2;
    
           COL
    ----------
           100
           200
    
    SQL> rollback to sp_1;
    
    Rollback complete.
    
    SQL> select * from test_1 union
      2  select * from test_2;
    
           COL
    ----------
           100
    
    SQL>

Posting Permissions

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