Defining autonomous transactions, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Defining autonomous transactions

Varrays versus nested tables, Varrays versus Nested Tables The Nested ta...

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

Advantages of exceptions, Advantages of Exceptions Using the exception...

Advantages of Exceptions Using the exceptions for the error handling has many benefits. Without an exception handling, every time you issue a command, you should ensure for th

Table literals - sql, Table Literals - SQL One might expect SQL to sup...

Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

Example of except operator - sql, Example of EXCEPT Operator - SQL Ex...

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

Packages, Packages The package is a schema object which groups logicall...

Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

Anatomy of a command, Anatomy of a Command Figure, showing a simple S...

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Overriding default locking, Overriding Default Locking By default, the...

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you

Comparison operators - sql operators, Comparison Operators Usually, yo...

Comparison Operators Usually, you use the comparison operators in the WHERE clause of a data manipulation statement to form the predicates, that compare one expression to anot

Fetching from a cursor variable, Fetching from a Cursor Variable The F...

Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

Using %type-declarations in sql, Using %TYPE The %TYPE attribute gives th...

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd