So, I don't know anything about running Perl in a Windows environment, but I can point out one or two very obvious thing(s) you are doing wrong. First, you want 'use DBI;' rather than 'use DBD::Oracle;' unless there is some weirdness about using SQL::Statement (which I have never used) that I don't know about. Second, you want to retrieve your results into some kind of data structure, not just let them fall to the floor:

  use strict;
  use DBI;
  # make the connection the way you have shown
  my @resultrow = ();
  my $resultref = $dbh->selectrow_arrayref("select ID from study 
    where NAME='$unique_search_string_here'");
  if (ref($resultref) eq 'ARRAY') {
    @resultrow = @{$resultref};
    # do something with $resultrow[0]
  }
  else {
    # check for error in $DBI::errstr, etc.
  }
[download]

This will work only where you have a unique search string that returns only a single row of data ... if you can have multiple rows, use selectall_arrayref, which will give you a reference to an array of array references. Take a few moments and familiarize yourself with DBI.

I think I would take this one step further and bind the variable that you are passing to the SQL.

use strict;
  use DBI;
  # make the connection the way you have shown
  my @resultrow = ();
  my $sth = $dbh->prepare("select ID from study 
    where NAME=?");
  $sth->execute($unique_search_string_here);
  my $resultref = $sth->fetchrow_arrayref;
  if (ref($resultref) eq 'ARRAY') {
    @resultrow = @{$resultref};
    # do something with $resultrow[0]
  }
  else {
    # check for error in $DBI::errstr, etc.
  }
[download]



--BigJoe

Learn patience, you must.
Young PerlMonk, craves Not these things.
Use the source Luke.

I always forget to do that, but this is good advice from BigJoe. There are two reasons (probably more) to use bind parameters:

• Preparing your SQL statement once and executing it inside a loop will save you a lot of overhead any time you are repeating the same operation across a range of variables. While a database cache may protect you from shooting yourself in the foot, there is no sense in trusting in that when you can code around it so easily.
• A happy side effect of using the question-mark in the prepare and passing the variable in the execute method is that you don't have to worry about quoting strings and not quoting numbers or dates -- the DBI just takes care of it for you. This can save you considerable grief, or at least I find it does in Oracle.

Hi,
Thanks for taking time to reply.
But I think my Win32 machine is just having trouble
using the DBI module.
I tried tweaking things,
and I get a variation on error messages such as:


install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/O
+racle/Orac
le.dll' for module DBD::Oracle: load_file:The specified procedure coul
+d not be f
ound at C:/Perl/lib/DynaLoader.pm line 166.

 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expecte
+d
 at C:\PlayGround\oracle.pl line 15
[download]

I am really not sure where to go from here...

I downloaded it from PPM as I'm using Perl v5.8.7 from ActiveState.
Out of desperation, I tried to re-install DBI and DBD::Oracle,
by downloading the zip files from cpan.org
and doing MakeFile and nmake,
but I've just gotten many other error messages,
such as:


Using Oracle in C:/oracle/ora92
DEFINE _SQLPLUS_RELEASE = "902000100" (CHAR)
Oracle version 9.2.0.1 (9.2)
Found oci directory
Using OCI directory 'oci'
Found oci/lib/MSVC/oramts.lib library

      Unable to find required Oracle OCI files for the build.  Please 
+check
      that you have your OCI installed in your oracle home (C:/oracle/
+ora92)
      directory and that it has the following files (and probably more
+):

        C:/oracle/ora92\oci\include\oratypes.h
        C:/oracle/ora92\oci\lib\MSVC\.lib

      Please install OCI or send comments back to dbi-users@perl.org
      if you have an OCI directory other than oci.

      Alternatively, if you're using ActiveState perl on Windows try
        ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.3/DBI.pp
+d
        ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.3/DBD-Or
+acle.ppd

       at C:\Perl\lib\DBD-Oracle-1.18\Makefile.PL line 258.
[download]

So, gathering what I have heard from the ChatBoard
and other web sites I've found and the above error,
I think you need the full developer version of Oracle,
in order to run DBD::Oracle.
The developer next door (he's not a Perl person)
told me that my machine does have OCI because
I am using SQL plus and Toad,
but we've concluded that there are other Oracle files
that Perl needs in order to run this module.

If my memory serves me correctly, when you use PPM to install DBD-Oracle, it prompts you to download a complete set of Oracle client drivers and then sets them up under the Perl root somewhere, but I could be confusing that with something else. One important thing in your Perl code is to set $ENV{ORACLE_HOME} before you try to use DBI->connect to open an Oracle connection. Your co-worker may be right as well. I don't have my work machine in front of me so I can't tell you offhand, but basically you may want to re-run the Oracle Installer and make sure you've selected all the options for client-side drivers.