Pragma Autonomous Transaction

Definition: An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction.

Scenario : You can use autonomous transaction in your report for writing error messages in your database tables.

Example

CREATE TABLE at_test (

      id               NUMBER            NOT NULL,

     description  VARCHAR2(50)  NOT NULL

   );

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');

INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

2 rows selected.

Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  FOR i IN 3 .. 10 LOOP

    INSERT INTO at_test (id, description)

    VALUES (i, 'Description for ' || i);

  END LOOP;

  COMMIT;

END;

/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

10 rows selected.

As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.

ROLLBACK;

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

8 rows selected.

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain.  The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session.

Bulk Collect

#---------------------
# HOW TO BULK BINDING
#---------------------

(i)  For Input collections-- use the FORALL statement
(ii) For Output collections--use BULK COLLECT clause


#---------------------------------------------------------------------------------
# OUTPUT COLLECTIONS - BULK COLLECT
#---------------------------------------------------------------------------------

--The bulk collect option instructs the SQL engine to bulk bind the output collections
   before returning them to the PL/SQL engine.
--This allows us to load data dynamically into collections at one shot for further processing.
--Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements
Syntax:

   ... BULK COLLECT INTO collection_name[, collection_name] ....


#-------------
# Examples 1
#-------------

DECLARE
TYPE cust_tab IS TABLE OF customer.customer_account_id%TYPE
INDEX BY BINARY_INTEGER;
Custs cust_tab;
BEGIN
SELECT customer_account_id
 BULK COLLECT INTO custs
FROM customer
WHERE effective_date BETWEEN
TO_DATE(‘01-Jan-2004’,’DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;;
/

#-------------
# Examples 2
#-------------


DECLARE
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   TYPE SalTab IS TABLE OF emp.sal%TYPE;
   names NameTab;
   sals SalTab;
   CURSOR c1 IS SELECT ename, sal FROM emp;
BEGIN
   OPEN c1;


   FETCH c1 BULK COLLECT INTO names, sals;
   FOR i IN names.FIRST..names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));
   END LOOP;
   CLOSE c1;
END;
/

#-----------------------------------
# Bulk used with Select into clause
#-----------------------------------


declare
 type emp_details is table of emp.ename%type index by binary_integer;
V emp_details;
begin
select ename bulk collect into V
from emp;
for i in V.first .. V.last
 loop
    dbms_output.put_line(V(i));
end loop;
end;

#-------------
# Bulk used in Cursors
#-------------


declare
cursor cf is select * from emp;
type emp_tab is table of emp%rowtype index by binary_integer;
V emp_tab;
v_limit natural := 10;
begin
 open cf;
fetch cf bulk collect into V limit v_limit;
for j in V.first .. V.last
 loop
        dbms_output.put_line(V(j).ename);
end loop;
end;

#-------------
# Bulk Insert
#-------------



Create table BI (a number check(a between 5 and 45));

declare
type no_list is table of number index by binary_integer;
v no_list;
bulk_errors exception;
 pragma exception_init ( bulk_errors, -24381 );
begin
for i in 5..50
loop
   v(i) := i;
end loop;
forall j in V.first .. V.last  save exceptions
   insert into bi values (V(j));
   dbms_output.put_line('Records inserted');
exception
 when bulk_errors then
 for j in 1..sql%bulk_exceptions.count
  loop
    Dbms_Output.Put_Line ( 'Error from element #' ||
      To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
      Sqlerrm(-sql%bulk_exceptions(j).error_code) );
  end loop;
end;

#-------------
# Bulk Delete
#-------------


declare
 type emp_tab is table of emp%rowtype index by binary_integer;
 V emp_tab;
begin
delete from emp
returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;
for i in V.first .. v.last
loop
   dbms_output.put_line(V(i).ename);
end loop;
end;

Conditions

Decision-making structures require that the programmer specify one or more conditions to be evaluated or tested by the program, along with a statement or statements to be executed if the condition is determined to be true, and optionally, other statements to be executed if the condition is determined to be false.

Statement	Description
IF - THEN statement	The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is true, the statements get executed and if the condition is false or NULL then the IF statement does nothing.
IF-THEN-ELSE statement	IF statement adds the keyword ELSE followed by an alternative sequence of statement. If the condition is false or NULL , then only the alternative sequence of statements get executed. It ensures that either of the sequence of statements is executed.
IF-THEN-ELSIF statement	It allows you to choose between several alternatives.
Case statement	Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.
Searched CASE statement	The searched CASE statement has no selector, and it's WHEN clauses contain search conditions that yield Boolean values.
nested IF-THEN-ELSE	You can use one IF-THEN or IF-THEN-ELSIFstatement inside another IF-THEN or IF-THEN-ELSIF statement(s).

Example 1:

   a   NUMBER (2) := 10;

BEGIN

   a := 10;                  -- check the boolean condition using if statement

   IF (a < 20)

   THEN                       -- if condition is true then print the following

      DBMS_OUTPUT.put_line ('a is less than 20 ');

   END IF;

   DBMS_OUTPUT.put_line ('value of a is : ' || a);

END;

Example 2:

DECLARE

a number(3) := 100;

BEGIN

-- check the boolean condition using if statement

IF( a < 20 ) THEN

-- if condition is true then print the following

dbms_output.put_line('a is less than 20 ' );

ELSE

dbms_output.put_line('a is not less than 20 ' );

END IF;

dbms_output.put_line('value of a is : ' || a);

END;

Example 3:

DECLARE

   a   NUMBER (3) := 100;

BEGIN

   IF (a = 10)

   THEN

      DBMS_OUTPUT.put_line ('Value of a is 10');

   ELSIF (a = 20)

   THEN

      DBMS_OUTPUT.put_line ('Value of a is 20');

   ELSIF (a = 30)

   THEN

      DBMS_OUTPUT.put_line ('Value of a is 30');

   ELSE

      DBMS_OUTPUT.put_line ('None of the values is matching');

   END IF;

    DBMS_OUTPUT.put_line ('Exact value of a is: ' || a);

END;


Example 4:

DECLARE

   a   NUMBER (3) := 100;

   b   NUMBER (3) := 200;

BEGIN

   -- check the boolean condition

   IF (a = 100)

   THEN

      -- if condition is true then check the following

      IF (b = 200)

      THEN

         -- if condition is true then print the following

         DBMS_OUTPUT.put_line ('Value of a is 100 and b is 200');

      END IF;

   END IF;

   DBMS_OUTPUT.put_line ('Exact value of a is : ' || a);

   DBMS_OUTPUT.put_line ('Exact value of b is : ' || b);

END;

GOTO statement: A GOTO statement in PL/SQL programming language provides an unconditional jump from the GOTO to a labeled statement in the same subprogram.

DECLARE

   a   NUMBER (2) := 10;

BEGIN

  <<loopstart>>

   -- while loop execution

   WHILE a < 20

   LOOP

      DBMS_OUTPUT.put_line ('value of a: ' || a);

      a := a + 1;

      IF a = 15

      THEN

         a := a + 1;

         GOTO loopstart;

      END IF;

   END LOOP;

END;