StevenLogic
asked on
No Begin Trans in Oracle??
Hi,
I noticed that there is no begin trans in Oracle. Is this true? Therefore, does this mean that there is an implicit begin trans, and if I do not do a commit at the end of a session that all of the information will be lost once the database is shut down and restarted?
Thanks for clarifying in advance,
-StevenLogic
I noticed that there is no begin trans in Oracle. Is this true? Therefore, does this mean that there is an implicit begin trans, and if I do not do a commit at the end of a session that all of the information will be lost once the database is shut down and restarted?
Thanks for clarifying in advance,
-StevenLogic
If you don't use commit then all changes will be lost.
to start a transaction use a savepoint.
savepoint a_bridge_too_far;
update x set col1='Wow';
rollback to a_bridge_too_far;
savepoint a_bridge_too_far;
update x set col1='Wow';
rollback to a_bridge_too_far;
if you do not commit changes are lost when connection is closed. Also changes are not visible to other connections.
ASKER
That's what I thought, but currently I have no commit/save point/rollback, and information is saved after connection is closed, and is available to other users. If I can get away without a transaction, it would be good, since this process doesn't really require it.
Save points are different to Begin trans in that you can have multiple save points. Under Oracle, does save point take the place of a Begin Trans?
Thanks,
-StevenLogic
Save points are different to Begin trans in that you can have multiple save points. Under Oracle, does save point take the place of a Begin Trans?
Thanks,
-StevenLogic
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks earthman2. So ADO must be doing the work for me. I was confused by what was hapenning where 'till now. Am using ADO with a stored procedure.
So to summarise, no commit, then no changes available after session closed.
Thanks very much.
So to summarise, no commit, then no changes available after session closed.
Thanks very much.
If you have experience with SQL Server (or another SQL-based database as I'm assuming you do, since you know what "begin trans" means) but are new to Oracle, you should be aware that there are many differences between SQL Server and Oracle. Some of the biggest differences are in these areas:
1. how nulls are handled
2. how dates are handled
3. how record-locking is handled
4. whether stored procedures return result sets (arrays) or not
Many people who are new to Oracle and expect it to work like SQL Server in these areas are unpleasantly surprised. I'm not saying the Oracle way or the SQL Server way is better or worse in these areas, I'm just reminding you that they are different and if you write code based on how things work in one database, the results may be different in the other.
1. how nulls are handled
2. how dates are handled
3. how record-locking is handled
4. whether stored procedures return result sets (arrays) or not
Many people who are new to Oracle and expect it to work like SQL Server in these areas are unpleasantly surprised. I'm not saying the Oracle way or the SQL Server way is better or worse in these areas, I'm just reminding you that they are different and if you write code based on how things work in one database, the results may be different in the other.
"Under Oracle, does save point take the place of a Begin Trans?
"
No, it plays a different role.
Under Oracle, all transactions are started implicitly (similar to ms sql 'set implicit_transaction on') and you do need an expliit commit/roolback in order to finish a transaction.
VC
"
No, it plays a different role.
Under Oracle, all transactions are started implicitly (similar to ms sql 'set implicit_transaction on') and you do need an expliit commit/roolback in order to finish a transaction.
VC
ASKER
Thanks everyone for your help. I'll do some experiments to determine what ADO is doing.