Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mike.

Asked: December 12, 2001 - 6:23 pm UTC

Last updated: June 03, 2008 - 10:44 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Spec:
Need a small lookup table with a b_default column and a trigger that allows 0 or 1 rows in the table to be set to "true" (= -1 for this question). When a new row is inserted where b_default = -1 then the trigger should remove update any existant row where b_default = -1. In short there should only exist one or zero rows where b_default is true. If a new row is inserted where b_default is true then reset all other rows to b_default false.

Descr:
I have coded this with an before insert trigger with an autonomous transaction (per your great book). The only problem is that if I insert a row but rollback the insert before commiting, the autonomous transaction has already reset all of the b_default values, in effect loosing my previous b_default. Note from the example below that I have a similar problem with updates as inserts.

Question:
Is there a better way to code this?

SQL> CREATE TABLE x_short
  2      (pk                             VARCHAR2(32) NOT NULL primary key,
  3      val                            NUMBER,
  4      b_default                      NUMBER(1))
  5  /

Table created.
SQL> CREATE OR REPLACE TRIGGER tbi_short
  2  BEFORE INSERT
  3  ON x_short
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  declare
  7    pragma autonomous_transaction;
  8  begin
  9    -- if creating a new default then set all other default(s) false
 10    if (:new.b_default = -1) then
 11      update x_short set b_default = 0 where b_default = -1;
 12      commit;
 13    end if;
 14
 15    -- generate pk
 16    if :new.pk is null then
 17      execute immediate 'select sys_op_guid() from dual ' into :new.pk;
 18    end if;
 19
 20  End;
 21  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER tbu_short
  2  BEFORE UPDATE
  3  ON x_short
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  declare
  7    pragma autonomous_transaction;
  8  begin
  9
 10    if (:new.b_default = -1) then
 11      update x_short set b_default = 0 where b_default = -1;
 12      commit;
 13    end if;
 14
 15  end;
 16  /

Trigger created.

SQL> insert into x_short (val, b_default) values (1, 0);

1 row created.

