just one more thing ...
December 13, 2001 - 3pm Central time zone
Reviewer: Mike Wilson from Los Angeles, CA
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.
Followup December 13, 2001 - 5pm Central time zone:
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
July 8, 2002 - 11am Central time zone
Reviewer: Lisa from Fairfax, VA USA
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
Followup July 8, 2002 - 4pm Central time zone:
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.
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
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.
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'
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;
for the ones that were inserted with Y, update all of the other org records to 'N', setting just
this one to 'Y'
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;
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.
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?
September 12, 2002 - 3pm Central time zone
Reviewer: A Reader from USA
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.
Followup September 12, 2002 - 4pm Central time zone:
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
http://asktom.oracle.com/~tkyte/Mutate/index.html for some techniques
Use an Instead of Trigger can do it.
September 13, 2002 - 2pm Central time zone
Reviewer: A Reader from USA
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.
Followup September 14, 2002 - 2pm Central time zone:
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
December 12, 2002 - 1pm Central time zone
Reviewer: Sandra
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.
Followup December 12, 2002 - 2pm Central time zone:
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
April 20, 2004 - 3am Central time zone
Reviewer: arangaperumal from Chennai,INDIA
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
Followup April 20, 2004 - 8am Central time zone:
you do not find that information without a column like that.
How to handle this.
April 20, 2004 - 8am Central time zone
Reviewer: arangaperumal from Chennai,INDIA
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?
Followup April 20, 2004 - 9am Central time zone:
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
July 13, 2004 - 4am Central time zone
Reviewer: Nayan J. Sarma from Chennai, India
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..
Followup July 13, 2004 - 11am Central time zone:
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
October 18, 2004 - 9am Central time zone
Reviewer: Veera from Rockville, USA
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.
Followup October 18, 2004 - 10am Central time zone:
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
October 18, 2004 - 10am Central time zone
Reviewer: veera from Rockville, MD
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.
Followup October 18, 2004 - 11am Central time zone:
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.
October 18, 2004 - 11am Central time zone
Reviewer: veera from rockville, MD
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.
Followup October 18, 2004 - 1pm Central time zone:
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...
November 5, 2004 - 9am Central time zone
Reviewer: Ajeeet
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
Followup November 5, 2004 - 4pm Central time zone:
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?
March 24, 2005 - 4am Central time zone
Reviewer: max
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?
Followup March 24, 2005 - 8am Central time zone:
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
April 19, 2005 - 11am Central time zone
Reviewer: Surjam
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
Followup April 19, 2005 - 12pm Central time zone:
no, you would need to break that code out into another procedure.
Autonomous transaction refers to data in previous context
December 19, 2005 - 4pm Central time zone
Reviewer: Salaam Yitbarek from Ottawa, ON, Canada
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.
Followup December 19, 2005 - 4pm Central time zone:
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
December 20, 2005 - 8am Central time zone
Reviewer: Salaam Yitbarek from Ottawa, ON Canada
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.
Followup December 20, 2005 - 9am Central time zone:
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
December 20, 2005 - 8am Central time zone
Reviewer: Salaam Yitbarek from Ottawa, ON Canada
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
December 21, 2005 - 8am Central time zone
Reviewer: Salaam Yitbarek from Ottawa, ON Canada
Yes, I sounds like a good way to go for now.
Thanks!
ok
January 5, 2006 - 6am Central time zone
Reviewer: Raju from Bangalore,India
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
Followup January 5, 2006 - 10am Central time zone:
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")
January 5, 2006 - 11am Central time zone
Reviewer: A reader
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)
Followup January 5, 2006 - 11am Central time zone:
not sure what you are referring to?
Rollback In case of an exception in automous trans?
May 8, 2006 - 10am Central time zone
Reviewer: Nick from NYC
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;
Followup May 8, 2006 - 10am Central time zone:
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
May 8, 2006 - 11am Central time zone
Reviewer: Nick from NYC
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.
Followup May 8, 2006 - 1pm Central time zone:
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
May 10, 2006 - 9am Central time zone
Reviewer: Mousumi Ghosh from India
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
Followup May 10, 2006 - 10am Central time zone:
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, http://tkyte.blogspot.com/2005/07/how-cool-is-this.html but prior to that, you'd have to procedurally process the data using bulk collect and forall
inserts with SAVE EXCEPTIONS.
SAVEPOINT
May 10, 2006 - 10am Central time zone
Reviewer: P.Karthick from India
"Is is possbile to declare autonomous transaction for a part of the procedure?...."
for the question Surjam asked cant we use SAVEPOINT tom.
Followup May 11, 2006 - 7am Central time zone:
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?
May 11, 2006 - 5am Central time zone
Reviewer: Dawn from UK
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...!
Followup May 11, 2006 - 7pm Central time zone:
That is an entirely proper use of an autonomous transaction.
9.2
September 11, 2006 - 8am Central time zone
Reviewer: A reader
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?
Followup September 11, 2006 - 10am Central time zone:
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
September 12, 2006 - 1am Central time zone
Reviewer: Arindam Mukherjee from Kolkata, India
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?
Followup September 12, 2006 - 8am Central time zone:
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.
so, in the procedure, P1 will exit with an unhandled exception - but p2 catches that exception so
when we run it, we'll see:
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
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:
ops$tkyte%ORA9IR2> select * from t;
MSG
------------------------------
before anything
Note that the STATEMENTs work is rolled back, NOT the transaction
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
September 12, 2006 - 3am Central time zone
Reviewer: A reader
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.
Followup September 12, 2006 - 8am Central time zone:
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.
September 12, 2006 - 4pm Central time zone
Reviewer: A reader
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?
Followup September 13, 2006 - 7am Central time zone:
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.
September 13, 2006 - 2pm Central time zone
Reviewer: A reader
Sorry, point taken.
Autonomous Session ?
April 17, 2007 - 11pm Central time zone
Reviewer: Yogesh Purabiya from India
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 ?
Followup April 18, 2007 - 12pm Central time zone:
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
April 18, 2007 - 11pm Central time zone
Reviewer: Yogesh Purabiya from India
Thanks, as ususal, for the kind support.
pragma autonomous_transaction
May 2, 2007 - 10am Central time zone
Reviewer: Sanjeev VIbuthi from India
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
Followup May 2, 2007 - 5pm Central time zone:
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"
September 22, 2007 - 8pm Central time zone
Reviewer: karlo from NY
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;
Followup September 26, 2007 - 1pm Central time zone:
:( indeed.
oops sorry page 683 not 863
September 22, 2007 - 8pm Central time zone
Reviewer: karlo from NY
oops sorry I made a typo -
it's page 683 of the book
transaction
February 5, 2008 - 7pm Central time zone
Reviewer: A reader
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?
Followup February 5, 2008 - 9pm Central time zone:
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
April 17, 2008 - 11pm Central time zone
Reviewer: A reader
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;
transaction
April 18, 2008 - 9am Central time zone
Reviewer: A reader
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.
Followup April 18, 2008 - 10am Central time zone:
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
April 18, 2008 - 11am Central time zone
Reviewer: A reader
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?
Followup April 18, 2008 - 2pm Central time zone:
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
April 19, 2008 - 6pm Central time zone
Reviewer: A reader
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;
Followup April 23, 2008 - 4pm Central time zone:
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
April 19, 2008 - 6pm Central time zone
Reviewer: A reader
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,
Followup April 23, 2008 - 4pm Central time zone:
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
April 23, 2008 - 7pm Central time zone
Reviewer: A reader
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.
Followup April 28, 2008 - 10am Central time zone:
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
May 23, 2008 - 9am Central time zone
Reviewer: A reader
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
Followup May 23, 2008 - 4pm Central time zone:
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
May 23, 2008 - 5pm Central time zone
Reviewer: A reader
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.
Followup May 23, 2008 - 6pm Central time zone:
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
May 23, 2008 - 11pm Central time zone
Reviewer: A reader
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;
Followup May 24, 2008 - 6pm Central time zone:
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
May 25, 2008 - 1pm Central time zone
Reviewer: Mohamed Houri from France
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.
Followup May 27, 2008 - 7am Central time zone:
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
June 2, 2008 - 8pm Central time zone
Reviewer: A reader
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;
Followup June 3, 2008 - 10am Central time zone:
of course, with savepoints you would NOT BE USING commit; and rollback; like that - if you do, you end the "savepoint"
June 3, 2008 - 10am Central time zone
Reviewer: Mohamed from France
You may have this error in this case
Others :ORA-01086: savepoint 'FOO' never established
|