How to commit a inner transaction but not effect the parent transaction ?

[ligang]

1. transaction start(session start)
2. do something
3. start inner transaction
3.1 do something and commit (but don't commit the things did in 2)
4. rollback (rollback 2 )

how to realize it , at time of 3 , is it possible to start another session in the procedure ?

[BJE_DBA]

You want to use an 'autonomous transaction in PL/SQL' it allows for what you describe.


http://www.dbasupport.com/oracle/ora...oustrans.shtml

[ligang]

exactly

[jovery]

http://download-west.oracle.com/docs...08sql.htm#8266

HTH

[tamilselvan]

Here is an example:
09:29:47 H8DEVW3>create table t1 (id number) ;

Table created.

Elapsed: 00:00:00.30
09:29:56 H8DEVW3>insert into t1 values (10) ;

1 row created.

Elapsed: 00:00:00.80
09:30:14 H8DEVW3>declare
09:30:22 2 pragma autonomous_transaction ;
09:30:37 3 begin
09:30:39 4 insert into t1 values (20) ;
09:30:54 5 commit;
09:30:57 6 end;
09:30:59 7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.90
09:31:00 H8DEVW3>rollback ;

Rollback complete.

Elapsed: 00:00:00.40
09:31:17 H8DEVW3>select * from t1 ;

ID
----------
20

Elapsed: 00:00:00.60
09:31:22 H8DEVW3>