is the following statement true??

[Prabhu_7]

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

[A. Clay Stephenson]

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."

[H.Merijn Brand (procura]

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

[Prabhu_7]

Thanks a lot people.