Defining autonomous transactions, PL-SQL Programming

Defining Autonomous Transactions

To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark the routine as the autonomous (independent). In this context, the word routine involves:

(i) The top-level anonymous PL/SQL blocks

(ii)The local, packaged functions, and stand-alone & procedures

(iii) The methods of the SQL object type

(iv)A database triggers

You can code the pragma wherever in the declarative part of a routine. But, for the readability, code the pragma at the top of the part. The syntax is as shown:

PRAGMA AUTONOMOUS_TRANSACTION;

In the illustration below, you mark a packaged function as autonomous:

CREATE PACKAGE banking AS

...

FUNCTION balance (acct_id INTEGER) RETURN REAL;

END banking;

CREATE PACKAGE BODY banking AS

...

FUNCTION balance (acct_id INTEGER) RETURN REAL IS

PRAGMA AUTONOMOUS_TRANSACTION;

my_bal REAL;

BEGIN

...

END;

END banking;

Posted Date: 10/5/2012 4:44:21 AM | Location : United States







Related Discussions:- Defining autonomous transactions, Assignment Help, Ask Question on Defining autonomous transactions, Get Answer, Expert's Help, Defining autonomous transactions Discussions

Write discussion on Defining autonomous transactions
Your posts are moderated
Related Questions
Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

What are 3 good practices of modeling and/or implementing data warehouses?

"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Renaming Columns - SQL SQL has no direct counterpart of RENAME. To derive the table on the right in Figure 4.4 from the table on the left, Tutorial D has IS_CALLED RENAME ( St