SQL> insert into x_short (val, b_default) values (2, -1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from x_short;

PK                                      VAL  B_DEFAULT
-------------------------------- ---------- ----------
94F1CF888CEC526BE034080020C902BB          1          0
94F1CF888CED526BE034080020C902BB          2         -1

SQL> insert into x_short (val, b_default) values (3, -1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from x_short;

PK                                      VAL  B_DEFAULT
-------------------------------- ---------- ----------
94F1CF888CEC526BE034080020C902BB          1          0
94F1CF888CED526BE034080020C902BB          2          0
94F1CF888CEE526BE034080020C902BB          3         -1

SQL> insert into x_short (val, b_default) values (4, -1);

1 row created.

SQL> rollback;

Rollback complete.

SQL> select * from x_short;

PK                                      VAL  B_DEFAULT
-------------------------------- ---------- ----------
94F1CF888CEC526BE034080020C902BB          1          0
94F1CF888CED526BE034080020C902BB          2          0
94F1CF888CEE526BE034080020C902BB          3          0

SQL> truncate table x_short;

Table truncated.
-- similar problem with inserts.

SQL> insert into x_short (val, b_default) values (1, -1);
1 row created.

SQL> insert into x_short (val, b_default) values (2, 0);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from x_short;
PK                                      VAL  B_DEFAULT
-------------------------------- ---------- ----------
94F1CF888CEA526BE034080020C902BB          1         -1
94F1CF888CEB526BE034080020C902BB          2          0

SQL> update x_short set b_default = -1 where val = 2;
1 row updated.

SQL> commit;

Commit complete.

SQL> select * from x_short;

PK                                      VAL  B_DEFAULT
-------------------------------- ---------- ----------
94F1CF888CEA526BE034080020C902BB          1          0
94F1CF888CEB526BE034080020C902BB          2         -1

SQL> update x_short set b_default = -1 where val = 1;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> select * from x_short;

PK                                      VAL  B_DEFAULT
-------------------------------- ---------- ----------
94F1CF888CEA526BE034080020C902BB          1          0
94F1CF888CEB526BE034080020C902BB          2          0





and Tom said...

Well, I'm sorry that you inferred from my book that this was a "good practice". I gave an example (auditing related) but also said "this does not mean that we will use autonomous transactions to 'fix' mutating table error any time we hit them-- they must be used cautiously and with the knowledge of how the transaction is really progressing. In the caveats section later, we will explore the reasons for this. The mutating table error is actually for your protection....'

This is a really super tricky question here! What you need to do is avoid the mutating table, see:

</code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>

say you have a table T ( id int primary key, ...., b_default int );

you could:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_bi before insert or update on t
2 begin
3 if ( not state_pkg.inTrig )
4 then
5 state_pkg.modified := state_pkg.empty;
6 end if;
7 end;
8 /
Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_bifer before insert or update on t
2 for each row
3 begin
4 if ( not state_pkg.inTrig and :new.b_default = -1 )
5 then
6 state_pkg.modified(state_pkg.modified.count+1) := :new.id;
7 end if;
8 end;
9 /
Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_ai after insert or update on t
2 begin
3 if ( not state_pkg.inTrig )
4 then
5 begin
6 state_pkg.inTrig := TRUE;
7 for i in 1 .. state_pkg.modified.count
8 loop
9 update t set b_default = 0
10 where b_default = -1;
11
12 update t set b_default = -1
13 where id = state_pkg.modified(i);
14 end loop;
15 state_pkg.inTrig := FALSE;
16 exception
17 when others then
18 state_pkg.inTrig := FALSE;
19 raise;
20 end;
21 end if;
22 end;
23 /

Trigger created.


But frankly, I would rather just create a stored procedure to do this:

procedure update_b_default( p_id in int )
as
begin
update t set b_default = 0 where b_default = -1;
update t set b_default = -1 where id = p_id;
end;
/


thats much easier....

Rating

  (48 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

just one more thing ...

Mike Wilson, December 13, 2001 - 3:44 pm UTC

Simple Follow Up:
Where do you define 'state_pkg.inTrig'? Is it simply an integer in a package somewhere? I don't see it in your response.

Thanks for the code but as I have learned by now the complicated solution is rarely the one to use. I agree with you and would probably prefer the procedural version.

I was also thinking about simply submitting a dbms_job to update the recordset in the trigger and letting the commit from the transaction commit the job also (I think you mention this in your, and I can't say this enough, *excellent* book). Thanks for your time.

Tom Kyte
December 13, 2001 - 5:04 pm UTC

doh -- sorry about that -- missed that part of the example on the cut and paste.

It was:

create or replace package state_pkg
as
type array is table of int index by binary_integer;

modified array;
empty array;
inTrig boolean default FALSE;
end;
/

thanks for the feedback on the book -- appreciate it.

Slightly different variation

Lisa, July 08, 2002 - 11:10 am UTC

This is very similar to a problem that I am facing, although mine is slightly more complex...
I have a table that specifies many different labels for an organization entity. One and only one label must be the default at any given time for a particular org.
E.g. table t has columns org, label, org_default.
If when inserting or updating there is not a current default label, the row on which I am operating must become the default. Alternately, if my :new.org_default='Y' for the label, I need to update the previous default to 'N'. The application code will never allow a label to change explicity from 'Y' to 'N', the user will specify another label as the default and the trigger must reset the old default.
I feel that the answer to my question is here, can you give a an additional push?

Thanks,

Lisa

Tom Kyte
July 08, 2002 - 4:26 pm UTC

This is BEGGING for a stored procedure -- rather then the application doing "insert into", the application does "begin do_insert( .... ); end;"

It'll be INCREDIBLY easy and straight forward and understandable (as opposed to the triggers).

sounding redundant here -- as that is the same advice I gave to the other person ;)



You can do it with triggers but it is ugly and hard to understand.  Here is one approach (requires the table have a primary key!)

ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence t_seq;
Sequence created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table T
  2  ( id number primary key,
  3    org varchar2(5),
  4    label varchar2(5),
  5    org_default varchar2(1) default 'N' );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create unique index t_idx on t(decode(org_default,'Y',org,null));

Index created.

<b>that index makes SURE at most one "org_default=Y" value per org.  Best to have that *just in case* something is wrong in the logic below -- it'll prevent logically corrupt data</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package state_pkg
  2  as
  3      type array is table of number index by binary_integer;
  4  
  5      modified         array;
  6      check_these      array;
  7      empty            array;
  8      inTrig           boolean default false;
  9  end;
 10  /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_bi before insert or update on t
  2  begin
  3      if ( not state_pkg.inTrig )
  4      then
  5          state_pkg.modified := state_pkg.empty;
  6          state_pkg.check_these    := state_pkg.empty;
  7      end if;
  8  end;
  9  /

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_bifer before insert or update on t
  2  for each row
  3  begin
  4      if ( not state_pkg.inTrig )
  5      then
  6          if ( :new.org_default = 'Y' )
  7          then
  8              state_pkg.modified(state_pkg.modified.count+1) := :new.id;
  9              :new.org_default := 'N';
 10          else
 11              state_pkg.check_these(state_pkg.check_these.count+1) := :new.id;
 12          end if;
 13      end if;
 14  end;
 15  /

Trigger created.

<b>Keeping the ids of records we need to set to "Y" in modified -- note how we undo the org_default for a moment here, so as to not violate our unique index above!

check_these are id's that need to be checked to see if they should default to 'Y' -- we need to check the rows that are just inserted and inserted with 'N'
</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_ai after insert or update on t
  2  begin
  3      if ( not state_pkg.inTrig )
  4      then
  5      begin
  6          state_pkg.inTrig := TRUE;
  7          for i in 1 .. state_pkg.modified.count
  8          loop
  9              update t set org_default = decode( id, state_pkg.modified(i), 'Y', 'N' )
 10               where org = ( select org from t where id = state_pkg.modified(i) );
 11          end loop;

<b>for the ones that were inserted with Y, update all of the other org records to 'N', setting just this one to 'Y'</b>


 12          for i in 1 .. state_pkg.check_these.count
 13          loop
 14              update t set org_default = 'Y'
 15               where id = state_pkg.check_these(i)
 16                 and not exists ( select null
 17                                    from t
 18                                   where org = ( select org from t where id = state_pkg.check_these(i) )
 19                                     and org_default = 'Y' );
 20          end loop;
 21          state_pkg.inTrig := FALSE;

<b>for the ones we need to check, set them to 'Y' IF no other row exists in that org with 'Y" already.  

This does have a race condition in it whereby if you have two sessions that both insert the "first" row at the same time -- since they cannot "see" eachothers inserts -- the unique index t_idx above will serialize them.  ONLY ONE session will succeed, the other will get "dup_val_on_index" -- which I suppose you could "ignore" (since the goal of the first row in being 'Y' is achieved) if you wanted.</b>

 22      exception
 23          when others then
 24              state_pkg.inTrig := FALSE;
 25              raise;
 26      end;
 27      end if;
 28  end;
 29  /

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label ) values ( t_seq.nextval, 'a', 'aa' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    Y

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label ) values ( t_seq.nextval, 'a', 'bb' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    Y
         2 a     bb    N

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label ) values ( t_seq.nextval, 'a', 'cc' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    Y
         2 a     bb    N
         3 a     cc    N

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label, org_default ) values ( t_seq.nextval, 'a', 'dd', 'Y' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    N
         2 a     bb    N
         3 a     cc    N
         4 a     dd    Y

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label ) values ( t_seq.nextval, 'b', 'aa' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    N
         2 a     bb    N
         3 a     cc    N
         4 a     dd    Y
         5 b     aa    Y

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label ) values ( t_seq.nextval, 'b', 'bb' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    N
         2 a     bb    N
         3 a     cc    N
         4 a     dd    Y
         5 b     aa    Y
         6 b     bb    N

6 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label ) values ( t_seq.nextval, 'b', 'cc' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    N
         2 a     bb    N
         3 a     cc    N
         4 a     dd    Y
         5 b     aa    Y
         6 b     bb    N
         7 b     cc    N

7 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t ( id, org, label, org_default ) values ( t_seq.nextval, 'b', 'dd', 'Y' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

        ID ORG   LABEL O
---------- ----- ----- -
         1 a     aa    N
         2 a     bb    N
         3 a     cc    N
         4 a     dd    Y
         5 b     aa    N
         6 b     bb    N
         7 b     cc    N
         8 b     dd    Y

8 rows selected.
 

Why a trigger with autonomous_transaction does not work for multiple updates in one statement?

A Reader, September 12, 2002 - 3:44 pm UTC

We are trying to update another table based on the update result of a table and have some problems doing it by using a trigger with autonomous_transaction.

create table t1 (Key_value number, sub_key number,CHANGE_VALUE number);
insert into t1 values(1,1,1);
insert into t1 values(1,2,1);
insert into t1 values(1,3,0);
insert into t1 values(2,1,1);
insert into t1 values(2,2,0);
insert into t1 values(2,3,0);
commit;

create table t2(key_value number, monitor_value number);
insert into t2 values(1,0);
insert into t2 values(2,0);
commit;

create table tab_temp (Key_value number, sub_key number,CHANGE_VALUE number);


CREATE OR REPLACE TRIGGER TRg_check
BEFORE UPDATE ON t1
FOR EACH ROW
DECLARE Avg_change NUMBER:=0;
pragma autonomous_transaction;
BEGIN
IF :NEW.CHANGE_VALUE=0 THEN
UPDATE t2 SET monitor_value = 0 WHERE Key_value = :NEW.Key_value;
ELSE
Insert into tab_temp select Key_value,sub_key,CHANGE_VALUE from t1
where Key_value=:NEW.Key_value;
SELECT AVG(CHANGE_VALUE) INTO Avg_change FROM tab_temp;
IF (Avg_change = 1) THEN
UPDATE t2 SET monitor_value = 1 WHERE Key_value = :NEW.Key_value;
ELSE
UPDATE t2 SET monitor_value = 0 WHERE Key_value = :NEW.Key_value;
END IF;
END IF;
commit;
END;

/

The purpose of using the trigger is to determine the monitor_value in t2 based on the change_value in t1 (including the newly inserted value). If the change_value in t1 for a key_value is 1 for all it's sub_key values, then the monitor_value in t2 is set to 1; otherwise, if any of the change_value for a key_value is 0, the monitor_value is set to 0.
The trigger works fine when we update one change_value each time. However, when we update two or more change_values, the trigger works incorrectly. Say, "update t1 where key_value=1 and sub_key in (2,3);"
When we check the tab_temp (which should be global temporary table), we get more than what we want and the results are wrong. Can you please help.

Qestion:
1. Is that the right way to do what we want by using this kind of trigger? We tried to remove the "pragma autonomous_transaction" and "commit", we endup with mutating the table.
2. Why the trigger works for one update but not for multiple row updates in one statement?

Thank you very much for your help.


Tom Kyte
September 12, 2002 - 4:26 pm UTC

bad use of autonomous transaction. You do understand that your updates to T2 might succeed whilst the insert into T1 failed and is rolled back (eg: your data integrity is totally OUT THE WINDOW, gone, bzzzt - game over player one)

Anyway, you need to defer the reading of T1 until AFTER the insert is processed.

see
</code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>
for some techniques

Use an Instead of Trigger can do it.

A Reader, September 13, 2002 - 2:20 pm UTC

Thank you Tom. I actually got the idea from your book (one on one p665). I think I misunderstood your meaning in the book. Now I can resolve it by creating a view (v1)which is the same as t1 and creating an instead of trigger without using the temporary table. It seems to be ok now. The only thing is that all the developers and users need to remember just update v1 not t1.
However, I still don't understand why a trigger works fine for one row updating but not for multiple updating in one statement. In the insert statement, I even put an extra condition in where clause:
Insert into tab_temp select Key_value,sub_key,CHANGE_VALUE from t1 where Key_value=:NEW.Key_value and sub_key<>:new.sub_key;
Thank you again for your help.


Tom Kyte
September 14, 2002 - 2:14 pm UTC

rename t1 to t1_table;
create or replace view t1 as .....;

grant on t1;
revoke on t1_table;

and no one will be the wiser.

As for the one row vs multiple rows: we KNOW in a single row insert, that you can get a consistent, meaningful, "data integrity is preserved" look at that table in the trigger. In a multi-row insert -- when you query the table in the trigger that is being inserted into -- you'll see it in a fuzzy state -- some of the rows will be inserted, some won't. And if you execute the SAME exact insert two times in a row like this:

insert into t select * from another_table;
-- take a look at T
rollback;
alter session set sort_area_size = 10000000;
insert into t select * from another_table;
-- take a look at T

You could end up with two different results if we let you peek at the table to make decisions (eg: the order of the rows would be/could be totally different, looking at that mutating table would lead to strange and really incredibly hard to reproduce issues)

We only distinguish between:

insert into t values ( ... ) <<<<---- single row insert
insert into t SELECT <<<<<---- multi-row insert, regardless of how many rows
are actually returned.

Getting multiple rows inside statement trigger

Sandra, December 12, 2002 - 1:42 pm UTC

Please Tom,

In a for each row trigger, I can use :new to get the new (individual) line columns. Suppose I have a statement trigger (which fires once no matter the number of rows).
I cannot use :new, because it only works in for each row triggers (right?).

Question: How can I get this SET of lines that have just been inserted using a statement trigger ?

Thank you.



Tom Kyte
December 12, 2002 - 2:09 pm UTC

You cannot. It does not work that way.

see the above link about Mutate.html -- it shows how you could capture the rowids/primary keys of the newly inserted rows.

How find out the last inserted row

arangaperumal, April 20, 2004 - 3:31 am UTC

Hi TOM,

We want to know "How to get LAST INSERTED/UPDATED ROW ". we are denied to add an additional col for Even_date.
How do i found this info without this col.
thank you



Tom Kyte
April 20, 2004 - 8:39 am UTC

you do not find that information without a column like that.

How to handle this.

arangaperumal, April 20, 2004 - 8:46 am UTC

Hi thank you for your reply.
If you are asked to do, what will be you idea?.
why oracle,till now, doesn't think about function for this?


Tom Kyte
April 20, 2004 - 9:00 am UTC

who is to say we haven't thought about it?

it is just something "not normal".

of what use is the "most recent row" -- in general -- nothing. It must have some application specific meaning -- meaning, it is part of your design.

You may as well ask "why doesn't oracle have a function to return the top paid employee". you have to design a structure to answer your questions.

so, why do you need the "last inserted row" (which immediately upon retrieval is probably not the last inserted row anymore - someone else will be inserting!)

Back updation problem

Nayan J. Sarma, July 13, 2004 - 4:39 am UTC

Dear Tom,
I have a similar, a bit tricky Mutating table problem.
SUPPOSE I have a PO_ITEM table with the following columns:

PO_CODE          VARCHAR2(12)
PARENT_PO_CODE     VARCHAR2(12)
ITEM_CODE     VARCHAR2(12)
PO_QTY        NUMBER(10)
BK_QTY        NUMBER(10)

I can create a PO directly, (i.e. PARENT_PO_CODE WILL BE NULL) or i can create a PO from an existing one (here the 

PARENT_PO_CODE should contain the PO_CODE of the transaction from which the records are copied). Whenever a PO is 

created from an existing PO (we call it 'PULLING' here), the BK_QTY of the parent PO should be updated with the 
PO_QTY of the child trasaction. An example will clarify this:

-----------------
CREATE TABLE PO_ITEM
(
PO_CODE          VARCHAR2(12),    -- PO CODE
PARENT_PO_CODE     VARCHAR2(12),    -- PO CODE OF THE PARENT PO, IF THE CURRENT PO IS 'PULLED' FROM ANOTHER PO
ITEM_CODE     VARCHAR2(12),   -- ITEM CODE
PO_QTY        NUMBER(10),     -- ITEM QUANTITY
BK_QTY        NUMBER(10)    -- BACKUPDATED QUANTITY, IF ANY PO IS GENERATED 
-- FROM THIS ONE, THEN THIS COLUMN IS     

                -- UPDATED BY THE CHILD PO. PENDING QTY = PO_QTY - BK_QTY
);

CREATE OR REPLACE TRIGGER TRG_PO_ITEM_001
          BEFORE INSERT OR UPDATE OR DELETE  
          ON PO_ITEM
          REFERENCING NEW AS NEW OLD AS OLD
          FOR EACH ROW

DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    IF INSERTING OR UPDATING THEN
        UPDATE PO_ITEM 
        SET BK_QTY = NVL(BK_QTY,0) + :NEW.PO_QTY
        WHERE
        PO_CODE    = :NEW.PARENT_PO_CODE;


    END IF;

    IF UPDATING OR DELETING THEN
        UPDATE PO_ITEM 
        SET BK_QTY = NVL(BK_QTY,0) - :OLD.PO_QTY
        WHERE
        PO_CODE    = :OLD.PARENT_PO_CODE;    

    END IF;

    COMMIT;
END;
/


SQL> INSERT INTO PO_ITEM (PO_CODE,PARENT_PO_CODE,ITEM_CODE,PO_QTY,BK_QTY)
  2  VALUES('A1', NULL, 'IT001', 100, 0);

1 row created.


SQL> INSERT INTO PO_ITEM (PO_CODE,PARENT_PO_CODE,ITEM_CODE,PO_QTY,BK_QTY)
  2  VALUES('A2', NULL, 'IT001', 50, 0);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PO_ITEM;

PO_CODE      PARENT_PO_CO ITEM_CODE       PO_QTY    BK_QTY
------------ ------------ ------------ --------- ---------
A2                        IT001               50         0
A1                        IT001              100         0

SQL> INSERT INTO PO_ITEM (PO_CODE,PARENT_PO_CODE,ITEM_CODE,PO_QTY,BK_QTY)
  2  SELECT 'A3', PO_CODE, ITEM_CODE, 60, 0 FROM PO_ITEM WHERE PO_CODE = 'A1';

1 row created.

SQL> SELECT * FROM PO_ITEM;

PO_CODE      PARENT_PO_CO ITEM_CODE       PO_QTY    BK_QTY
------------ ------------ ------------ --------- ---------
A2                        IT001               50         0
A3           A1           IT001               60         0
A1                        IT001              100        60

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO PO_ITEM (PO_CODE,PARENT_PO_CODE,ITEM_CODE,PO_QTY,BK_QTY)
  2  SELECT 'A4', PO_CODE, ITEM_CODE, 40, 0 FROM PO_ITEM WHERE PO_CODE = 'A2';
 

1 row created.

SQL> SELECT * FROM PO_ITEM;

PO_CODE      PARENT_PO_CO ITEM_CODE       PO_QTY    BK_QTY
------------ ------------ ------------ --------- ---------
A2                        IT001               50        40
A3           A1           IT001               60         0
A4           A2           IT001               40         0
A1                        IT001              100        60

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM PO_ITEM;

PO_CODE      PARENT_PO_CO ITEM_CODE       PO_QTY    BK_QTY
------------ ------------ ------------ --------- ---------
A2                        IT001               50        40
A3           A1           IT001               60         0
A1                        IT001              100        60

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

So the problem finally boils down to one case, what if the Inserts fails ? the BK_UPD column will have inconsistent 

values.

I've read the examples above, but the solution using BEFORE and AFTER triggers in tandem is cumbersome. Can U suggest 

me some other way of doing it, maybe using a stored procedure.. ? The bottomline is that the database should not end 

up in an inconsistent state & the updation should take place at the back end only. Although we are using Forms 6i as 

the front end & the PRE/POST INSERT/UPDATE/DELETE triggers at block level provides an easy & viable solution, our 

standards do not allow that, viz. "All backupdation code should be there in the backend and they should be triggered 

from the backend only".
Looking forward to your valuable suggestion..

 

Tom Kyte
July 13, 2004 - 11:44 am UTC

do not do logic like this in an automagical trigger -- that is disaster waiting to happen.

use a straightforward stored procedure "create_po( ..... )", have people invoke that stored procedure, put your logic in there.


using trigger like this leads to certain disaster every time I see it done. it is confusing and non-maintainable and really hard to document/understand. people will be rueing the day you did this to them for years to come.

Does not work with temporary tables

Veera, October 18, 2004 - 9:57 am UTC

I used autonomous transaction to insert program log in a temporary table. It worked fine if the stored procedure is in my schema.

CREATE OR REPLACE PROCEDURE insert_log_line
(
p_debug_level IN NUMBER,
p_in_level IN NUMBER,
p_debug_message IN VARCHAR2
) IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
IF p_in_level > p_debug_level THEN
INSERT INTO temp_xxx00a_program_log
VALUES (sysdate, p_debug_message);
END IF;

COMMIT;
END insert_log_line;

temp_xxx00a_program_log table is created in user schema each time when the program is run.

But when we moved the stored procedure to common schema for system testing, it did not work, i.e it did not give any error and it did not commit the data.



Tom Kyte
October 18, 2004 - 10:16 am UTC

umm -- you did something wrong then. things don't work that way.


perhaps you created your temporary table with on commit delete rows. then, i would fully expect this table to always appear empty.


Not that you needed an autonomous transcaction anyhow -- i mean "why"? with a temporary table -- only YOU can see it (so you did not need to commit to let another session see it).

So, one might ask "what was the thought process behind an atrans".


but given the level of detail supplied here (eg: no full test case, no create table) I cannot really say anything more.

Does not work with temporary tables

veera, October 18, 2004 - 10:42 am UTC

Tom,

Thanks for your quick response.
This is my table layout.

DROP TABLE temp_program_log;
--
CREATE TABLE temp_program_log
(
creation_date DATE,
log_message VARCHAR2(300)
);

I created this atrans to log the program flow, so if my main program fails due to any reason I will rollback the main transaction tables, but I can still see the program log to know the error.

I call this stored procedure with the autonomous transaction from a batch process which is also a stored procedure which is called from an oracle form.

Again this works fine if the stored procedure is in my schema.


Tom Kyte
October 18, 2004 - 11:03 am UTC

it works fine regardless of the schema it is in. It is that it will write to a single table, in that other schema


It works, it worked, it did not "fail to insert", you just looked in the wrong table. It worked as stored procedures are designed to work.

Sorry -- but you would have to give me a clear cut example of it "failing".


That by the way is "not a temporary table", that is a table.

veera, October 18, 2004 - 11:49 am UTC

Tom,

Thanks again for your quick response, and sorry for not giving as much information as you needed.

This table is temporary in the sense it's created in user schema, and dropped and created each time the program runs.

Let me explain our environment. We have a common schema prod_user which will create all the transaction tables and all users will have synonyms to that table.
Normally i.e in SQL*PLUS all users will have only select access, but when we go to forms menu, we will change the user role so that users can have update access to those tables.

During testing in development to avoid the migration and change managemet process, I created tables and stored procedures in my schema.
So for this autonomous transaction, I created the stored procedure and tables in my schema and it worked fine. That is my log table entries were committed, and transaction
tables that are used in main process were rolled back when there was any error.
If there was no error all tables entries were committed.


But when I moved the code to system testing testers will have synonyms to the prod_user schema, so all the tables and stored procedures are used from the prod_user schema. But still the temp_xxx00a_program_log is created in the tester schema. That's where I had problem.


The program did not fail (it finished successfully), but it did not commit the data in any table neither user table i.e temp_xxx00a_program_log nor prod_user schema tables.


In nut shell the program commits data in my environment (schema), the same program with the same data when tester runs in a different schema it does not commit. When I took out the PRAGMA AUTONOMOUS TRANSACTION
it worked fine for the tester. Downside is we cannot see the log when program fails.

Tom Kyte
October 18, 2004 - 1:15 pm UTC

yes it did.

tell you what set me up a test case and prove me wrong. The procedure did insert into a single table, the procedure did commit those inserts, those inserts were visible to anyone that looked at them. Really -- they would be - they have to be (else the database is *broken* totally).

Sorry -- but you've made a mistake somewhere, setting up a test case and trying to prove me wrong will help us find that mistake.

What if I need old and new both values...

Ajeeet, November 05, 2004 - 9:07 am UTC

Tom,
I read through a your solution for mutating table..you have given 2 different appraoches here -- one where we need old values and another where we need new values.In my one of the requirement i think i need both..I used both of the way you suggested and it works too.But want to be sure that i am not doing something wrong..

also
What i really want to ask --can i get the old and new values using one set of package and triggers ...

or is there a better way to do this..

Here is my requiremt.

create table demo (plan_seq_id number(9) not null,plan_id number(9) not null,fleet varchar2(10) not null,
removal_date date,removal_value number(7) );

alter table demo add constraint demo_pk primary key(plan_seq_id,plan_id,fleet)
using index ;

--
insert into demo values (1,10,'MAS',sysdate-5,500) ;
insert into demo values (2,10,'MAS',sysdate-4,500) ;
insert into demo values (3,10,'MAS',sysdate-4,800) ;
commit ;

Now If I update a plan_version (which is plan_seq_id in the table) like this..

update demo set removal_date = sysdate+100 ,removal_value = 100
where plan_seq_id = 1 ;
then I need to have data like this :

PLAN_SEQ_ID PLAN_ID FLEET REMOVAL_D REMOVAL_VALUE
----------- ---------- ---------- --------- -------------
1 10 MAS 31-OCT-04 500
4 10 MAS 13-FEB-05 100
5 11 MAS 13-FEB-05 100
2 10 MAS 01-NOV-04 500
3 10 MAS 01-NOV-04 800

-so the requirement is -- if user updates a plan from front end --which is Java screens -- then following things should happen -
1) create a new records i.e a different plan version of the
plan version user updated and this new record should have all old values + updated values of the plan..so that is my record where plan_seq_id = 4 in above (here plan_seq_id would change as it is a system generated number - -using a sequence here).


2) Create a new record -- i.e a new plan version as in #1 but this record should have a different plan id which the record where plan_seq_id = 5 in the above output.so this is same as #1 but in this record i should have a different plan_id..again we have to just increment the original plan_id by 1 ...when I say a new plan_id.

3) this is strange --they want to have the old values (not updated values ) in the record which they choose for updation.plan_seq_id can change -- as it is really a system generated number --so does not matter as such.

I used your code like this..

create or replace package state_pkg
as
type ridArray is table of rowid index by binary_integer;

newRows ridArray;
empty ridArray;
end;
/
--
create or replace trigger parent_bi
before update on demo
begin
state_pkg.newRows := state_pkg.empty;
end;
/
create or replace trigger parent_aifer
after update on demo for each row
begin
state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid;
end;
/
create sequence plan_dtl_seq start with 4 increment by 1 ;
create or replace trigger demo_ai
after update on demo
declare
new_plan_seq number ;
begin
--
for i in 1 .. state_pkg.newRows.count loop
select plan_dtl_seq.nextval into new_plan_seq from dual;

insert into demo
select new_plan_seq, plan_id, fleet,removal_date,removal_value
from demo where rowid = state_pkg.newRows(i);
select plan_dtl_seq.nextval into new_plan_seq from dual;
insert into demo
select new_plan_seq, plan_id+1, fleet,removal_date,removal_value
from demo where rowid = state_pkg.newRows(i);
delete from demo where rowid = state_pkg.newRows(i);
end loop;
end;
/

create or replace package update_demo_pkg
as
type array is table of demo%rowtype index by binary_integer;

oldvals array;
empty array;
end;
/
create or replace trigger demo_fix_bu
before update on demo
begin
update_demo_pkg.oldvals := update_demo_pkg.empty;
end;
/
create or replace trigger delete_demo_bdfer
before update on demo
for each row
declare
i number default update_demo_pkg.oldvals.count+1;
begin
update_demo_pkg.oldvals(i).fleet := :old.fleet;
update_demo_pkg.oldvals(i).plan_id := :old.plan_id;
update_demo_pkg.oldvals(i).plan_seq_id := :old.plan_seq_id;
update_demo_pkg.oldvals(i).removal_date := :old.removal_date;
update_demo_pkg.oldvals(i).removal_value := :old.removal_value;
end;
/
create or replace trigger delete_demo_ad
after update on demo
begin
for i in 1 .. update_demo_pkg.oldvals.count loop
insert into demo
values
( plan_dtl_seq.nextval ,
update_demo_pkg.oldvals(i).plan_id,
update_demo_pkg.oldvals(i).fleet,
update_demo_pkg.oldvals(i).removal_date,
update_demo_pkg.oldvals(i).removal_value );
end loop;
end;
/

--

Thanks
Ajeet


Tom Kyte
November 05, 2004 - 4:29 pm UTC

why would you ever even consider doing this via triggers?


create a procedure

have this procedure accept inputs
have this procedure do the correct, unambigous thing

have the java call this stored procedure -- call it a "method" and they will think it is "cool"


this is a really bad idea -- how obscure can we get, just use a procedure.

Should one avoid autonomous transaction here?

max, March 24, 2005 - 4:55 am UTC

hi tom,

we 'd like to get access to an existing (more or less complex) procedure's output from within SQL. this procedure generates/inserts data into a table and commits. instead of a two-step-approach (1st generating data, 2nd querying generated data) we 'd like to use a function that combines both steps. such function would have to make use of an autonomous transaction in order to be called from within SQL.

is there anything wrong with such an approach?

Tom Kyte
March 24, 2005 - 8:50 am UTC

well, it changes the transactional semantics here.

before, calling this procedure was like calling commit.

now, it is not.

it is up to you.

Seems the procedure should return a ref cursor - then you call procedure, get cursor, fetch data.

Autonomous transaction for a part of the procedure

Surjam, April 19, 2005 - 11:45 am UTC

Hi Tom,

Is is possbile to declare autonomous transaction for a part of the procedure?

something like..

create or replace procedure test
as
begin

insert into t values(100);

begin
< if there could be pragma autonomous transaction here>
insert into t values(200);
commit;
end;
rollback;
end;


so only the insert of value 200 gets commited and the first insert to be rollbaked.

Thanks for the time


Tom Kyte
April 19, 2005 - 12:10 pm UTC

no, you would need to break that code out into another procedure.


Autonomous transaction refers to data in previous context

Salaam Yitbarek, December 19, 2005 - 4:19 pm UTC

I have a transaction t1 in which I do a lot of stuff, among which is generate some rows in table X. I look at these rows in table X, and if there is some sort of error, I would like to store these rows in a table Y, and then rollback table X and all other statements in transaction t1.

I try to do this using an autonomous transaction t2 to do the insert into table Y, but the problem is that in that in t2, the changes to table X that took place in t1 are not visible. How can I get around this problem?

In case the above is not clear, below is a script that illustrates:

SET ECHO ON;
CREATE TABLE TABLE_CHANGING (COL NUMBER)
Table created

CREATE TABLE TABLE_LOG (COL NUMBER)
Table created

CREATE OR REPLACE PACKAGE PACK_AUTO AS

PROCEDURE PROC_AUTO;

END PACK_AUTO;
Package created

CREATE OR REPLACE PACKAGE BODY PACK_AUTO AS

PROCEDURE PROC_AUTO IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TABLE_LOG SELECT 1 FROM TABLE_CHANGING;
COMMIT;
END PROC_AUTO;

END PACK_AUTO;
Package body created

BEGIN
INSERT INTO TABLE_CHANGING VALUES (1);
CANSIM2.PACK_AUTO.PROC_AUTO;
COMMIT;
END;
PL/SQL procedure successfully completed

SELECT * FROM TABLE_LOG
0 rows selected

SELECT * FROM TABLE_CHANGING
COL
---
1
1 row selected

ROLLBACK
Rollback complete

DELETE FROM TABLE_LOG
0 rows deleted


DELETE FROM TABLE_CHANGING
1 rows deleted


BEGIN
INSERT INTO TABLE_CHANGING VALUES (1);
COMMIT;
PACK_AUTO.PROC_AUTO;
END;
PL/SQL procedure successfully completed

SELECT * FROM TABLE_LOG
COL
---
1
1 row selected

SELECT * FROM TABLE_CHANGING
COL
---
1
1 row selected

DROP TABLE TABLE_LOG
Table dropped

DROP TABLE TABLE_CHANGING
Table dropped

DROP PACKAGE PACK_AUTO
Package dropped


Thanks.

Tom Kyte
December 19, 2005 - 4:39 pm UTC

since you cannot reasonably enforce data integrity constraints yourself, in your application - that cross rows in the table, or objects in the database - I fail to see the point....


But this is the way an autonomous transaction is *designed* to work.


This logic scares me, I don't want to comment further, this sounds like a big data integrity bug just waiting to happen.

You would really need to explain "why" you want to do this, "what" sort of logic you could have that would say "nope, don't want these rows after all" - I don't follow that at all.

(this can be done, I'm just afraid to show you how - because the initial logic itself sounds "flawed" to me)

Re: Autonomous transaction refers to data in previous context

Salaam Yitbarek, December 20, 2005 - 8:13 am UTC

Yes, you're right, of course, I am reconsidering the whole process, which is as follows.

I have a large transaction t which can be conceptually split into three parts, p1, p2, and p3.

In each of these parts, I do some complex processing, error detection, and then populate table X.

If an error is detected in any of three parts, I must rollback the whole transaction to leave table X the way it was.

At the same time, I want to log the errors in tables. Unfortunately, the errors are generated during transaction t1.

p1 is not a problem, since populating table X is the last thing it does. However, if I log an error during p2 or p3 and then commit, then it will commit what I did in p1, and I don't want to do this.

To give you an idea of what is actually being processed, p2 reads data about statistical datapoints that are meant to be modified in the database, and detects errors such as if there are variance errors among the datapoints.

If variance errors are detected, then I want to put all the datapoints between which there are gaps in an error table. But this list of datapoints only exists in transaction t1.

Tom Kyte
December 20, 2005 - 9:38 am UTC

when error
bulk collect OUT the data in error
rollback;
bulk insert IN the data in error to error log
commit;
RAISE

would that not work well?

Re: Autonomous transaction refers to data in previous context

Salaam Yitbarek, December 20, 2005 - 8:55 am UTC

Just to clarify something from my previous post, p1 populates table X, but p2 and p3 do UPDATES on table X.

Re: Autonomous transaction refers to data in previous context

Salaam Yitbarek, December 21, 2005 - 8:09 am UTC

Yes, I sounds like a good way to go for now.

Thanks!

ok

Raju, January 05, 2006 - 6:11 am UTC

How to Modify the procedure so that it runs successfully.

I want that to be an autonomous transaction.

can another block be used here??

SQL> create global temporary table t_date(c date)
  2  on commit delete rows
  3  /

Table created.

SQL> create or replace procedure t_proc
  2  as
  3  pragma autonomous_transaction;
  4  begin
  5  for i in 1..10 loop
  6   insert into t_date values(sysdate+i);
  7  end loop;
  8  end;
  9  /

Procedure created.

SQL> commit
  2  /

Commit complete.

SQL> exec t_proc
BEGIN t_proc; END;

*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SCOTT.T_PROC", line 5
ORA-06512: at line 1 
 

Tom Kyte
January 05, 2006 - 10:45 am UTC

well, an autonomous transaction MUST commit or rollback before it returns. Add a commit to your procedure.

but actually, ask yourself, do I really want to use an autonomous transaction (the answer is almost always "no")

A reader, January 05, 2006 - 11:22 am UTC

hey, tom, please be honest: didn't you just get that description of the action(s) to be taken to avoid such an issue from the 'database error messages' delivered with documentation? :o)

Tom Kyte
January 05, 2006 - 11:49 am UTC

not sure what you are referring to?

Rollback In case of an exception in automous trans?

Nick, May 08, 2006 - 10:51 am UTC

I have an autonomous transaction that has an update statement. If it throws an exception, is it correct (required) to issue a rollback? Here is an example below- notice that it has a rollback after the error logging routine (which itself is an autonomous transaction).

Thanks!

--------------------------------------
function RefreshLeads return number is
PRAGMA AUTONOMOUS_TRANSACTION; --we want to commit this by itself.
n_retcode number;
BEGIN

--do the update
UPDATE lead l .. .;

COMMIT;

return 0;

EXCEPTION
WHEN OTHERS THEN
n_retcode := SQLCODE;
LogMessage(DBG_EXCEPTION, NULL,
'Exception refreshing leads',
NULL,n_retcode,SQLERRM);

rollback;
return n_retcode;

END RefreshLeads;


Tom Kyte
May 08, 2006 - 10:56 am UTC

Only you can answer this.

What do you want to have happen - a rollback? If so, go for it. If not, tell us what you would like to have happen??

We cannot decide your personal application logic.


I can say - if you cannot answer this, there is NO WAY you should be considering an autonomous transaction yet - you need to sit down and decide what is your real, useful, correct transaction semantics.

I get very scared when people start doing autonomous transactions - virtually everytime I see them used, they are used entirely inappropriately, causing real data integrity issues



RE: Rollback In case of an exception in automous

Nick, May 08, 2006 - 11:21 am UTC

Thanks for the reply- and don't be scared Tom.

My question was more "Is it technically correct" rather than is it correct business wise. I just wanted to make sure that that rollback in the exception block rolls back only the autonomous transaction (it does, right?). I should have phrased it differently.




Tom Kyte
May 08, 2006 - 1:33 pm UTC

I'm scared anyway. I've RARELY seen autonomous transactions used correctly in the field.



Yes, transaction control statements in the autonomous block affect that block only.

problem related to bulk insert

Mousumi Ghosh, May 10, 2006 - 9:48 am UTC

I have two tables
1)TEST_TBL(name varchar2(10),age varchar2(10))
2)TMP_ABL_IFS_MAP_CODES(a varchar2(10) , b number )

