Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


What is Pragma Autonomous transaction. It's Real time
Example

Answers were Sorted based on User's Feedback



What is Pragma Autonomous transaction. It's Real time Example..

Answer / maruthi

Pragma Autonomous Transaction is which is executed in the
execution of another transaction known as parent
transaction which is independent of it
or
the whole transaction splits in to two parts known as
parent& child will be independent of both but have the same
relationship

Is This Answer Correct ?    35 Yes 6 No

What is Pragma Autonomous transaction. It's Real time Example..

Answer / 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

What is Pragma Autonomous transaction. It's Real time Example..

Answer / neelbhotra

in Pragma Autonomous transaction
one(parents) transaction execute another transaction(child)
n the parent transaction suspend temporaly, wait till the
child transaction get complete (commited) n then parent is
resume.

ex.
it is used in interface pogram to hold the error datas in
error table & commited there, and from interface table the
inserted data is rollback.

Is This Answer Correct ?    21 Yes 8 No

What is Pragma Autonomous transaction. It's Real time Example..

Answer / venkatesh

Pragma autonomous transaction is independent transaction
and mainly we can use this transaction in databsae triggers
to write TcL statments like commit,rollback.

Is This Answer Correct ?    3 Yes 7 No

Post New Answer



More Oracle Apps Technical Interview Questions

Form development process?

1 Answers  


Difference between triggers and procedures?

2 Answers  


HOW CAN U KNOW THE STAUS OF PO?

1 Answers   TCS,


In oracle application how do you debug or trace errors?

0 Answers  


what is the difference between oracle 8i and 9i?

1 Answers  






q)what are the validations in sql*loader

0 Answers   Accenture,


while doing the process of conversion if got any data error out what is the next step to reprocess

1 Answers  


in oracle workflow if i want to send a mail to the user after sending if the user didn't approve the mail after 10 mins it is automatimatically approved how we can approach?

2 Answers   Cap Gemini,


what is mean by confine mode and flexi mode?

2 Answers  


How many types of projects are there in project module?

6 Answers   Fujitsu, Tech Mahindra,


Tell me how to find the custom directory in front end?

0 Answers  


How to register a form and stored procedure in oracle apps

2 Answers   Mastek, TCS,






Categories
  • Oracle Apps Technical Interview Questions Oracle Apps Technical (547)
  • Oracle Apps Financial Interview Questions Oracle Apps Financial (791)
  • Oracle Apps Manufacturing Interview Questions Oracle Apps Manufacturing (53)
  • Oracle Apps HRMS Interview Questions Oracle Apps HRMS (169)
  • Oracle Apps CRM Interview Questions Oracle Apps CRM (9)
  • Oracle Apps SCM Interview Questions Oracle Apps SCM (141)
  • Oracle Install Base Interview Questions Oracle Install Base (62)
  • Oracle Service Contracts Interview Questions Oracle Service Contracts (101)
  • Oracle Apps AllOther Interview Questions Oracle Apps AllOther (114)