Autonomous transaction in oracle

Autonomous transaction
==========================================
Structure:-
-----------

Declare
PRAGMA AUTONOMOUS_TRANSACTION;
.......
begin
…………….
……………
commit; ---or rollback;
End ;


Autonomous transactions perform an independent transaction, and return to the calling transaction without affecting it. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions.
Oracle originally exposed Autonomous Transactions to developers as a mechanism for committing information on the side whilst not affecting the main event. They wanted to give us a way to record what happened in a job regardless of success or failure of the job itself, and without changing the transaction semantics of the main transaction.


The following types of PL/SQL blocks can be defined as autonomous transactions:
• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.


Example


SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 5 11:19:42 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION dfr_name (passedinid IN NUMBER)
2 RETURN VARCHAR2
3 IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 LAST VARCHAR2 (30);
6 BEGIN
7 SELECT emp.ename
8 INTO LAST
9 FROM emp
10 WHERE empno = passedinid AND ROWNUM = 1;
11
12 UPDATE emp
13 SET sal = sal + 100
14 WHERE empno = passedinid;
15
16 COMMIT;
17 RETURN LAST;
18 END;
19 /

Function created.

SQL> show error
No errors.
SQL>
SQL> select * from emp where empno='7369';

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 900
20


SQL> set linesize 1000
SQL> set pagesiz 100
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 900 20

SQL>
SQL> select dfr_name('7369') from dual;

DFR_NAME('7369')
------------------------------------------------------------------------------------------

SMITH

SQL>
SQL>
SQL> select * from emp where empno='7369';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20

SQL>
SQL>
Autonomous transaction
=====================

Autonomous transaction feature is helpful for
1. A mutating table error in a trigger in which I am querying data from the table from which the trigger is fired.
2. I want to log an error so that when I rollback my business transaction, I do not lose my error log entry.
3. I need to keep an audit trail of every row that is retrieved by a set of queries in my application


A user-defined function cannot execute any DML (insert, update, delete, merge) if it is to be called inside an SQL statement, unless that function is marked as autonomous.
If you mark it as autonomous and also commit/rollback before leaving the function, then the SQL engine will let your function execute one or more DML statement(s).
The reason that this restriction is relaxed is that an autonomous transaction function executes in its own "transaction space" and does not share any locks with the session from which the SQL statement was executed.
This means that the function cannot see any of the uncommitted changes in that (or any other) session. It also means that any DML changes made and committed will not affect the results of the currently executing SQL statement.

If we want to use Autonomous Transactions in a known safe and efficient way, then we can keep just two simple rules in mind:
1) An Autonomous Transaction cannot see uncommitted changes from the parent transaction.
2) Autonomous Transactions should be used as “add on” capabilities which are essentially irrelevant to an otherwise wholly consistent piece of code.

Day wise total SQL Query with opening and colsing balance

Example-1
==============

with t as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
--- select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
--- select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select *
from t
model
dimension by (daynum,cast(trantype as /*VARCHAR2(15)*/chAR(15)) trantype)
measures (amount)
rules upsert all
( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
, amount[any,'Closing balance'] order by daynum = sum(amount)
[daynum <= cv(daynum),trantype != 'Closing balance']
, amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum
, decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/


===========================
EXAMPLE-2
===========================

with tran as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, 1 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, 1 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
), days as
( SELECT LEVEL daynum FROM DUAL CONNECT BY LEVEL <= 4)
SELECT daynum
, DECODE (trantype,'OB','Opening Balance','CB','Closing Balance',trantype) descr
, CASE
/* For opening balance/closing balance
amount will = sum of previous days transactions
*/
WHEN (trantype IN ('OB','CB'))
THEN NVL (SUM(amount) OVER (ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)),0)
ELSE amount
END amount
FROM
(
SELECT d.daynum
, CASE
WHEN ( GROUPING_ID (d.daynum,1)>0
AND GROUPING_ID (d.daynum,2)>0
)
THEN t.trantype
WHEN ( GROUPING_ID (d.daynum,1)>0)
THEN 'OB'
WHEN ( GROUPING_ID (d.daynum,2)>0)
THEN 'CB'
END trantype
, t.amount
FROM tran t
, days d
WHERE d.daynum = t.daynum(+)
/* ROWNUM required to preserve duplicate transactions */
GROUP BY GROUPING SETS ((d.daynum,1),(d.daynum,2),(d.daynum,t.trantype,t.amount,ROWNUM))
)
WHERE trantype IS NOT NULL /* Do not show NULL transaction
for days with no transaction
(outer join)
*/
ORDER BY daynum,DECODE (trantype,'OB',1,'CB',3,2)
/