test_tbl has suppose 100 records where 98th record has age='as23'

now in a procedure, to insert all the records into
TMP_ABL_IFS_MAP_CODES from test_tbl I want to use

INSERT INTO TMP_ABL_IFS_MAP_CODES
SELECT * FROM TEST_TBL ;

there will be an error...
in exception section I want to show
the 98th record for which this exception came...

is it possible to show when I use
INSERT INTO TMP_ABL_IFS_MAP_CODES
SELECT * FROM TEST_TBL ;


Regards,
Mousumi

Tom Kyte
May 10, 2006 - 10:23 am UTC

the 98th record, hmm, I smell a rat. What is the "98th record" exactly in a relational database?

In 10gR2 we can do this with the LOG ERRORS clause, </code> http://asktom.oracle.com/Misc/how-cool-is-this.html <code>but prior to that, you'd have to procedurally process the data using bulk collect and forall inserts with SAVE EXCEPTIONS.




SAVEPOINT

P.Karthick, May 10, 2006 - 10:42 am UTC

"Is is possbile to declare autonomous transaction for a part of the procedure?...."

for the question Surjam asked cant we use SAVEPOINT tom.



Tom Kyte
May 11, 2006 - 7:26 am UTC

savepoints are not even remotely similar in functionality to an autonomous transactions.

