Home » SQL & PL/SQL » SQL & PL/SQL » How to create sub independent Transaction?
icon5.gif  How to create sub independent Transaction? [message #211683] Sun, 31 December 2006 06:50 Go to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Given that,
I have One main Transaction (AUTONOMOUS) a, i want to Insert a
sub program with an independent Transaction b.
-----------------A-----------------
.......................
.......................
---------B--------
..................
COMMIT B;
..................
---------END OF B-

........................
----------------END OF A------------
That means b can do "commit" or "rollback" actions etc ,but that would not influence transaction a.
In fact ,in my mind ,b do the log job used to logging something,and a do other actions,to get this aim ,i hope that trasaction b is independent.
how to ?



Re: How to create sub independent Transaction? [message #211685 is a reply to message #211683] Sun, 31 December 2006 12:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where did you get the term autonomous transaction?
You must have misunderstood it, because it exactly is what you describe.
Re: How to create sub independent Transaction? [message #211698 is a reply to message #211685] Mon, 01 January 2007 06:44 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Thank you very much!
If i do not set any pragma hints, any store procedures would start in auto-transaction mode.I can set "commit" or "rollback" keywords anywhere ,but that means,any such actions would affect the whole main transaction.
I have tested several times,there is Not any difference.
My Disappointment! Razz
Maybe ,I disunderstand those concepts.
In fact ,I need guidance!

Re: How to create sub independent Transaction? [message #211703 is a reply to message #211698] Mon, 01 January 2007 09:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a test case: we'll create a table and two procedures: one with, and one without PRAGMA AUTONOMOUS_TRANSACTION:
SQL> create table tab_a (name varchar2(50));

Table created.

SQL> create procedure prc_with_pragma is
  2    pragma autonomous_transaction;
  3  begin
  4    insert into tab_a values ('proc. with PRAGMA');
  5    commit;
  6  end;
  7  /

Procedure created.

SQL> create procedure prc_without_pragma is
  2  begin
  3    insert into tab_a values ('proc. without PRAGMA');
  4    commit;
  5  end;
  6  /

Procedure created.
Now let's see first test case: using a procedure WITHOUT pragma aut. trans.:
- anonymous PL/SQL block inserts 'Testing proc. ...' into the table
- prc_without_pragma inserts 'Proc. without pragma' and COMMITS
- anonymous ROLLBACK does nothing because previous COMMIT, well, commits both inserts.
SQL> -- Example WITHOUT pragma aut. trans.
SQL> begin
  2    insert into tab_a values ('Testing proc. without pragma');
  3    prc_without_pragma;
  4    rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * From tab_a;

NAME
--------------------------------------------------
Testing proc. without pragma
proc. without PRAGMA
Now let's see what AUTONOMOUS_TRANSACTION does:
- first, make room in our table
- anonymous PL/SQL block does the same as previously: inserts 'Testing proc. ...' line
- prc_with_pragma inserts 'Proc. with pragma' line and commits
- anonymous ROLLBACK rolls back first insert ('Testing ...')
All that is left in the table is string inserted in our autonomous transaction - its COMMIT affected only insert made in 'prc_with_pragma' procedure; first insert wasn't commited and thus rollback removed 'Testing ...' record from the table.
SQL> delete from tab_a;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> -- Example WITH pragma aut. trans.
SQL> begin
  2    insert into tab_a values ('Testing proc. with pragma');
  3    prc_with_pragma;
  4    rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from tab_a;

NAME
--------------------------------------------------
proc. with PRAGMA

SQL>

Read more about Pragma Autonomous_transaction on Oracle Technology Network; also, I believe Tom Kyte covered this area with nice examples.
icon14.gif  Re: How to create sub independent Transaction? [message #213785 is a reply to message #211703] Fri, 12 January 2007 05:21 Go to previous message
lzfhope
Messages: 67
Registered: July 2006
Member
Recently ,I am too busy!
Thank Littlefoot ! You do me a great favour! Cool
Tkank all!

Previous Topic: How to find Grants given on Synonyms.
Next Topic: About Trigger
Goto Forum:
  


Current Time: Sun Dec 11 02:31:47 CST 2016

Total time taken to generate the page: 0.08439 seconds