Home » Other » General » Where else to use AUTONOMOUS TRANSACTION (Generic)
Where else to use AUTONOMOUS TRANSACTION [message #630107] Sat, 20 December 2014 09:05 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Hi,

The below is just for knowledge sharing.

All the time I am reading posts regarding AUTONOMOUS TRANSACTION, and it's always suggested, don't use AUTONOMOUS TRANSACTION anywhere except ERROR LOGGING, as it may cause your data to be in-consistent, and integrity issues may arise.

In an interview someone asked me two questions consecutively-

1. Have you ever used pipelined function in Oracle?
2. What are AUTONOMOUS_TRANSACTIONS?

I think he wanted to listen, why and how to use AUTONOMOUS TRANSACTION with PIPELINE functions.

I will suggest to read below:

Combining PIPE ROW with AUTONOMOUS_TRANSACTION

Regards,
Manu

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630110 is a reply to message #630107] Sat, 20 December 2014 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

OK, so, what is the point?

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630112 is a reply to message #630110] Sat, 20 December 2014 09:40 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
And with this comment, you have achieved 60K responses.

I just found something, which was new to me, and thought to share. I already written in the beginning of the post-

The below is just for knowledge sharing.

And below gives the answer of your question exactly. Razz Very Happy

https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=what%20is%20point%20wikipedia

Manu

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630113 is a reply to message #630112] Sat, 20 December 2014 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You could also all and every page of the documentation, I bet there are thousand that contain things you do not know. Grin

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630114 is a reply to message #630113] Sat, 20 December 2014 09:53 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Betting is prohibited here...
Have to come to France sometime to bet on something, where there are also my chances to win... Razz

I just wrote that, because I used it few times, without knowing the use cases, someone asked, and I did.
So where else I can use AUTONOMOUS TRANSACTION?

Thanks,
Manu

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630115 is a reply to message #630114] Sat, 20 December 2014 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You can use it everywhere the syntax allows you to do it.
Now the question is: should you?

And so is my question, what do you think you have to post this, what is your point to post it, why do you think it has any importance?

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630116 is a reply to message #630115] Sat, 20 December 2014 10:11 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
I can't debate on this small thing. It has less importance, that's why I posted in General forum, not in SQL.
It's just for information, so anybody can read it, whoever want to.

My question should be,

"Where else should I use AUTONOMOUS TRANSACTION"

But I think, I will find out over time, can you please delete this topic.

I will really appreciate that.

Manu

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630117 is a reply to message #630116] Sat, 20 December 2014 10:25 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I use it in a function which returns sequential invoice numbers. As they *have to* be gapeless, a sequence (as an Oracle object) isn't used in that case. The last invoice number is stored in a table. That function SELECTS FOR UPDATE, adds 1 to the last invoice number, commits and returns the next value.

As of deleting this topic, that's not going to happen, I'm afraid.

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630118 is a reply to message #630116] Sat, 20 December 2014 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

But what where I wanted to go is "is there any (real) case where the section you pointed has to be apply?".
This is the real question. Referencing something that is never needed is useless, just a waste of resources. Now the topic comes to a real interesting point: "Is this of real interest? Is there any case where pipelined function and autonomous_transaction are interleaved in such a way that you must commit or rollback inside this function?". This is interesting.
I exclude implicit commit/rollback due to DDL, half of my pipelined functions are built on this to follow DDL operations.

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630119 is a reply to message #630117] Sat, 20 December 2014 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Littlefoot wrote on Sat, 20 December 2014 17:25
I use it in a function which returns sequential invoice numbers. As they *have to* be gapeless, a sequence (as an Oracle object) isn't used in that case. The last invoice number is stored in a table. That function SELECTS FOR UPDATE, adds 1 to the last invoice number, commits and returns the next value.

As of deleting this topic, that's not going to happen, I'm afraid.


And what happens if the invoice is cancelled afterwards? Is there any gap in the numbers then?
And why is it a pipelined function?

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630120 is a reply to message #630119] Sat, 20 December 2014 10:49 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Quote:
And what happens if the invoice is cancelled afterwards?
This is something I've had to deal with, too. Auditors do NOT like gaps in business document numbes, and in some jurisdictions it may even be illegal in some circumstances. A cancelled invoice is not te same as a missing invoice. As DBAs or developers, we just to accept the instructions, silly though they may be.

Manu, I think the best way to spread knowledge (and yes, the topic you found is interesting) is to write it up as a blog article. Why not do that?

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630129 is a reply to message #630120] Sat, 20 December 2014 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Interesting, so there is no rollback in their application, only commits.

Note that this topic (or at least the link) was about autonomous transaction AND pipelined functions.

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630130 is a reply to message #630129] Sat, 20 December 2014 12:27 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Thanks Littlefoot and Michel.

For Littlefoot,
I am able to understand fully, as may be I am not aware, how invoice sequences are used further. I understood, select for update will lock it, so that no other transactions can use it, autonomous will commit regardless of the main calling transaction, but why it's pipelined.

The thing I have in my mind regarding pipelined functions is that on the fly processing, without getting complete result set in one go. As soon as you will get the data you will start processing it further. Like in data warehouses, where there are many intermediate transformations dependent on each other, and we don't really want to wait for a stage to be complete, there we use pipelined function.

I never wrote a blog, but let me try this for very first time (may be around new year).

For Michael,

I read many things without any use, and when the time comes, I at least know the direction in which I have to explore.

Combining PIPE ROW with AUTONOMOUS_TRANSACTION

A restriction on combining table functions and PRAGMA AUTONOMOUS_TRANSACTION has been introduced in the 10g (10.1) release because table functions pass control back and forth to a calling routine as rows are produced. If a table function is part of an autonomous transaction, it must COMMIT or ROLLBACK before each PIPE ROW statement to avoid an error in the calling subprogram.


I read that, but I don't understand that. Requesting you, Can you give me any simple test case/scenario, where I have to have to use this (AUTONOMOUS TRANSACTION with PIPELINED FUNCTION), and I will prepare data and try to code the scenario.

P.S You guys are always helpful, but the way you help is sometimes odd. Very Happy

Thanks,
Manu

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630131 is a reply to message #630130] Sat, 20 December 2014 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
there we use pipelined function.


I don't see the implication (logically speaking).

Quote:
Can you give me any simple test case/scenario, where I have to have to use this (AUTONOMOUS TRANSACTION with PIPELINED FUNCTION), and I will prepare data and try to code the scenario.


This is exactly my question what could be such scenario!

As I said, I only use pipelined function and autonomous_transaction for the same things than other functions: logging or DDL, so DDL implies implicit commit and if you log, you commit what you log, nothing specific there.

Report message to a moderator

Re: Where else to use AUTONOMOUS TRANSACTION [message #630157 is a reply to message #630131] Sun, 21 December 2014 14:27 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never said that my function is pipelined. Because, it is not.

It was just an example of another AUTONOMOUS_TRANSACTION usage.

Report message to a moderator

Previous Topic: How to get Oracle 9i client for Linux 32bit
Next Topic: oracle 11g on Windows server 2003 - 64 bit
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 21:50:44 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.