Home » SQL & PL/SQL » SQL & PL/SQL » How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function.
How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function. [message #223082] Wed, 07 March 2007 08:40 Go to next message
Jegathees
Messages: 8
Registered: December 2005
Location: Coimbatore
Junior Member
Hi Friends,

I created One table ‘Student’ i in our Oracle database with the fields ID, NAME, BIRTHYEAR, BIRTHMONTH, READFLAG. I inserted some records also. I have written one function for UPDATE operation using table function concept. It works also fine. But, one doubt we have. The AUTONOMOUS_TRANSACTION function works independently. Then, How will we ensure LUW is working correctly for transactions when use the below function ?

I give the full coding below. You can understand the idea I used.
------------------------------------------------------------
CREATE TYPE object_row_type AS OBJECT (
ID VARCHAR2(10),
NAME VARCHAR2(10),
BIRTHYEAR VARCHAR2(10),
BIRTHMONTH VARCHAR2(2),
READFLAG CHAR(1))
);
------------------------------------------------------------
CREATE TYPE object_table_type AS TABLE OF object_row_type;
------------------------------------------------------------
CREATE OR REPLACE FUNCTION student_new
RETURN object_table_type PIPELINED IS
TYPE ref0 IS REF CURSOR;
cur0 ref0;
v_ret number;
out_rec object_row_type := object_row_type(NULL, NULL, NULL, NULL, NULL);
BEGIN
OPEN cur0 FOR SELECT * FROM student WHERE readflag = ' ';
LOOP
FETCH cur0 INTO out_rec.ID, out_rec.NAME, out_rec.BIRTHYEAR, out_rec.BIRTHMONTH, out_rec.READFLAG;
v_ret:= update_stud (out_rec.ID);
EXIT WHEN cur0%NOTFOUND;
PIPE ROW(out_rec);
END LOOP;
CLOSE cur0;
RETURN;
END student_new;
/
---------------------------------------------------------------

CREATE OR REPLACE FUNCTION update_stud(stud_id IN varchar2)
return number AS
pragma autonomous_transaction;
BEGIN
UPDATE student SET readflag = 'Y' where ID = stud_id;
COMMIT;
RETURN 1;
END;
---------------------------------------------------------------

Thanks in advance.

Kind Regards,
Jegatheeswaran P
Re: How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function. [message #223086 is a reply to message #223082] Wed, 07 March 2007 08:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean by 'LUW'?

When you select from this function, you will not see the changes performed by the autonomous transaction, if that's what you're asking.
Re: How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function. [message #223116 is a reply to message #223082] Wed, 07 March 2007 10:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
BIRTHYEAR VARCHAR2(10),
BIRTHMONTH VARCHAR2(2),

Baaaaad idea! Be ready for some headaches.
Store dates in fields of datatype date, so store birthday in one single column of type date.
If you need the year or the month, you can always extract it from that single field.

Furthermore, normally, you want the calling procedure/form/whatever to handle the transaction. Don't commit that deep.
Tip: Forget about autonomous transactions, except for debugging/logging purposes. Autonomous transactions in other places are almost always the result of bad design and the cause of hard-to-find errors.
Re: How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function. [message #223200 is a reply to message #223086] Thu, 08 March 2007 00:00 Go to previous messageGo to next message
Jegathees
Messages: 8
Registered: December 2005
Location: Coimbatore
Junior Member
Hi

LUW means Logical unit of work i.e Consistency of transaction. In Autonomous Functions, how will we ensure the consistency of transaction ? (Example: Transfer of funds from one account to another account, debit the account here and credit the account there. This is a complete transaction. If we implement this using AUTONOMOUS function, will it work consistently ?)

Kind regards,
Jeg
Re: How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function. [message #223232 is a reply to message #223200] Thu, 08 March 2007 01:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think I answered that. Autonomous transactions don't work well with the 'luw's that you describe.
Re: How will we ensure LUW is working correct in PRAGMA AUTONOMOUS_TRANSACTION function. [message #223239 is a reply to message #223200] Thu, 08 March 2007 02:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As Frank has said, Autonomous transactions don't fin into a LUW viewpoint.
If you rollback your transaction, the changes made by the autonomous transactions don't get rolled back.
Previous Topic: Multiple rows into Single row on Oracle 8i
Next Topic: count function
Goto Forum:
  


Current Time: Thu Dec 08 23:55:29 CST 2016

Total time taken to generate the page: 0.06162 seconds