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
Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

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.

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

Create a procedure named DDPROJ_SP that retrieves project information for a specific project based on a project ID. The procedure should have two parameters: one to accept a projec

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to