Solved

No Begin Trans in Oracle??

Posted on 2004-08-03
9
6,088 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:StevenLogic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11701951
If you don't use commit then all changes will be lost.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11702425
to start a transaction use a savepoint.


savepoint a_bridge_too_far;

update x set col1='Wow';

rollback to a_bridge_too_far;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11702443
if you do not commit changes are lost when connection is closed.  Also changes are not visible to other connections.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:StevenLogic
ID: 11703125
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
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 250 total points
ID: 11703333
It depends what you are using as your interface and ODBC connection might autocommit as default.

exit command in sql*plus will commit and return to OS$ by default.

ie exit commit; is same as exit; or exit rollback; if you want to discard changes.

You don't need a begin transaction you get one for free on the first dml statement.
0
 

Author Comment

by:StevenLogic
ID: 11703809
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 11707110
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.
0
 
LVL 11

Expert Comment

by:vc01778
ID: 11708390
"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
0
 

Author Comment

by:StevenLogic
ID: 11713911
Thanks everyone for your help.  I'll do some experiments to determine what ADO is doing.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
run sql script from putty 4 179
Alternative to GTT for a temp table for further reuse in Oracle 8 46
oracle date format checking 7 33
return value based on substr 10 48
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question