Re: Help: DBI in oracle

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 - 11:30:56 CDT