OraFAQ Forum: SQL & PL/SQL » AutoCommit in Stored Proc

Home » SQL & PL/SQL » SQL & PL/SQL » AutoCommit in Stored Proc (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

AutoCommit in Stored Proc [message #609908]

Thu, 13 March 2014 16:20

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi,

can i do

set autocommit on;
set autocommit 10000;

in a stored procedure, and i have to schedule this proc through an event
that accept just the PROC name, that's it no SQL clause before that..


CREATE OR REPLACE PROCEDURE COPY_PAYMTS_TO_HISTORY (Days_To_Retain Number)
IS
  V_ID  NUMBER(10) := 0;
  V_EVENT_NAME VARCHAR2(20) := 'COPY_PYMT';
Bein
....

End;/

I need to put autocommit in between insert/delete statements please.

Thanks

Report message to a moderator

Re: AutoCommit in Stored Proc [message #609911 is a reply to message #609908]

Thu, 13 March 2014 18:37

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

No. PL/SQL has no concept of autocommit. If you want a procedure to commit at a certain point then you need to write a commit statement in it.

Report message to a moderator

Re: AutoCommit in Stored Proc [message #609915 is a reply to message #609911]

Thu, 13 March 2014 22:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I need to put autocommit in between insert/delete statements please.
so now what will you do, since it is not possible to actually implement your "requirement"?
realize that row by row processing results in slow by slow application.

Report message to a moderator

Re: AutoCommit in Stored Proc [message #609924 is a reply to message #609915]

Thu, 13 March 2014 23:36

vslabs
Messages: 26
Registered: March 2014
Location: Cape Town

Junior Member

Auto commit in Oracle PL/SQL is idiotic.

There is NO valid reason for it.

In Oracle, there are no lock overheads ito server resources. A billion locks have the same memory overheads as zero locks - none. In Oracle, there is no lock escalation of row locks to page locks. Thus none of the SQL-Server reasons for auto commit in Oracle is valid.

What does auto commit do? It increases the amount of work that needs to be done. More work means more resources need to be used. It often violates ANSI SQL standards of actually fetching across commits (which in Oracle results in a snapshot too old error).

It violates the integrity of the business transaction - as it now consists of 100's of auto commit database transactions of which some may have been successful, and others may have failed.

There is not a single valid reason for auto commit in PL/SQL. Using auto commit is a epic fail on the developer's part.

Report message to a moderator

Re: AutoCommit in Stored Proc [message #610084 is a reply to message #609924]

Mon, 17 March 2014 08:12

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

DML statements (INSERT/DELETE/UPDATE/MERGE) don't do an auto commit in PL/SQL. DDL statements do commit (ALTER/CREATE etc) and this will happen even if something failed. If you're running EXECUTE IMMEDIATE like dynamic statement that runs a DDL, this will also commit your transaction. And its been like that [and will remain] since 2000

clienit interfac like SQL*Plus have an auto commit feature that can be turned off/on , look for it in the client documentations. Something like

SET AUTOCOMMIT OFF
You can see the current status of this variable

SHOW AUTCOMMIT
and that will tell you whether its on/off .

Report message to a moderator

Re: AutoCommit in Stored Proc [message #610085 is a reply to message #610084]

Mon, 17 March 2014 08:25

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It should be pointed out that sqlplus autocommit will not commit individual dmls in a stored proc.
It will only commit the actions of the stored proc as a whole, assuming the proc is run direct from sqlplus.
This isn't what the OP wants and from his original post it appears he already knows about it.

As vslabs says this level of commiting is almost certainly counter productive.

Report message to a moderator

Previous Topic:	File/Folder write permission in Oracle via Java Policy
Next Topic:	merge 2 rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 11:03:20 CDT 2024