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
Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

Type versus Representation Confusion in SQL This describes how a value might have two or more distinct representations. For example, user-defined type POINT might have a decla

Datatype Conversion At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character stri

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D