Re: Stupid Oracle question

Bruce Johnson wrote:
> 
> On May 6, 2010, at 11:34 AM, John Scoles wrote:
> 
>> Bruce Johnson wrote:
>>
>> Depends if you have |AutoCommit| on or not and if you DB and DBD
>> friver can do a rollback.
> 
> 
> I've explicitly turned autocommit off, so I can roll back transactions
> if an error occurs.

You do not /need/ to do that. You can leave AutoCommit turned on and
when you want to start a txn you issue a begin_work then a
commit/rollback. With some DBDs if you disable AutoCommit you'd need to
commit everything including selects which can be a bit annoying. So in
general with DBD::Oracle you can:

connect # AutoCommit is on by default
# set RaiseError => 1
eval {
  $dbh->begin_work
  # do something in the txn
};
if ($@) {
  $dbh->rollback or die "Failed to rollback - perhaps add DBI->err here";
} else {
  $dbh->commit or die "Failed to commit";
}

Please note - the above is simplified - I don't personally do that and
generally use Try::Tiny to avoid issues with $@.

> In the old Oraperl syntax it's:
> 
> if ($ora_errstr){
>     print "$ora_errstr occurred with $statement";
>     &ora_rollback($dbh);
>     }
> &ora_commit($dbh);
> 
> I'm redoing some old scripts to use DBI instead, so I'm guessing the
> equivalent DBI code is:
> 
> if ($ora_errstr){
>     print "$ora_errstr occurred with $statement";
>     $dbh->rollback();
>     }
> $dbh->commit();
> 

Didn't use Oraperl so difficult to say - my example should work though
without having to examine any errors so long as RaiseError is enabled on
the connection handle.

Martin