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
Multiple Assignment- SQL SQL supports multiple assignment to local variables and also applies multiple assignment semantics in SET clauses of UPDATE statements, but does not

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

Autonomous versus Nested Transactions Though an autonomous transaction is started by the other transaction, it is not a nested transaction for the reasons shown below: (i)

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

GOTO Statement The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block.

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

How do I display usernames for students from a student table, assigning each student a username initials001 (initials is the actual student initials), and if the students initials