===========================
EXAMPLE-3
===========================


with t as
2 (
3 select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9 ),
10 data
11 as
12 ( select daynum, r
13 from (select level daynum from dual connect by level <= 4),
14 (select level r from dual connect by level <= 2 )
15 )
16 select daynum,
17 r,
18 ttype,
19 amt,
20 case when ttype in ( 'close', 'open' )
21 then sum(samt) over (order by daynum, r)
22 end theamount
23 from (
24 select data.daynum,
25 data.r,
26 case when (nvl(t.trantype,'CR') = 'CR' and data.r = 1 )
27 then 'open'
28 when (nvl(t.trantype,'DR') = 'DR' and data.r = 2)
29 then 'close'
30 else t.trantype
31 end ttype,
32 case when (t.trantype = 'CR' and r = 2) or (t.trantype = 'DR' and r=1)
33 then t.amount
34 end amt,
35 case when (t.trantype = 'DR' and r = 2)
36 then sum(case when r=1 then t.amount end) over (partition by
data.daynum)
37 else 0
38 end samt
39 from t, data
40 where data.daynum = t.daynum (+)
41 )
42 order by daynum, r, decode( ttype, 'open', 1, 'CR', 2, 'DR', 3, 'close', 4 )
43 /



===========================
EXAMPLE-4
===========================



SQL> with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
2 select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
3 select 1 as daynum, 'CR' as trantype, 400 as amount from dual union all
4 select 1 as daynum, 'DR' as trantype, -500 as amount from dual union all
5 select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6 select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7 select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8 select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
9 select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
10 select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
11 select 5 as daynum, 'CR' as trantype, 200 as amount from dual union all
12 select 5 as daynum, 'DR' as trantype, -500 as amount from dual union all
13 select 6 as daynum, 'DR' as trantype, -200 as amount from dual),
14 dummy as (select 1 id, 'Opening Balance' descr from dual union all
15 select 2 id, 'CR' descr from dual union all
16 select 3 id, 'DR' descr from dual union all
17 select 4 id, 'Closing Balance' descr from dual)
18 select mt.daynum,
19 d.descr,
20 mt.trantype,
21 decode(d.id, 1, 0,
22 2, amount,
23 3, amount,
24 4, sum(decode(d.id, 1, 0, 4, 0, amount)) over (partition by mt.daynum))
amo
unt
25 from (select mt1.*, row_number() over (partition by mt1.daynum order by mt1.trantype) rn
26 from my_tab mt1) mt
27 left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)
28 where case when d.id = 1 and rn = 1 then 1
29 when d.id = 2 and mt.trantype = 'CR' then 1
30 when d.id = 3 and mt.trantype = 'DR' then 1
31 when d.id = 4 and rn = 1 then 1
32 else 0
33 end = 1
34 order by mt.daynum, d.id;



===========================
EXAMPLE-2
===========================

with t as (
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select daynum,
trim(trantype),
amount
from t
model
dimension by (
daynum,
cast(trantype as char(15)) trantype
)
measures (
amount,
daynum + 1 dnum
)
rules upsert all (
dnum[1,'Opening balance'] = max(dnum)[any,any],
amount[for daynum from 1 to dnum[1,'Opening balance'] increment 1,'Opening balance'] = 0,
amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance'],
amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum,
decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/

Firing Order of same type trigger (In oracle 10g)

Firing Order of same type trigger (In oracle 10g)
Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

Although triggers of different types are fired in a specific order, triggers of the same type for the same statement are not guaranteed to fire in any specific order. For example, all BEFORE row triggers for a single UPDATE statement may not always fire in the same order. Design your applications so they do not rely on the firing order of multiple triggers of the same type.
The only thing, can say about the order of triggers firing :
• All of the BEFORE triggers will fire in some order.
• Then all of the BEFORE FOR EACH ROW triggers will fire in some order.
• Then all of the AFTER FOR EACH ROW triggers will fire in some order.
• Then all of the AFTER triggers will fire in some order.

Question: How can I order firing of same type oracle trigger?
Answer:
1. you must consolidate them into a single trigger.
2. All triggers be calls to packaged procedures

Question: Why does Oracle have multiple same-type triggers?
Answer:
The reason goes back to the original implementation of replication in Oracle Database. Materialized view logs used a database trigger to record the data manipulation language (DML) operations that had taken place on a given table.