Re: Help: DBI in oracle

From: Anurag Varma <avdbi_at_nospam.hotmail.com>
Date: Mon, 24 Sep 2001 18:24:36 GMT
Message-ID: <ETKr7.43966$z7.10819681@news02.optonline.net>

> > my $dbh = DBI->connect ( 'dbi:Oracle:test',
> > 'test',
> > 'test',
> > {
> > PrintError => 1,
> > RaiseError => 1,
> > AutoCommit => 1

                                      ^^^^^^^^^^^^^^^


> >                         }

> >                         ) || die "Database Connection not made

$DBI::errstr" ;

You have set AutoCommit on and are trying to use a rollback segment? Turn it off (AutoCommit => 0) and then try. Be sure to do an explicit commit when you are done with
the transaction.

Anurag

"Adam Hapworth" <hap_at_mikomi.org> wrote in message news:a6cb04db.0109240830.4ea42f30_at_posting.google.com... > u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote in message news:<l.1001014491.1000518798@[64.94.198.252]>...
> > Hi, I am posting here because I know a lot of good DBAs are also good
> > perl programmer. I have a perl scripts which I want to use only one
rollback segment.
> > the script is like the follow:
> > _----------------------------------------------------------------
> > my $dbh = DBI->connect ( 'dbi:Oracle:test',
> > 'test',
> > 'test',
> > {
> > PrintError => 1,
> > RaiseError => 1,
> > AutoCommit => 1
> > }
> > ) || die "Database Connection not made
$DBI::errstr" ;

> >

> > #increate day in a month
> > my $count1 = 1;
> > while ($count1 < 32)
> > {
> > my $T1="to_date('2001/1/$count1 00:00:00','yyyy/mm/dd
> > hh24:mi:ss')";
> > my $T2="to_date('2001/1/$count1 23:59:59','yyyy/mm/dd
> > hh24:mi:ss')";

>
> TO_DATE syntax in a sql statment would look  like this
> "select to_date('2001/1/$count1 00:00:00','yyyy/mm/dd hh24:mi:ss')
> from dual;"
>


> >          $count1 = $count1 + 1;

> >


> >    # set rollback segment

> >       my $sql = qq{ set transaction use rollback segment RBS0 };

> >                 $dbh->do ( $sql );

> >


> >       #move data from test to archtest

> >                 my $sql = qq{

> >    INSERT INTO table1 select * from table2

> >         where polltime > $T1 and polltime < $T2};

> >         $dbh->do ( $sql );

> >         }

> > $dbh->disconnect();
> > ----------------------------------------------------------------
> > But the first sql does not seem to work, the transaction did not use
> > rollback segment I specified.

> >

> > Could somebody help me out?

> >

> > Thanks for your help

>
> That may be the problem but I do question your logic in the while.
>  while ($count1 > 32)
> will work great for months that have 31 days in them but say febuary
> with only 28 will give you an error and no rows in oracle for the last
> three days (given it isn't a leap year).  Also not sure how DBI
> handles Oracle transactions.  If Dbi commits each statement then you
> will not be able to rollback like you are thinking.  I am not a Oracle
> pro yet but getting there so I may be slightly off.
>
> Adam

Received on Mon Sep 24 2001 - 13:24:36 CDT