Why wont my script finish?
Hi, 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 Thanks, Mark
Re: Why wont my script finish?
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.
Re: Why wont my script finish?
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.
Re: Why wont my script finish?
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.
Re: Why wont my script finish?
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.
RE: Why wont my script finish?
Mark Martin [mailto:[EMAIL PROTECTED] wrote: 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)) Might you have one of the rows you're trying to delete from DBI locked in SQLPLUS? Make sure you commit in SQLPLUS to release any locks you're holding. Ronald
Re: Why wont my script finish?
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.
RE: Why wont my script finish?
Brilliant - that's it! Thank you! Would SELECTs lock up a record without a commit statement? Mark At 11:15 24/11/2004 -0500, Ronald J Kimball wrote: Mark Martin [mailto:[EMAIL PROTECTED] wrote: 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)) Might you have one of the rows you're trying to delete from DBI locked in SQLPLUS? Make sure you commit in SQLPLUS to release any locks you're holding. Ronald
RE: Why wont my script finish?
No. COMMIT is a transactional statement. In Oracle, readers do not block writers. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Mark Martin [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 9:26 AM To: Ronald J Kimball Cc: [EMAIL PROTECTED] Subject: RE: Why wont my script finish? Brilliant - that's it! Thank you! Would SELECTs lock up a record without a commit statement? Mark At 11:15 24/11/2004 -0500, Ronald J Kimball wrote: Mark Martin [mailto:[EMAIL PROTECTED] wrote: 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)) Might you have one of the rows you're trying to delete from DBI locked in SQLPLUS? Make sure you commit in SQLPLUS to release any locks you're holding. Ronald This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Why wont my script finish?
okay - must have locked it with some other statement without realizing. Many thanks to all At 09:38 24/11/2004 -0700, Reidy, Ron wrote: No. COMMIT is a transactional statement. In Oracle, readers do not block writers. - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Mark Martin [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 24, 2004 9:26 AM To: Ronald J Kimball Cc: [EMAIL PROTECTED] Subject: RE: Why wont my script finish? Brilliant - that's it! Thank you! Would SELECTs lock up a record without a commit statement? Mark At 11:15 24/11/2004 -0500, Ronald J Kimball wrote: Mark Martin [mailto:[EMAIL PROTECTED] wrote: 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)) Might you have one of the rows you're trying to delete from DBI locked in SQLPLUS? Make sure you commit in SQLPLUS to release any locks you're holding. Ronald This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Why wont my script finish?
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()?
Hold it. This needs to be clear...
If you have AutoCommit on and one delete statement that deletes 42,000 rows,
that's no
different than without AutoCommit and one commit after the statement.
AutoCommit would
*not* commit after each row is deleted in a single statement. It commits after
the
statement is complete.
Jeff
RE: Why wont my script finish?
On Nov 24, Jeff Urlwin scribed:
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()?
Hold it. This needs to be clear...
If you have AutoCommit on and one delete statement that deletes 42,000 rows,
that's no
different than without AutoCommit and one commit after the statement.
AutoCommit would
*not* commit after each row is deleted in a single statement. It commits
after the
statement is complete.
Jeff
Of course, you are correct. I don't know what I was thinking when I wrote
that.
Dave
