What is Pragma Autonomous transaction. It's Real time
Example
Answer Posted / mohan reddy
Autonomous Transactions
Autonomous transactions allow you to leave the context of
the calling transaction, perform an independant transaction,
and return to the calling transaction without affecting it's
state. The autonomous transaction has no link to the calling
transaction, so only commited data can be shared by both
transactions.
The following types of PL/SQL blocks can be defined as
autonomous transactions:
• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL
declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.
The easiest way to understand autonomous transactions is to
see them in action. To do this, we create a test table and
populate it with two rows. Notice that the data is not commited.
CREATE TABLE at_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO at_test (id, description) VALUES (1,
'Description for 1');
INSERT INTO at_test (id, description) VALUES (2,
'Description for 2');
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
SQL>
Next, we insert another 8 rows using an anonymous block
declared as an autonomous transaction, which contains a
commit statement.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10 rows in the table. If we now
issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our current session (transaction)
have been rolled back, while the rows inserted by the
autonomous transactions remain. The presence of the PRAGMA
AUTONOMOUS_TRANSACTION compiler directive made the anonymous
block run in its own transaction, so the internal commit
statement did not affect the calling session. As a result
rollback was still able to affect the DML issued by the
current statement.
Is This Answer Correct ? | 20 Yes | 1 No |
Post New Answer View All Answers
tell me the scenarios of the independent in reports
What is the concept of soa governance?
What is instead of trigger in where we will use?
List the various types of value set.
In sequence i want to 11 and 15th values how we will write?
Which oracle apps version you are very confident in?
q)what are the validations in sql*loader
Is soa a part of oracle fusion middleware?
A program must be written which accepts date parameter. The date parameter will accept data in the format DD/MM/YYYY. This data has to be validated and post validation, the following details have to be displayed for this date parameter 1. Input date must be defaulted to sysdate, It can be overridden by any other date. 2. Financial year should be displayed for this date parameter. Eg: If the input date is 30/11/2010, the financial year must be displayed as 1. From Date : 01-Aprr-2010 To Date: 31-Mar-2011 3. Calendar year and Calendar month must also be displayed. 4. The next payment cycle (say for EB), must be displayed based on this date parameter. 5. The previous payment must also be displayed based on this date.
Hi Friends this is preetham, i am searching for job on oracle apps(technical) i put 3 years fake exp, so any one please could you help me for realtime interview questions and 9739782164 this is my no srpsrp777@gmail.Com please guys please provide your no for contact i have a doubts i want to clarify
what are the balancing segments in AR?
what is the difference between request group and request security in oracle apps?
can we use look up type instead of a pofile option?or valueset?is it possible how?
What do you understand by a set of books?
tell my any difficult situtation you solved in your experience in your company?