( 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. >
