I do what you originally did ($dbh->do("delete...") all the time on Oracle
8.1.7. The only difference I have is
my $dbh = DBI->connect("dbi:Oracle:database", "user", "pass",
{ RaiseError => 1, AutoCommit => 0 });
i.e. I explicitly set AutoCommit off. I always have done that, and don't
know what the default is. It sounds like AutoCommit is on. 42K commits
wouldn't be very pretty, but I would expect that some of the rows would
have been deleted. Have you tried trace()?
HTH,
Dave
On Nov 24, Mark Martin scribed:
> 49620 rows deleted in ~ 15 seconds.
> ( I have tried the delete from DBI on varying subsets of data all the way
> down to 1 record (the only delete that works))
>
> At 10:57 24/11/2004 -0500, David N Murray wrote:
> >How long does 'delete from table where year = 2003' take in SQLPlus?
> >
> >On Nov 24, Mark Martin scribed:
> >
> > > Michael,
> > > thanks for the quick response.
> > >
> > > "TABLE" was just an example.
> > >
> > > tried your error checking and nothing came up. So, went back to :
> > >
> > > 1. defining the SQL statement instead.
> > > 2. preparing the statement handler - my $sth = $dbh->prepare($sql)
> > > 3. executing - $sth->execute()
> > >
> > > .......instead of a $dbh->do.
> > >
> > > This didn't fix it, but allowed me to add print statements to see which
> > > parts of the procedure were working
> > >
> > > 1. Check
> > > 2. Check
> > > 3. NOTHING
> > >
> > > So, tried something else - modified the SQL statement so that the "DELETE"
> > > targets only one record. Hey Presto - it works!!
> > >
> > > Is this a problem with autocommitting on the DB or some sort of cursor
> > > problem??
> > >
> > > Regards,
> > > Mark
> > >
> > > At 06:40 24/11/2004 -0800, Michael A Chase tech wrote:
> > > >On 11/24/2004 06:19 AM, Mark Martin said:
> > > >
> > > >>I have a very simple script to delete records from an Oracle table :
> > > >>#!/usr/bin/perl
> > > >>use DBI;
> > > >>use DBD::Oracle;
> > > >>$dbh = DBI->connect( "dbi:Oracle:database", "user", "password") or die
> > > >>"Can't connect to Oracle database: $DBI::errstr\n";
> > > >>$dbh->do( "DELETE FROM TABLE WHERE YEAR=2003" );
> > > >>exit;
> > > >>the script never finishes and the records remain????
> > > >
> > > >If that is the actual SQL, TABLE is a reserved word so it would need to
> > > >be
> > > >"TABLE".
> > > >
> > > >You have no idea what happened because you aren't checking for errors in
> > > >do(). The examples below show some ways (only one is needed), see
> > > >http://search.cpan.org/~timb/DBI-1.46/DBI.pm for more information; search
> > > >for errstr, RaiseError and PrintError.
> > > >
> > > > $dbh = DBI->connect( "dbi:Oracle:database", "user", "password",
> > > > { RaiseError => 1 } )
> > > > or die "Can't connect to Oracle database: $DBI::errstr\n";
> > > > $dbh -> {RaiseError} = 1;
> > > >
> > > > $dbh->do( qq(DELETE FROM "TABLE" WHERE YEAR=2003) )
> > > > or die "Can't delete, $DBI::errstr\n";
> > > >
> > > >--
> > > >Mac :})
> > > >** I usually forward private questions to the appropriate mail list. **
> > > >Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
> > > >Cthulhu in 2004. Don't settle for the lesser evil.
> > >
>