savepoints are a useful, generally safe feature.

as opposed to autonomous transactions...

Have I got the right end of the stick?

Dawn, May 11, 2006 - 5:07 am UTC

Tom,

Given that you mention you often see autonomous transactions used improperly, I'd like to double-check with you that my understanding is correct, as I'm a relative newbie at pl/sql.

I have used an autonomous transaction as part of error handling - eg:

1. Start main transaction
2. Do processing (inserts/updates/whatever)
3. Error occurs (oh no!)
4. Start autonomous transaction
5. Log error in table
6. Commit autonomous transaction. Error logged in table.
7. Rollback the main transaction. All processing changes lost, error still logged in table.

I believe that this is an entirely proper use of autonomous transactions, as the error information is separate to the rest of the information, so keeping it (or not) shouldn't affect the integrity of the main data. However, you've got me doubting!

Is my thinking correct? If it isn't, I'd like to correct it now, before I inflict any more damage on my poor database...!

Tom Kyte
May 11, 2006 - 7:22 pm UTC

That is an entirely proper use of an autonomous transaction.



9.2

A reader, September 11, 2006 - 8:06 am UTC

Tom,

I have the following test case, a table

create table t (a varchar2(100));


create or replace procedure auto1 as
pragma autonomous_Transaction;
begin
insert into t (a) values ('X');
dbms_lock.sleep(5);
auto2;
dbms_lock.sleep(5);
rollback;
exception when others
then
dbms_output.put_line(' others in auto1 ');
end;
/


