Home » SQL & PL/SQL » SQL & PL/SQL » PRAGMA AUTONOMOUS_TRANSACTION; Not Working
PRAGMA AUTONOMOUS_TRANSACTION; Not Working [message #47757] |
Tue, 14 September 2004 13:11  |
saustin
Messages: 43 Registered: June 2003
|
Member |
|
|
Hi,
Have the following trigger in an 8i db that does NOT save records into a log table BARCODERECEIVER_LINE
if the PRAGMA AUTONOMOUS_TRANSACTION; and COMMIT; lines are included. The save works perfectly if they are commented out. No error is displayed. It just does not save the record.
Using the example in the 8i documentation it looks like it should.
CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;
Can anyone spot my oversight ? Just converted this db from 8.05 if that is of any help but i really think it has more to do with the AUTONOMOUS_TRANSACTION not being allowed in a select/insert block. Many Thanks, Steve.
DECLARE
mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN
INSERT INTO BARCODERECEIVER_LINE
(RECEIVER_ID, LINE_NO, PRINTED, QTY, PO, VENDOR, DESCRIPTION, PART, RECEIVED_DATE, PRINTER, EARLY, TRANSTIME)
SELECT
:NEWDATA.RECEIVER_ID,
:NEWDATA.LINE_NO,
'N',
:NEWDATA.RECEIVED_QTY,
:NEWDATA.PURC_ORDER_ID,
SYSADM.VENDOR.NAME,
SYSADM.PART.DESCRIPTION,
NVL(SYSADM.PURC_ORDER_LINE.PART_ID, SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID),
SYSADM.RECEIVER.RECEIVED_DATE,
DECODE(SYSADM.RECEIVER.USER_ID, 'CARL', 1, 'ORLANDO', 2, 'TONYA', 3, 'MATT', 'TOM', 3, 'SYSADM', 3),
'Y',
to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
FROM SYSADM.RECEIVER, SYSADM.PURC_ORDER_LINE, SYSADM.PART, SYSADM.PURCHASE_ORDER, SYSADM.VENDOR
WHERE :NEWDATA.RECEIVER_ID = SYSADM.RECEIVER.ID
AND :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID
AND SYSADM.PURC_ORDER_LINE.PART_ID = SYSADM.PART.ID (+)
AND SYSADM.RECEIVER.PURC_ORDER_ID = SYSADM.PURCHASE_ORDER.ID (+)
AND SYSADM.PURCHASE_ORDER.VENDOR_ID = SYSADM.VENDOR.ID (+)
AND SYSADM.RECEIVER.USER_ID IN ('ORLANDO', 'TONYA', 'MATT', 'TOM', 'SYSADM');
COMMIT;
--raise_application_error(-20000, 'Check with Purchasing please Exceeds Early Need Date');
END IF;
END;
|
|
|
|
|
Re: PRAGMA AUTONOMOUS_TRANSACTION; Not Working [message #47768 is a reply to message #47760] |
Wed, 15 September 2004 05:06   |
saustin
Messages: 43 Registered: June 2003
|
Member |
|
|
Hi Maaher,
Many thanks for the reply. Sorry, should have been more clear on what I am looking to accomplish. Yes this is a BEFORE inert trigger on the RECEIVER_LINE table. The ultimate goal is to PREVENT an update to RECEIVER_LINE and raise an error if a certain condition is met AND save a record of this to a log file table BARCODERECEIVER_LINE.
Also,
If I remove the PRAGMA...COMMIT; it will save the log file record BUT NOT if the error is raised.
|
|
|
Re: PRAGMA AUTONOMOUS_TRANSACTION; Not Working [message #47771 is a reply to message #47768] |
Wed, 15 September 2004 15:33   |
 |
Barbara Boehmer
Messages: 9105 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Perhaps this is a problem with 8i. It seems to work as desired in 9i as demonstrated below. Can you post a similar test in 8i that demonstrates the problem, complete with insert statement, whatever messages or errors you receive, and the resulting contents of the tables inserted into?
scott@ORA92> -- version info
scott@ORA92> SELECT banner FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
scott@ORA92> -- starting data:
scott@ORA92> SELECT * FROM receiver_line
2 /
no rows selected
scott@ORA92> SELECT * FROM purc_order_line
2 /
PROMISE_D LINE_NO PART_ID VENDOR_PART_ID PURC_ORDER_ID
--------- ---------- ---------- -------------- -------------
14-NOV-04 1 1 1 1
17-JUL-04 2 2 2 2
scott@ORA92> SELECT * FROM vendor
2 /
NAME ID
---------- ----------
1 1
2 2
scott@ORA92> SELECT * FROM part
2 /
DESCRIPTION ID
----------- ----------
1 1
2 2
scott@ORA92> SELECT * FROM purchase_order
2 /
ID VENDOR_ID
---------- ----------
1 1
2 2
scott@ORA92> SELECT * FROM receiver
2 /
RECEIVED_ USER_ID ID PURC_ORDER_ID
--------- ---------- ---------- -------------
14-NOV-04 ORLANDO 1 1
17-JUL-04 ORLANDO 2 2
scott@ORA92> SELECT * FROM barcodereceiver_line
2 /
no rows selected
scott@ORA92> -- trigger:
scott@ORA92> CREATE OR REPLACE TRIGGER your_trigger
2 BEFORE INSERT ON receiver_line
3 REFERENCING NEW AS NEWDATA
4 FOR EACH ROW
5 DECLARE
6 mDate scott.PURC_ORDER_LINE.PROMISE_DATE%type;
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 SELECT scott.PURC_ORDER_LINE.PROMISE_DATE
10 INTO mDate
11 FROM scott.PURC_ORDER_LINE
12 WHERE :NEWDATA.PURC_ORDER_LINE_NO = scott.PURC_ORDER_LINE.LINE_NO
13 AND :NEWDATA.PURC_ORDER_ID = scott.PURC_ORDER_LINE.PURC_ORDER_ID;
14 IF mDate > (SYSDATE + 30) THEN
15 INSERT INTO BARCODERECEIVER_LINE
16 (RECEIVER_ID,
17 LINE_NO,
18 PRINTED,
19 QTY,
20 PO,
21 VENDOR,
22 DESCRIPTION,
23 PART,
24 RECEIVED_DATE,
25 PRINTER,
26 EARLY,
27 TRANSTIME)
28 SELECT :NEWDATA.RECEIVER_ID,
29 :NEWDATA.LINE_NO,
30 'N',
31 :NEWDATA.RECEIVED_QTY,
32 :NEWDATA.PURC_ORDER_ID,
33 scott.VENDOR.NAME,
34 scott.PART.DESCRIPTION,
35 NVL (scott.PURC_ORDER_LINE.PART_ID, scott.PURC_ORDER_LINE.VENDOR_PART_ID),
36 scott.RECEIVER.RECEIVED_DATE,
37 DECODE(scott.RECEIVER.USER_ID,
38 'CARL' , 1,
39 'ORLANDO', 2,
40 'TONYA' , 3,
41 'MATT' , 3,
42 'TOM' , 3,
43 'scott' , 3),
44 'Y',
45 to_char (sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
46 FROM scott.RECEIVER,
47 scott.PURC_ORDER_LINE,
48 scott.PART,
49 scott.PURCHASE_ORDER,
50 scott.VENDOR
51 WHERE :NEWDATA.RECEIVER_ID = scott.RECEIVER.ID
52 AND :NEWDATA.PURC_ORDER_LINE_NO = scott.PURC_ORDER_LINE.LINE_NO
53 AND :NEWDATA.PURC_ORDER_ID = scott.PURC_ORDER_LINE.PURC_ORDER_ID
54 AND scott.PURC_ORDER_LINE.PART_ID = scott.PART.ID (+)
55 AND scott.RECEIVER.PURC_ORDER_ID = scott.PURCHASE_ORDER.ID (+)
56 AND scott.PURCHASE_ORDER.VENDOR_ID = scott.VENDOR.ID (+)
57 AND scott.RECEIVER.USER_ID IN ('ORLANDO', 'TONYA', 'MATT', 'TOM', 'scott');
58 COMMIT;
59 raise_application_error
60 (-20001, 'Check with Purchasing please Exceeds Early Need Date');
61 END IF;
62 END;
63 /
Trigger created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> -- test of inserts and results:
scott@ORA92> INSERT INTO receiver_line VALUES (2, 2, 2, 2, 2)
2 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> INSERT INTO receiver_line VALUES (1, 1, 1, 1, 1)
2 /
INSERT INTO receiver_line VALUES (1, 1, 1, 1, 1)
*
ERROR at line 1:
ORA-20001: Check with Purchasing please Exceeds Early Need Date
ORA-06512: at "SCOTT.YOUR_TRIGGER", line 55
ORA-04088: error during execution of trigger 'SCOTT.YOUR_TRIGGER'
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> SELECT * FROM receiver_line
2 /
PURC_ORDER_LINE_NO RECEIVER_ID LINE_NO RECEIVED_QTY PURC_ORDER_ID
------------------ ----------- ---------- ------------ -------------
2 2 2 2 2
scott@ORA92> SELECT * FROM barcodereceiver_line
2 /
RECEIVER_ID LINE_NO P QTY PO VENDOR DESCRIPTION PART RECEIVED_
----------- ---------- - ---------- ---------- ---------- ----------- ---------- ---------
PRINTER E TRANSTIME
---------- - ------------------------
1 1 N 1 1 1 1 1 14-NOV-04
2 Y Wed 15-Sep-2004 16:30:31
|
|
|
Re: PRAGMA AUTONOMOUS_TRANSACTION; Not Working [message #47773 is a reply to message #47768] |
Wed, 15 September 2004 21:33   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
From the Oracle forums:
----------------------------------------------------------------------
Isn't it because your autonomous transaction can't see the not yet committed data from the SELECT statement. It is, after all, a different transaction. Using the 'VALUES' in the insert does, on the other hand, work because we know the values of :NEW and :OLD in the trigger.
I ran a small test and the result is clear:
------------ BEGIN OF SCRIPT ------------
DROP TABLE t1
/
DROP TABLE t2
/
DROP TABLE t3
/
CREATE TABLE t1(id number, val VARCHAR2(200))
/
CREATE TABLE t2(id number, val VARCHAR2(200))
/
CREATE TABLE t3(id number, val VARCHAR2(200))
/
CREATE OR REPLACE TRIGGER t1_bri_t2 BEFORE INSERT ON t1
REFERENCING old AS old new AS new
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- Will result in 0 records, the sql environment of this
-- autonomous transaction doesn't know the newly created
-- but not yet committed record.
INSERT INTO t2( id
, val
)
SELECT id
, val
FROM t1
WHERE id = :NEW.id;
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER t1_bri_t3 BEFORE INSERT ON t1
REFERENCING old AS old new AS new
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- Will result in 1 record, the trigger
-- knows the :NEW values, no matter what transaction
-- we're in.
INSERT INTO t3( id
, val
)
VALUES (:NEW.id, :NEW.val);
COMMIT;
END;
/
col val format a40
INSERT INTO T1 VALUES(1,'Visible in T3 but not in T2')
/
PROMPT TABLE T1
PROMPT --------
SELECT id
, val
FROM t1
/
PROMPT TABLE T2
PROMPT --------
SELECT id
, val
FROM t2
/
PROMPT TABLE T3
PROMPT --------
SELECT id
, val
FROM t3
/
------------ END OF SCRIPT ------------
----------------------------------------------------------------------
As suggested in this reply, I think the problem you encounter is due to the fact that the SQL environment in your autonomous transaction isn't aware of the new record yet. It isn't yet commited and therefor not yet visible to other transactions. Now, this is way out of my field of expertise but isn't that because Oracle will first replace the :NEW variable by its value and parse that query to the SQL environment where no record is found (because it isn't yet committed)?
In the PL/SQL of your trigger, on the other hand, it knows the value of any :NEW variable and again, through binding, it will replace any :NEW by its value...
I hope you catch my drift, because I don't know how I can explain this more clear. Think about the 2 transactions and read consistency...
HTH,
MHE
|
|
|
Re: PRAGMA AUTONOMOUS_TRANSACTION; Not Working [message #47784 is a reply to message #47773] |
Thu, 16 September 2004 07:44   |
saustin
Messages: 43 Registered: June 2003
|
Member |
|
|
Hi Maaher,
Genius !!!! Many many thanks for this tip ! Indeed the SQL environment was not aware of the new transaction. It was no problem to re-write using VALUES. Here is the working trigger.
DECLARE
mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;
mVENDOR SYSADM.VENDOR.NAME%type;
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mPART SYSADM.PURC_ORDER_LINE.PART_ID%type;
mVPART SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID%type;
v_username varchar2(10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN
SELECT TRIM ('#' FROM user) INTO v_username FROM dual;
INSERT INTO BARCODERECEIVER_LINE (RECEIVER_ID, LINE_NO, RECEIVED_DATE, PRINTER, PRINTED, QTY, PO, EARLY, TRANSTIME, USERNAME)
VALUES ('', :NEWDATA.PURC_ORDER_LINE_NO, SYSDATE, DECODE(v_username, 'CARL', 1, 'ORLANDO', 2, 'TONYA', 3, 'MATT', 'TOM', 3, 'SYSADM', 3), 'N', :NEWDATA.RECEIVED_QTY, :NEWDATA.PURC_ORDER_ID,'Y', to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS'), v_username);
COMMIT;
raise_application_error(-20000, 'Check with Purchasing please Exceeds Early Need Date');
END IF;
END;
Thank you again ! Steve.
|
|
|
Re: PRAGMA AUTONOMOUS_TRANSACTION; Not Working [message #47785 is a reply to message #47771] |
Thu, 16 September 2004 07:55  |
saustin
Messages: 43 Registered: June 2003
|
Member |
|
|
Hi Barbara,
Many thanks for your kind and thought provoking response. Maaher (brilliant !)tested the VALUES vs SELECT insert logic in 8i and found a difference. I just re-wrote the trigger with VALUES (in 8i) and it now works well. Here is the trigger.
DECLARE
mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;
mVENDOR SYSADM.VENDOR.NAME%type;
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mPART SYSADM.PURC_ORDER_LINE.PART_ID%type;
mVPART SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID%type;
v_username varchar2(10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN
SELECT TRIM ('#' FROM user) INTO v_username FROM dual;
INSERT INTO BARCODERECEIVER_LINE (RECEIVER_ID, LINE_NO, RECEIVED_DATE, PRINTER, PRINTED, QTY, PO, EARLY, TRANSTIME, USERNAME)
VALUES ('', :NEWDATA.PURC_ORDER_LINE_NO, SYSDATE, DECODE(v_username, 'CARL', 1, 'ORLANDO', 2, 'TONYA', 3, 'MATT', 'TOM', 3, 'SYSADM', 3), 'N', :NEWDATA.RECEIVED_QTY, :NEWDATA.PURC_ORDER_ID,'Y', to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS'), v_username);
COMMIT;
raise_application_error(-20000, 'Check with Purchasing please Exceeds Early Need Date');
END IF;
END;
Am excited to try this (and the original version) on a 9i test box and see if it works.
Steve.
|
|
|
Goto Forum:
Current Time: Mon May 19 04:50:19 CDT 2025
|