Thanx for the tips. I will definitely incorporate them. Something else must
have been gobbling up the database, 'cuz I finally got the query to run. The
bad news was that it took about 22 minutes, so now I get to figure out why.
Thanx, everybody, for the help.
ltg
-----Original Message-----
From: Tony Foiani [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 18, 2001 4:01 PM
To: Laurie Gennari
Cc: Dbi-Users (E-mail)
Subject: Re: works in sqlplus but not through script
>>>>> "Laurie" == Laurie Gennari <[EMAIL PROTECTED]> writes:
Laurie> I'm sure I must be missing something obvious (it's been one of
Laurie> those weeks), but I can't for the life of me figure out why
Laurie> this doesn't work. It just hangs and never finishes. Any help
Laurie> would be much appreciated.
As someone else already pointed out, an indication of what is going
wrong would help us a lot. In particular, do any of your "die"
conditions trigger? And, if so, what is the error?
Offhand:
Laurie> #!/usr/local/bin/perl
Consider adding "-w" to the invocation line above, and "use strict".
"perldoc strict" for more info.
Laurie> use DBI;
Laurie> $ENV{ORACLE_HOME} = '/opt/oracle/product/8.1.6';
Laurie> $ENV{ORACLE_SID} = 'dev1';
Laurie> $dbh = DBI->connect("DBI:Oracle:tamarac_dev1","sys","password", {
Laurie> PrintError => 1} )
Laurie> || die "Can't connect ", $dbh->errstr();
This looks distinctly odd. I'd have to consult the precedence tables,
but a more idiomatic way of writing this might be:
$dbh = DBI->connect(...)
or die "Can't connect: " . $dbh->errstr();
Note that this uses the ultra-low-precedence "or" operator, as well as
using concatenation (instead of a comma, which confuses me a little.
'die' does accept a LIST, but I'd probably elect to use parens in that
case....)
Anyway, if you're just going to die anyway, you might as well just set
"RaiseError" in the connect and be done with it. (Admittedly, this is
probably just a small snippit of your code, but ...) Even in a larger
program, using RaiseError is handy; wrap the important bits inside an
"eval { ... }", and then either "commit" or "rollback" depending on
whether you got to the end gracefully or not.
Laurie> $sql = "SELECT vs.username, vs.serial#, vl.id1, vst.sql_text
Laurie> FROM v\$session vs, v\$lock vl, v\$sqltext vst
Laurie> WHERE vs.lockwait = vl.kaddr
Laurie> AND vst.address = vs.sql_address
Laurie> AND vst.hash_value = vs.sql_hash_value";
Consider using single quotes when you don't have anything you're
interpolating. For one this long, you should probably also use a
here-doc (both of these are described in "perldoc perlop"). So, it
might evolve into:
$sql = <<'SQL';
SELECT vs.username, vs.serial#, vl.id1, vst.sql_text
FROM v$session vs, v$lock vl, v$sqltext vst
WHERE vs.lockwait = vl.kaddr
AND vst.address = vs.sql_address
AND vst.hash_value = vs.sql_hash_value
SQL
This is purely a taste issue. "perldoc perlstyle"
Laurie> $sth = $dbh->prepare($sql)
Laurie> || die "Can't prepare statement ", $sth->errstr();
Same comments about precedence apply here. Either use "or", or
parenthesize the assignment.
Laurie> $sth->execute()
Laurie> || die "Can't execute statement ", $sth->errstr();
Laurie> $sth->bind_columns(undef, \$username, \$serial, \$id1, \$sqltext);
Laurie> while ($sth->fetch) {
Laurie> print "username: [$username]\n serial: [$serial]\n id1: [$id1]\n
sqltext:
Laurie> [$sqltext]\n\n";
Laurie> }
Laurie> $dbh->disconnect;
HTH,
t.
