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 ...
do the ddl;
when others then
rollback to savepoint <whatever>;
... 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?
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.
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?
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 :
perform any appropriate pre-DDL trigger code;
do the ddl;
perform any appropriate post-DDL trigger code;
when others then
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
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?
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
i would like to know the time taken to commit 100 records and a million records.
February 26, 2004 - 10:20 am UTC
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
Thanks for replying to my previous question.
please tell me how do i convert a number to bit mode.
how will execute procedure with ref cursor as a out parameter
T.V Ranagadham, September 21, 2004 - 8:31 am UTC
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
September 21, 2004 - 8:33 am UTC
execute procedure with ref cursor parameter
T.V Rangadham, September 22, 2004 - 2:12 am UTC
i have created Peoplecur type object in package.
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.
p_file_date IN date,
PeopleCursor out Peoplecur
OPEN PeopleCursor FOR
SELECT file_date, close_amt FROM rivet_cash_contracts where file_date=p_file_date;
Thanks & regards
September 22, 2004 - 7:47 am UTC
A reader, April 21, 2008 - 11:20 pm UTC