create or replace procedure auto2 as
pragma autonomous_Transaction;
begin
insert into t (a) values ('N');
dbms_lock.sleep(10);
--auto3;
rollback;
--insert into t (a) values ('M');
--commit;
exception when others
then
errpkg.logandgo('adfs');
end;
/

Yes, I'd put the sleeps to interrogate the v$transaction, v$session views to check how many rows are available in the V$Transaction. I thought I'd find TWO rows in V$transaction, but found only one 'fluctuating' row!


1) begin
auto1;
end;
/

I do the following query from another Session:

select addr,xidusn,xidslot,xidsqn, TN.used_ublk,ss.username from V$TRANSACTION tn, v$session ss where tn.addr = ss.taddr

What happens is I first start with one unique combination of the addr,xidusn,xidslot then when auto2 is called, it switches to a brand new combination. Finally after auto2 completes and control comes back to auto1 we get the orig combination of auto1.


Question: Is the reason why I cannot see two rows in V$transaction using the above query is because of the join condition 'tn.addr=ss.taddr' as v$session gets only the latest transaction it is running as value in taddr?

Tom Kyte
September 11, 2006 - 10:03 am UTC

there is only one session.

hence there can only be one row in v$session for you.

So, if you join to v$session, you'll by definition only get the currently active transaction for that session.

