Looking at the Oracle 8 Server Concepts manual, a transaction is committed explicitly by a "commit" statement or implicity when a DDL statement is issued. My understanding is that a "create table" statement is a DDL statement. Thus an implied commit occurs at that point in your code and you can not roll back to a point in time before that.
-dpf- ----------------------- David P. Fannin Database Administrator [EMAIL PROTECTED] UM-Rolla Computing and Information Services FAX (573) 341-4216 URL http://www.umr.edu/~dpf PHONE (573) 341-4841 ----------------------- -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 3:31 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Re: Oracle DBI commit/rollback question... Mac, Ok, I've got a problem with rollback(), I think. I'm not sure it's a database problem or if it's my code, but here's the output from my DBI script, and the code itself is attached (NOTE - I intentionally used the wrong column in my delete statement to force an error): AUTO_COMMIT is set to: AUTO_COMMIT is set to: create table t_dave_test as select trade_date, host_cust_id from cm_trade_fact where trade_id = 210960459_compile_ This SQL Statement was Processed Successfully. Return Status: 1 (Rows Effected or Error Code if function or procedure) AUTO_COMMIT is set to: delete from t_dave_test where trade_id = 'WA339482'_compile_ This SQL Statement Produced an Error... Rollback Status: 1 Database Execution Error: DBD::Oracle::db do failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute) at test.pl line 154. ROLLING BACK TRANSACTION... Disconnected from Database and Terminating. I probably shouldn't say "ROLLING BACK TRANSACTION" because the "Rollback Status = 1". But I expect because AutoCommit = "" (I set it OFF) that the table I created in the first SQL statement would not exist. But it does!!!!!! So I don't know why it won't rollback. Can you offer any more suggestions? Is it perhaps a setting in Oracle which overrides DBI rollback()? -Dave- -----Original Message----- From: mchase Sent: Wednesday, February 06, 2002 5:58 PM To: Wren, David; dbi-users Cc: mchase Subject: Re: Re: Oracle DBI commit/rollback question... Please keep this on the list; I have added dbi-users to my response. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 06, 2002 14:41 Subject: RE: Re: Oracle DBI commit/rollback question... > Thanks Mac, but what I'm doing is looping different DML statements > through several calls to do() in the same database connection. You can prepare many statement handles with the same database handle. Then all you need is execute() at the appropriate points, not do() which includes prepare() as well. With placeholders, which I strongly recommend, a statement handle can be executed many times. > I'm calling commit() or rollback() (depending on results from eval) > after all DML statements have been passed, but before I disconnect. > > I'm wondering if only the last DML statement before error (or program > completion) gets committed or rolled back, or if all statements do. A transaction is a unit of work, all DML in the transaction gets committed or rolledback together. > -----Original Message----- > From: mchase > Sent: Wednesday, February 06, 2002 5:26 PM > To: Wren, David; dbi-users > Cc: mchase > Subject: Re: Oracle DBI commit/rollback question... > > Until your execute() or do() the statement hasn't occurred yet, so > there's > nothing to commit or rollback. > > Each time you commit or rollback, a new transaction starts. > > Transactions are session specific, so the transactions in two open > database > handles in the same program are independent of each other. > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, February 06, 2002 14:03 > Subject: Oracle DBI commit/rollback question... > > > > Does anybody know how Oracle8i handles transactions from DML > statements > > passed from a DBI database connection? > > > > Do the dbh->commit() and dbh->rollback() statements commit or rollback > > all DML instructions issued while the connection is open, or just > those > > which get passed by sth->do() or sth->execute()?
