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?
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?
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?
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
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
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
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
September 22, 2004 - 7:47 am UTC
transaction
A reader, April 21, 2008 - 11:20 pm UTC