Slippery autonomous transaction

Arindam Mukherjee, September 12, 2006 - 1:04 am UTC

Respected Sir,

Today I really wonder to have the following line in the Oracle 9i PL/SQL Guide.

“If a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram.”

Now let us imagine one scenario. One has written one stored package or procedure and that stored object is invoked from a trigger to gain performance edge. If that stored object does not handle exception properly, so database change done by that stored package/procedure prevails. At the end, when application or calling program issues “Commit”, all changes would be on the same page. Am I right Sir?

Now you please tell us - How would we deal with this undesired scenario?

Tom Kyte
September 12, 2006 - 8:20 am UTC

that one is a "bit ambigous".  It could be written better.


If the error propagates back to the client - the STATEMENT is rolled back, and the statement includes all statements executed by plsql.  consider:




ops$tkyte%ORA9IR2> create table t ( msg varchar2(30) );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure p1
  2  as
  3  begin
  4          insert into t values ( 'i am in p1' );
  5          raise program_error;
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure p2
  2  as
  3  begin
  4          insert into t values ( 'i am starting p2' );
  5          p1;
  6  exception
  7          when others
  8          then
  9                  for x in ( select * from t ) loop dbms_output.put_line( x.msg ); end loop;
 10                  RAISE;
 11  end;
 12  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t values ( 'before anything' );

1 row created.


<b>so, in the procedure, P1 will exit with an unhandled exception - but p2 catches that exception so when we run it, we'll see:</b>

ops$tkyte%ORA9IR2> exec p2
before anything
i am starting p2
i am in p1
BEGIN p2; END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P2", line 10
ORA-06512: at line 1




<b>the subprogram P1 was not rolled back HOWEVER, since the STATEMENT (begin p2; end;) failed - and Statements are ATOMIC, the work performed by P2 and any side effects of that statement - are rolled back:</b>

ops$tkyte%ORA9IR2> select * from t;

MSG
------------------------------
before anything


<b>Note that the STATEMENTs work is rolled back, NOT the transaction</b>



I would catch and handle ONLY THOSE EXCEPTIONS THAT ARE NOT ERRORS - eg: no_data_found is the "classic example", maybe it is OK for your select into to get no_data_found, you are expecting it, you catch it, you deal with it- you default the variables in the when block.


All other exceptions - things you are NOT EXPECTING, do not catch them (or AT BEST, catch them, log them, AND THEN RE=RAISE THEM!!!!  that is the most important fact - you RE-RAISE THEM

You RE-RAISE THEM

let them get back to the client

And the database will be put back the way it was right before that statement executed.


If you catch it
And you do not re-raise it

the database is left "half done", the client has NOT A CLUE IN THE WORLD what the database state is.


IF you catch exceptions
   that you cannot handle (eg: you just want to log the error)
       RE-RAISE THAT EXCEPTION 

A reader, September 12, 2006 - 3:27 am UTC

Arindam

Consider:

create table t (a varchar2(10));
create table t1 (a varchar2(100));


/* Formatted on 12/09/2006 07:53 (Formatter Plus v4.8.7) */
CREATE OR REPLACE PACKAGE test_err
AS
PROCEDURE p;
END;
/


CREATE OR REPLACE PACKAGE BODY test_err
AS
PROCEDURE p
IS
BEGIN
INSERT INTO t
VALUES ( 'trying' );

INSERT INTO t
VALUES ( 'asdfjsalfdjsafjsadf' );
END;
END;
/

--- Now run
BEGIN
INSERT INTO t1
VALUES ( 'SYS' );
EXCEPTION
WHEN OTHERS THEN

INSERT INTO t
VALUES ( ' gg ' );
END;


---

If errors are let out the entire exception causing statement is rolled back. And hence the value "SYS" never gets saved to T1 nor do the valid value "trying" onto T.

In the above example if an exception handler is coded inside procedure p, then the value "trying" would be saved to T and "SYS" saved to T1, that is because the exception is handled.

Tom Kyte
September 12, 2006 - 8:33 am UTC

see above

IF YOU CATCH EXCEPTIONS YOU CANNOT DEAL WITH (they are truly "exceptional", not expected, nothing you planned for) then

RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM
RE-RAISE THEM



You have a bug in your developed code, you catch a when others, you do not re-raise it.

RE-RAISE it after doing whatever sort of logging you want to do.

A reader, September 12, 2006 - 4:18 pm UTC

Tom

I modified your example's P1 as
create or replace procedure p1
as
begin
insert into t values ('i am in p1');
insert into t values ( 'i am in p1 sdfsakjfasdfljsadlsalsadjlfasljdfsalfjsjf' );


end;

I was hoping the Rollback could be traceable by 10046 event, but could not find any rlbk=1 in the trace file.

Why not?

Is it so implicit it is not recorded in the trace file?

Tom Kyte
September 13, 2006 - 7:00 am UTC

it doesn't rollback the transaction.

I DEMONSTRATED it does not rollback the transaction.

I demonstrated that statement level atomicity is preserved, I demonstrated the transaction is not rolled back, I demonstrated that the database state is "well understood" by the client since it knows the work done by a statement that fails is undone.



A reader, September 13, 2006 - 2:17 pm UTC

Sorry, point taken.

Autonomous Session ?

Yogesh Purabiya, April 17, 2007 - 11:53 pm UTC

Last few years whatever I have learned about Oracle is either from your (this) site, or from your books.
Currently I am going through your book (Expert / Oracle / Database Architecture).

You have nicely explained the difference between the Session & Connection.

While reading the chapter "Transactions" - topic "Autonomous Transactions" - I could easily understand it completely.
But, later on, thought of some few more points; please verify and tell me if I am wrong.

(1) Autonomous Transactions are carried out in a different session - may I call it an Autonomous Session ?

(2) When we commit in an Autonomous Trigger, the current Autonomous Transaction is over, and we enter in a fresh / new / next Autonomous Trasaction in the same Autonomous Session. Is that true ?

(3) As per the example of Auditing Trigger using Autonomous Transaction - you are using the following PL/SQL statement

raise_application_error (-20001, "Access Denied !");

To which session this PL/SQL Statement (raise_application_error) belongs to ?

I think it belongs to the Autonomous Session - and it gets terminated / aborted.
But, before termination, it must be sending a message / alert to the calling (triggering) session / transaction that the trigger has raised an exception.
Otherwise, exceptions do not apply to more than 1 session / transaction.
Am I right ?
Tom Kyte
April 18, 2007 - 12:03 pm UTC

1) no they are not, autonomous transactions are in the SAME SESSION, different transactions.

2) no, when you commit the autonomous transaction - you are back in the parent transaction (they are nested)

3) there is ONLY ONE SESSION


Autonomous Transactions

Yogesh Purabiya, April 18, 2007 - 11:52 pm UTC

Thanks, as ususal, for the kind support.

pragma autonomous_transaction

Sanjeev VIbuthi, May 02, 2007 - 10:49 am UTC

Hi Tom,

This is regarding pragma autonomous_transaction,

Create table m1 (sno number, name varchar2(10), flag char(1) default 'N');
Create table m2 (no number, sno number, marks number);
alter table m1 add constraint pk_m1 primary key (sno);
alter table m2 add constraint pk_m2 primary key (no);
alter table m2 add constraint fk_m1 foreign key (sno) references m1(sno) ON DELETE CASCADE;

Insert into m1 values (1, 'a','N')
Insert into m1 values (2, 'B','N')
Insert into m1 values (3, 'C','N')
Insert into m1 values (4, 'D','N')

When ever We insert or update or delete records in m2 table,
the corresponding sno in m1 should get updated

--logic
IF inserting into m2
update m1 flag='Y' where m1.sno=:new.m2.sno
elsif updating in m2
update m1 flag='N' where m1.sno=:old.m2.sno
update m1 flag='Y' where m1.sno=:new.m2.sno
elsif deleting
update m1 flag='N' where m1.sno=:old.m2.sno
end if;

This is working fine with a trigger with the above logic on M2 table in insert and update mode,
for deletion if we delete data in a order i.e. from m2 first then m1 it is working fine

But trigger is failing (getting mutating error) when we delete record
from m1 which deletes data from m2 (on delete cascade) which fires trigger and try to update the record which is deleted.

I fixed this issue by adding when others exception and it looks working..

But after reading your book and visiting site, I come up with the following solution...

Create or replace package pkg_test
is
begin
TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
no_list t_table;
no_list2 t_table;
no_empty t_table;

end;
/

Create or replace trigger tidb_stmt_m2 before insert or update or delete on m2
begin

pkg_test.no_list := pkg_test.no_empty;
pkg_test.no_list2 := pkg_test.no_empty;
End;
/

Create or replace trigger tidb_m2 before insert or update or delete on m2
for each row

