You seem to be misunderstanding how grep works. I recommend re-reading the documentation.

You actually want something like this:

if ( grep $_ == $xpid, @procs ) {
   print "Pid is in use : $xpid\n";
}
[download]

Also, fetchrow_arrayref returns an array _reference_ (as in the name). So you should be writing:

$procs = $dbh->selectall_arrayref("select spid from v\$process");
if ( grep $_ == $xpid, @$procs ) {
   print "Pid is in use : $xpid\n";
}
[download]

The potential downside with grep is that it always searches the whole list - which will impact performance if the list is large. Something like this might be more efficient (or build a hash as robiticus suggests):

$procs = $dbh->selectall_arrayref("select spid from v\$process");
my $found;
foreach (@$procs) {
  if ($_ == $xpid) {
    $found = 1;
    last;
  }
}
if ($found) {
   print "Pid is in use : $xpid\n";
}
[download]

Oh, and one last tip. If you use single quotes around your SQL then you don't need to escape the '$'.

$procs = $dbh->selectall_arrayref('select spid from v$process');
[download]

Given the number of times I need to do this I've decided to go down the hash route . However thanks for your answer, it's shown me some things I hadn't considered (even after reading the manual).
One thing I'd like to add is that the " around the SQL statement is purely habit on my part.
I often have to write SQL that does something like this

"select blah from v\$someview where upper(NAME) like upper('%file%')"
[download]

Hence the need to use double instead of single quotes

I can't seem to get this to work. I thought I'd try this out to compare it for speed with the hash version mentioned later. It never prints the pid in use message even though the pid exists in the array. How do I debug this statement

if ($_ == $xpid) {
[download]

To see why the match is not being made ? I tried

print @$_ ;
[download]

This gives me the value I expect to see (i.e. a pid) but even though $xpid appears to have the same value the if is not evaluating to true ?

I've answered my own question. The answer I found is

if ("@$_" == "$xpid") {
[download]

Sorry for the confusion

The above answers are good.... But why not let the database do the search?

my $xpid = 1234;
my $procs = $dbh->selectall_arrayref("
    select spid from v\$process
    where spid = $xpid
");
if ( @$procs ) { # was one found?
    print "Pid is in use : $xpid\n";
}
[download]

Or even change the SQL to SELECT count(*) FROM v\$process WHERE spid = $xpid so you get immediately the number of records in the database for which spid = $xpid. It is faster, it saves on memory and you can plug it straight into the if.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

This is fine provided you know the values of all the $xpid (there could be thousands) prior to issuing the SQL. The problem is I don't. This means that I would have to issue a select every time I search for a spid which isn't as cost effective as getting the whole list up front and letting Perl do the search (I know ... I've tested it). It's my fault for not expressing the question in as much detail as perhaps I should

grep selects items from a list based on a test. If the test is true the item is selected, otherwise it is rejected. In your code you are using the contents of $xpid as the "test". You probably actually want something like:

if ( grep {$_ eq $xpid} @procs ) {
   print "Pid is in use : $xpid\n";
}
[download]

my $xpid = 1234;
%procs = map {$_ => 0} @{$dbh->selectall_arrayref(
                         "select spid from v\$process")};
if (defined($procs{$xpid})) {
   print "Pid is in use : $xpid\n";
}
[download]

You are assigning the value 0 to every key so every key will be defined however you can't test for truth because 0 is false. If instead of assigning 0 you assigned any non-zero value you wouldn't have to use defined() to test the keys:

my $xpid = 1234;

%procs = map { $_ => 1 } @{ $dbh->selectall_arrayref( "select spid fro
+m v\$process" ) };

if ( $procs{ $xpid } ) {
   print "Pid is in use : $xpid\n";
}
[download]

Another way to do it:

my $xpid = 1234;

@procs{ @{ $dbh->selectall_arrayref( "select spid from v\$process" ) }
+ } = ();

if ( exists $procs{ $xpid } ) {
   print "Pid is in use : $xpid\n";
}
[download]

jwkrahn:

Thanks, I hadn't thought of that. It certainly simplifies things a bit!

I'm not a perl golfer yet, but I think you just got a birdie! 8^)

--roboticus

Out of curiousity I tried testing these out and neither seem to work ?


use DBI;
use DBD::Oracle;

$dsn = "dbi:Oracle:MYSID";
$dbuser = "myuser/mypass";
my $dbh = DBI->connect($dsn, $dbuser, '', { AutoCommit => 1, PrintErro
+r => 0 });

unless($dbh) {
   die "Unable to connect ($DBI::errstr)\n";
   exit 1;
}

@procs{ @{ $dbh->selectall_arrayref( "select spid from v\$process" ) }
+ } = ();

my $xpid = 16405;
if ( exists $procs{ $xpid } ) {
      print "Pid in use : $xpid\n";
}
[download]

The $xpid definitely exists in the v$process view. Is there something missing from this ?