General
cancel
Showing results for 
Search instead for 
Did you mean: 

is the following statement true??

Prabhu_7
Frequent Advisor

is the following statement true??

I'm having commit statements within my procedure, is the following statement true??

"We usually should not be having commits inside the procedures/packages unless we have large transactions and not enough rollback seg. The commit or rollback has to be invoked form the calling process. "

I thought only in cursors we should not use commits!!!!

Thanks
3 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: is the following statement true??

The only possible answer to this question is "It depends". Unlike commits in cursors defined as SELECT FOR UPDATE where any fetches after a COMMIT statement will fail, your use of commits inside a procedure is optional and depends upon the intent of the software designer. For example, you might intend that anytime your procudure is called that the work done is immediately available to other Oracle instances or you might wish to ensure that all locks are released -- in those cases COMMIT's are valid uses. On the other hand, you probably would not want, as a side effect, a COMMIT within your procedure to actually trigger a commit of transactions made before the call to the procedure itself. Thus, the generally better approach is to avoid the commits within procedures but, again, "It depends."
If it ain't broke, I can fix that.
H.Merijn Brand (procura
Honored Contributor

Re: is the following statement true??

And on some databases COMMIT's invalidate open cursors and/or do not guarantee that the still open cursor is at the same position.

(temporary) AUTO-COMMIT might help in those situations.

Enjoy, have FUN! H.Merijn
Enjoy, Have FUN! H.Merijn
Prabhu_7
Frequent Advisor

Re: is the following statement true??

Thanks a lot people.