Begin

IF inserting then
pkg_test.no_list(pkg_test.no_list.count+1) := :new.sno;



elsif deleting then
pkg_test.no_list(pkg_test.no_list.count+1) := :old.sno;

elsif updating('SNO') then

pkg_test.no_list2(pkg_test.no_list2.count+1) := :old.sno;
pkg_test.no_list(pkg_test.no_list.count+1) := :new.sno;

end if;

End;
/

Create or replace trigger tidb_stmt after insert or update or delete on m2
Begin

IF INSERTING THEN


For i in 1..pkg_test.no_list.count
loop
Update m1 set flag = 'Y' where sno = pkg_test.no_list(i);
end loop;

elsif deleting then
For i in 1..pkg_test.no_list.count
loop
Update m1 set flag = 'N' where sno = pkg_test.no_list(i);
end loop;

elsif updating then
For i in 1..pkg_test.no_list2.count
loop
Update m1 set flag = 'N' where sno = pkg_test.no_list2(i);
end loop;

For i in 1..pkg_test.no_list.count
loop
Update m1 set flag = 'Y' where sno = pkg_test.no_list(i);
end loop;

end if;
End;
/

Can you please clarify whether above solution is workable and
why cant we go with exception and pragma autonomous_transaction solutions.

Thanks
Sanjeev Vibuthi
Tom Kyte
May 02, 2007 - 5:29 pm UTC

do not ever EVER use an autonomous transaction to work around the mutating table constraint.

That indicates horribly flawed transactional logic.


what is the goal here - when I see this sort of stuff - I know one thing "you have modeled your data entirely wrong"

And since you are just setting out here, just starting, we can fix the model.


Just say NO to triggers.

About "Expert One-on-One"

karlo, September 22, 2007 - 8:12 pm UTC

Tom,

Just want to confirm:
page 863 of "Expert One-on-One":
are the 2 put_lines in wrong, opposite places ?
i.e top one should say "dbms_output.put_line( 'Already opened' );"

and lower one "dbms_output.put_line( 'NOT already opened cursor' );" ?

thanks

tkyte@TKYTE816> create or replace package body my_pkg
2 as
3
4
5 cursor global_cursor is select ename from emp;
6
7
8  procedure show_results
9  is
10 pragma autonomous_transaction;
11 l_ename emp.ename%type;
12 begin
13   if ( global_cursor%isopen )
14   then
15     dbms_output.put_line( 'NOT already opened cursor' );
16   else
17     dbms_output.put_line( 'Already opened' );
18     open global_cursor;
19   end if;
20
21 loop
22     fetch global_cursor into l_ename;
23     exit when global_cursor%notfound;
24     dbms_output.put_line( l_ename );
25 end loop;
26 close global_cursor;
27 end;

Tom Kyte
September 26, 2007 - 1:32 pm UTC

:( indeed.

oops sorry page 683 not 863

karlo, September 22, 2007 - 8:14 pm UTC

oops sorry I made a typo -
it's page 683 of the book

transaction

A reader, February 05, 2008 - 7:54 pm UTC

Tom:

How in plsql you ensure that your transaction will either insert a record into Table1 and Table2 or
will not be saved


INSERT into EMP values .....

INSERT into ANOTHER_TABLE values .....


IF there was 0 records inserted into ANOTHER_TABLE, i do not want to save the record inserted in EMP.

IF there was 0 records or error occurs in first INSERT then no records should be saved in SECOND table.


Do you check for sql%rowcount?

Tom Kyte
February 05, 2008 - 9:49 pm UTC

umm, confused.

if you have


insert into table values.....

it will either (without log errors, which I think I can assume)

a) insert a record
b) fail

and if it fails, just let the exception propagate up up and away and all things are taken care of as they should be.

ops$tkyte%ORA10GR2> create table t1 ( x int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int check (x >0) );

Table created.

ops$tkyte%ORA10GR2> create or replace procedure p( p_x in number )
  2  as
  3  begin
  4          insert into t1 values (p_x);
  5          insert into t2 values (p_x);
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p(1);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select 't1', x from t1 union all select 't2', x from t2;

'T          X
-- ----------
t1          1
t2          1

ops$tkyte%ORA10GR2> exec p(0);
BEGIN p(0); END;

*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C0024645) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 1


ops$tkyte%ORA10GR2> select 't1', x from t1 union all select 't2', x from t2;

'T          X
-- ----------
t1          1
t2          1



it would only be if you caught any and all exceptions and did something bad like turn them into a return code (and make the exception go away) that you would have a problem...

