Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ashiq.

Asked: December 29, 2002 - 10:47 pm UTC

Last updated: September 22, 2004 - 7:47 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

As we know after every ddl oracle will do implicit commit.My question is ,why its committing for the ddl which is got failed.For example..


scott@RMSREAL> delete from emp1;

14 rows deleted.

scott@RMSREAL> drop table emp2;
drop table emp2
*
ERROR at line 1:
ORA-00942: table or view does not exist


scott@RMSREAL> select * from emp1;

no rows selected


Why its doing like this?Any reason for this?

thanx


and Tom said...

DDL is done conceptually like this:


begin
COMMIT;
do the ddl;
COMMIT;
exception
when others then
ROLLBACK;
raise;
end;


Now, if that first commit were not there AND you had outstanding work -- then we would roll back your changes as well as ours. So, rather then make it unpredicable -- it does "commit -- ddl -- commit | rollback"

You can use an autonomous transaction:
</code> http://asktom.oracle.com/~tkyte/autonomous/index.html <code>
to avoid the commit from affecting you alltogether.




Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ashiq, December 30, 2002 - 9:46 am UTC

Thanx for explaining me very detailed

Dave, December 30, 2002 - 11:05 am UTC

It seems like the pseudocode ...

begin
savepoint <whatever>;
do the ddl;
COMMIT;
exception
when others then
rollback to savepoint <whatever>;
raise;
end;

... would also work conceptually, and would not commit on failure of the DDL. Is there an ANSI reason for not implementing/permitting such a methodology, or some other rationale that you are aware of?

Tom Kyte
December 30, 2002 - 12:09 pm UTC

a user that gets blocked on the "do the ddl" inside the savepoint is blocked on the transaction -- not the "subtransaction". Hence it would block people on the data dictionary -- a place we cannot afford to get jammed up.

The data dictionary is "special" -- it drives the rest of the system. If portions of it get locked up for extended periods of time -- it could be deadly.

Good clarification

Cefers.br, December 30, 2002 - 1:17 pm UTC

Nice explanation. I´ve already heard about 2 implicit commits on DDL, but I wasn´t sure if this information was right. Thanks, Tom.

And just to emphasize, the implicit commits occur only for the DDL commands that change the data dictionary.

An ALTER SESSION SET NLS_DATE_FORMAT, for example, doesn´t do COMMIT/ROLLBACK.

Am I right, Tom?

Tom Kyte
December 30, 2002 - 1:28 pm UTC

correct -- alter session does not commit.

Just ddl that does stuff to the data dictionary.

Implicit commits and DDL triggers

Gary, December 30, 2002 - 5:15 pm UTC

Just to add, from looking at a trace file, it looks like it's more :

begin
COMMIT;
perform any appropriate pre-DDL trigger code;
do the ddl;
perform any appropriate post-DDL trigger code;
COMMIT;
exception
when others then
ROLLBACK;
raise;
end;


So it's probably advisable not to do anything big and horrible in a AFTER DDL trigger.


Why not autonomous transaction?

Cefers.br, June 23, 2003 - 5:44 pm UTC

Tom,

I was wondering why DDL statements are not executed inside an autonomous transaction (like sequences do), so they wouldn´t affect any pending user transaction...

Can you clarify?

Tom Kyte
June 24, 2003 - 7:34 am UTC

that would be as "confusing" as not doing it that way. anyway, you have atrans so if you want to, you can.

commit time for 1 million records

Rangadham, February 26, 2004 - 5:58 am UTC

hi Tom,

i would like to know the time taken to commit 100 records and a million records.

Thanks
Rangadham


Tom Kyte
February 26, 2004 - 10:20 am UTC

the same.

unless you commit 1,000,000 records 100 at a time -- then, it'll be 1,000 times larger or more.

if you have "Expert one on one Oracle" -- i actually cover this topic and give examples as well.

convertable function from number to bit

T.V.Rangadham, March 17, 2004 - 2:00 am UTC

hi Tom,

Thanks for replying to my previous question.

please tell me how do i convert a number to bit mode.

Thanks
Rangadham

Tom Kyte
March 17, 2004 - 7:27 am UTC

if by "bit mode" you mean "binary", see

</code> http://asktom.oracle.com/~tkyte/hexdec/index.html <code>

how will execute procedure with ref cursor as a out parameter

T.V Ranagadham, September 21, 2004 - 8:31 am UTC

Hi tom,

how will i execute procedure with ref cursor as a out parameter.

i have 2 parameters. one is in parameter and the other is out parameter which i have created as ref cursor. while executing that proc. i am getting following error
wrong number or types of aurguments calling in procedure.

pls give me reply
thanks
rangadham

Tom Kyte
September 21, 2004 - 8:33 am UTC

execute procedure with ref cursor parameter

T.V Rangadham, September 22, 2004 - 2:12 am UTC

hi Tom,

i have created Peoplecur type object in package.

like this

TYPE PeopleCur IS REF CURSOR RETURN c1%rowtype;

following code is my proc.
it was created successfully.
pls tell me how do i execute this proc.

PROCEDURE Get_values
(
p_file_date IN date,
PeopleCursor out Peoplecur
)
IS
begin
OPEN PeopleCursor FOR
SELECT file_date, close_amt FROM rivet_cash_contracts where file_date=p_file_date;

END Get_values;

Thanks & regards
Rangadham

Tom Kyte
September 22, 2004 - 7:47 am UTC

transaction

A reader, April 21, 2008 - 11:20 pm UTC