| autonomous_transaction [message #39998] |
Tue, 03 September 2002 03:35  |
prashant
Messages: 122
Registered: September 2000
|
Senior Member |
|
|
hi all,
I have a doubt regarding autonomous transactions. As far as I understand the concept, we cannot have a 'commit' command in a trigger unless it is declared autonomous. Going by the same logic a procedure cannot also have a commit command unless it is declared autonomous. This is necessary to ensure the transactional nature of Oracle. But i wrote a simple procedure as
SQL> create or replace procedure testing
2 as
3 begin
4 insert into emp(empno,ename,deptno) values (1000,'PRASHANT',10);
5 commit ;
6 end ;
7 /
Procedure created.
contrary to what i thought there was no error inspite of the procedure not being autonomous.
then i just tested a small PL/SQL block as follows
SQL> declare
2 num number;
3 begin
4 testing ;
5 num := 10/0 ;
6 end ;
7 /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5
which is fine, but in this case the entire transaction should be rolled over, but the row has been inserted into the emp table since there was a commit in the procedure. Had there been no commit in the procedure then the row would not have been inserted.
Is this behaviour normal ? or is there something wrong ?
Please help.
Thanks
|
|
|
|
| Re: autonomous_transaction [message #39999 is a reply to message #39998] |
Tue, 03 September 2002 04:46  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
rules are
1. A trigger cannot have a commit.
2. A procedure can have a commit.
3. A trigger cannot have a DDL becuase it issues a
commit internallly.
4. Workaround is use an autonomous transaction.
ie.. create the statement that causes the
commit inside a stored procedure.
Declare it 'Autonmous'
5. Call the that procedure inside the trigger.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