this is why I hate when others :( People don't get it. Look how different and BAD this is:

ops$tkyte%ORA10GR2> create table t1 ( x int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int check (x >0) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p( p_x in number, p_return_code out number )
  2  as
  3  begin
  4          insert into t1 values (p_x);
  5          insert into t2 values (p_x);
  6          p_return_code := 0;
  7  exception
  8  when others
  9  then
 10          p_return_code := 1234;
 11  end;
 12  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable rc number
ops$tkyte%ORA10GR2> set autoprint on
ops$tkyte%ORA10GR2> exec p(1,:rc);

PL/SQL procedure successfully completed.


        RC
----------
         0

ops$tkyte%ORA10GR2> select 't1', x from t1 union all select 't2', x from t2;

'T          X
-- ----------
t1          1
t2          1

ops$tkyte%ORA10GR2> exec p(0,:rc);

PL/SQL procedure successfully completed.


        RC
----------
      1234

ops$tkyte%ORA10GR2> select 't1', x from t1 union all select 't2', x from t2;

'T          X
-- ----------
t1          1
t1          0
t2          1


transaction

A reader, April 17, 2008 - 11:37 pm UTC

Tom:

If you have several procedures that insert values into a large table would you create an API like p1 or insert statement without API like p2.

In p1, do i commit transaction in p1 or save_t1_record?

Procedure P1

begin

save_t1_record(p_col1,p_col2,p_col3)

commit;

end;

procedure p2

begin

insert into t1 (col1, col2, col3) values (p_col1,p_col2,...)

end;
Tom Kyte
April 18, 2008 - 8:25 am UTC

transaction

A reader, April 18, 2008 - 9:17 am UTC

Tom:

i am confused. Did you mean you hate P1?

P1 is the one using a table API to insert a record into the table.

P2 is just using a regular insert statement into the table which is repeated in many other procedures.

for some reason i thought you always encourage table API to seprate teh code from presentation logic in web applications.
Tom Kyte
April 18, 2008 - 10:21 am UTC

no, p2 - why have a silly little stored procedure that does that?


p1 is a procedure calling another procedure, in general, that is acceptable.

p2 is a procedure I would never want - a table api, ugh.


read the supplied link above please.

transaction

A reader, April 18, 2008 - 11:56 am UTC

I read the link.

I do not understand why is the difference between TAPI and XAPI.

When i have an insert statement to a table is this a transaction APi
or table API. hard to distnguish.


You say you like p1.

To me p1, is calling a procedure which is the table api.

Reading your thread I thought you would prefer to have the SQL code
(insert statement) in the procedure itslef which is what p2 has.

can you clarify this confusion a little?
Tom Kyte
April 18, 2008 - 2:52 pm UTC

how many transactions do you have that consistent soley of "insert into t" and nothing else.

When you have a package that looks like:

...
procedure insert_into_t ( .... );
procedure update_t ( .... );
procedure delete_a_row_from_t( .... );
function query_table_t( ... ) returns sys_refcursor;

you have a 'TAPI'

when you have a package:

....
procedure hire_employee()
procedure fire_employee()
procedure transfer_employee()
....


you have a 'XAPI' - you have transactions.


I see nothing to be gained by writing a procedure that contains nothing more than "insert into t" - that is a TAPI and not useful



I like p1 in general - because it calls other procedures and there is nothing wrong with that. I was not evaluating what p2 was when commenting on p1.

I don't like p2 at all because it is a tapi, as I've said.

Transaction

A reader, April 19, 2008 - 6:10 pm UTC

Tom:

Let me describe P1 and P2 in more detail and then you can advise me on which way is more efficient. MAybe you
also have a better way than both.

I do not think i provided you with
enough info to make a decision. I am not going to list all the parameters but

this is the basic idea.

I have an html data entry form that will be submitted to a an API (procedure P1 or P2).

procedure p1
(p_bkno IN VARCHAR2
p1_serv IN VARCHAR2,
p1_dir IN VARCHAR2,
p1_status IN VARCHAR2,
p2_serv IN varchar2,
p2_dir ...........,
p2_status........,
p3_serv.........,
p3_dir...........,
p3_Status ,
p_file_name IN types.array,
p_file_size IN types.array )

AS

-- some variables
-- some exceptions

begin

-- some code to validate the input parameters.


IF (p1_serv is not null and p1_dir is not null and p1_serv is not null) THEN
SAVE_REC_INTO_TABLE_T;

SAVE_FILES_INTO_TABLE_Y;
END IF;

IF (p2_serv is not null and p2_dir is not null and p2_serv is not null) THEN
SAVE_REC_INTO_TABLE_T;

SAVE_FILES_INTO_TABLE_Y;
END IF;

IF (p3_serv is not null and p3_dir is not null and p3_serv is not null) THEN
SAVE_REC_INTO_TABLE_T;

SAVE_FILES_INTO_TABLE_Y;
END IF;


--some flag updates for other tables

COMMIT;

EXCEPTIONS

--some exceptions

END;

--------------------------------
PROCEDURE SAVE_REC_INTO_TABLE_T
(p_bkno
p_serv,
p_dir,
p_Status)

begin

INSERT into table T (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)

END;

-----------------------------------
PROCEDURE SAVE_FILES_INTO_TABLE_Y
(P_serv,
p_bkno,
p_file_name,
p_file_size,
)

begin
For i in 1..p_file_name(i)
LOOP

INSERT into table Y (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)
END LOOP;
END;

--------------------------------------------------
procedure p2

begin

IF (p1_serv is not null and p1_dir is not null and p1_serv is not null) THEN
INSERT into table T (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)

For i in 1..p_file_name(i)
LOOP

INSERT into table Y (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)
END LOOP;
END IF;

IF (p2_serv is not null and p2_dir is not null and p2_serv is not null) THEN
INSERT into table T (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)

For i in 1..p_file_name(i)
LOOP

INSERT into table Y (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)
END LOOP;
END IF;


IF (p3_serv is not null and p3_dir is not null and p3_serv is not null) THEN
INSERT into table T (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)

For i in 1..p_file_name(i)
LOOP

INSERT into table Y (col1,col2,col3,col4,...) values (seq.nextval,p_bkno,p_serv,p_dir,p_Status,...)
END LOOP;
END IF;


End;




Tom Kyte
April 23, 2008 - 4:12 pm UTC

let me put it to you this way.

pretend it is not sql
pretend INSERT is just an "api" call - like calling "procedure_x"

Now, your goal - write modular code, period.

I see what appears to be a ton of repeated stuff, I would be using a package with the repeated bits implemented as helper functions.


transaction

A reader, April 19, 2008 - 6:20 pm UTC

Tom:

A quick question on the above code

IF you want to implement the logic where you want to validate the value of pX_SERV if it is not null


IF ( p1_serv is not null and p1_serv <> 'AAA') AND
( p2_serv is not null and p2_serv <> 'BBB') AND
( p3_serv is not null and p3_serv <> 'CCC')
THEN raise invalid_VALUE;
END IF;

This does not seem to work when I pass p1_serv="AAA" only with other parameters. I keep hitting the exception.
Ther result should (T AND F and F) which is F. So it should not hit the expcetion.

is it because of the NULLs or Should it be "OR"?

thanks,





Tom Kyte
April 23, 2008 - 4:13 pm UTC

i did not follow this at all.

"this does not seems to work" is as meaningful as "my car won't start" - insufficient data to say anything intelligent back.

procedure

A reader, April 23, 2008 - 7:07 pm UTC

Tom:

I do not understand. Are you saying you would not do P1 or P2. I evetntually will stick the procedure in a pakcage.

<I see what appears to be a ton of repeated stuff, I would be using a package with the repeated bits implemented as helper functions. >

Can you show me a small example of how you would do this for the different conditions I had.


2. what i am trying to build an if logic for is


If (my_first_par is not null) then it must be = "AAA", otherwise raise invalid_exception
If (my_second_par is not null) then it must be = "BBB", otherwise raise invalid_exception
If (my_third_par is not null) then it must be = "CCC", otherwise raise invalid_exception


how do you combine all of these into one IF statement? or do you have to have separate ones.
Tom Kyte
April 28, 2008 - 10:59 am UTC

simply stated:

do not do a TAPI (table api) whereby you have a procedure that does just an insert, another that does just an update, another that does just a delete.

which one of your p1, p2 resembles a TAPI.


2) simple logic. You meant "or", not "and". You want to say "if ANY of the inputs are bad..."


ops$tkyte%ORA10GR2> create or replace function f( p1 in varchar2, p2 in varchar2, p3 in varchar2 ) return varchar2
  2  as
  3  begin
  4          if (( p1 <> 'AAA' and p1 is not null ) OR
  5              ( p2 <> 'BBB' and p2 is not null ) OR
  6              ( p3 <> 'CCC' and p3 is not null ) )
  7          then
  8                  return 'bad';
  9          else
 10                  return 'good';
 11          end if;
 12  end;
 13  /

Function created.

ops$tkyte%ORA10GR2> create table t ( x varchar2(3) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( null );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'AAA' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'BBB' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'CCC' );

1 row created.

ops$tkyte%ORA10GR2> column str format a10
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select t1.x, t2.x, t3.x, f(t1.x,t2.x,t3.x) str
  2    from t t1, t t2, t t3
  3   where (t1.x < t2.x and t2.x < t3.x) or length(t1.x||t2.x||t3.x) = 3;

X   X   X   STR
--- --- --- ----------
        AAA bad
        BBB bad
        CCC good
    AAA     bad
    BBB     good
    CCC     bad
AAA         good
AAA BBB CCC good
BBB         bad
CCC         bad

10 rows selected.


autonomous

A reader, May 23, 2008 - 9:47 am UTC

Tom:

Would not this work the same way as a autonomous transaction. If not, can you explain why

1. start main transaction
2. do processing (insert, update, delete)

now we will go to 3a or 4

3a. Error occurs, raise exception
3b. rollback, insert into error log, update trans_status='F'
3c. commit (this will only commit the error record and failed status)
3d. stop

4. no error, update trans_status='P'
5. commit (This will save all processing in #2)
6. stop


Tom Kyte
May 23, 2008 - 4:18 pm UTC

no it won't.

The purported goal here is to process a set, logging errors as they happen and having the errors be logged regardless of the transaction outcome.


For your logic to work you would have to:

loop
BEGIN
get inputs
try to save outputs
COMMIT; <====
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
log_error;
commit;
end;
end loop;


but that isn't what would be happening.

trans

A reader, May 23, 2008 - 5:29 pm UTC

Tom:

I do not understand. Would the logic you listed implement the same thing without using autonomous transaction or you MUST use an autonomous transaction to update a status flag on failure.

You are committing before the expception which is on Success condition. You are rolling back when an expcetion occurs (failure) and then inserting an error record and committing it. So it seems it is the same as an autonomous trnasaction unless I am missing something.
Tom Kyte
May 23, 2008 - 6:45 pm UTC

I was trying to say "it depends on the logic"

in general, if the need is "log error regardless of what else happens", an autonomous transaction is used.

... You are committing before the expception which is on Success condition....

that EXAMPLE was the pre-conditions you would have to mandate in order for you to be able to use a non-autonomous logging routine. It is contrary to what most would be doing (that commit I flagged - it was flagged to say that would HAVE to be there)

Please note, I wrote:

For your logic to work you would have to:


I was demonstrating what you would HAVE TO DO if you wanted your statement to be "true"

transaction

A reader, May 23, 2008 - 11:24 pm UTC

Tom:

let us say you are not loggin an error.

You are simply committing inserts/updates and updating a status field with "P" when a transactions OR rollback and updating one status with "F" on failure

woul you do it like this?

Procedure P1

begin

...code
...code

update table T set status_code='P';
commit;

exception
when others
rollback
update table T set status_code = 'F'
commit;

end;
Tom Kyte
May 24, 2008 - 6:56 pm UTC

given your scenario

well, still no - I don't believe in commit and rollback in a procedure necessarily, maybe:

BEGIN
  savepoint foo;
  ....code...
  update t set status_code = 'P';
exception
  when others 
  then
     rollback to savepoint foo;
     update t set status_code = 'F';
end;


with lots and lots of comments as to "why" this is OK

Mohamed Houri, May 25, 2008 - 1:35 pm UTC

I can tell you why this is correct

BEGIN
savepoint foo;
....code...
update t set status_code = 'P';
exception
when others
then
rollback to savepoint foo;
update t set status_code = 'F';
end;

Because the above piece of plsql code is generally used in a for loop and I can't imagine you(reader) will commit and rollback for each row. This is why you will use a savepoint in order to rollback only to this savepoint in case of error and you will commit outside the loop for all other successfull rows.
Tom Kyte
May 27, 2008 - 7:48 am UTC

I mean why the "when others" is 'ok' - which, truth be told, it isn't if you ask me.

I still think there is a finite set of things that can go wrong with the transaction and we should enumerate them. And if something else happens - we would just let the error propagate out - it is a FATAL error (it is perfectly acceptable to have a FATAL error you know, you want that to propagate out)

rolback

A reader, June 02, 2008 - 8:28 pm UTC

Tom:

I assume the rollback to savepoint foo would only work if there is no commits after it that did run. right?

for example, if you had this then the rollback wont work.


BEGIN
savepoint foo;
....code...
update t set status_code = 'P';
commit;
code ...(it fails here)
code..
exception
when others
then
rollback to savepoint foo;
update t set status_code = 'F';
end;


Tom Kyte
June 03, 2008 - 10:44 am UTC

of course, with savepoints you would NOT BE USING commit; and rollback; like that - if you do, you end the "savepoint"

Mohamed, June 03, 2008 - 10:54 am UTC

You may have this error in this case

Others :ORA-01086: savepoint 'FOO' never established

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library