in reply to Oracle 10g Database Connection Trouble

So the file exists in c:/Perl/lib/DBI, but perl searches for it in C:/Perl/lib. I have no idea why it would install that file in the DBI/ sub directory, where it would be refer to DBI::DBD, not DBD .

Update: There is a module DBI::DBD in the DBI distribution. Are you sure that what you posted was the complete error message? Why the heck would it want to load DBD, which doesn't exists on cpan? What's your code? what DSN did you pass to DBI->connect?

Replies are listed 'Best First'.
Re^2: Oracle 10g Database Connection Trouble
by Photius (Sexton) on Aug 25, 2008 at 17:00 UTC
    Thanks! That got me past that error. Now it is giving: 
    Can't connect to data source '(LOAD_BALANCE=on)' because I can't work 
+out what driver to use (it doesn't seem to contain a 'dbi:driver:' pr
+efix and the DBI_DRIVER env var is not se
t) at H:\Projects\NLSExport\ExportNLSched.pl line 65

    [download]
    With other applications that connect to our Oracle databases, we are using the Oracle tnsnames.ora file which contains the various connect string stanzas. I simply copied the same connect string from the TNS file that we normally use. So excertps from my Perl code look like this: 
    use strict;
use lib 'c:/Perl/lib/DBI';
use DBI;
my $dbh;
my $dbConnectString = '(LOAD_BALANCE=on)'|| 
                      '(ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521
+))'||
                      '(ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521
+))'||
                      '(CONNECT_DATA=(SERVICE_NAME=pcipJDBC.oracle.com
+))';
my $dbUser = 'myUsername';
my $dbPwd  = 'myPassword';
                                                                      
+ 
unless ($dbh = DBI->connect($dbConnectString, $dbUser, $dbPwd, {RaiseE
+rror => 0, AutoCommit => 0 })) { # Handle for PROD database connectio
+n
  die "Unable to connect to PCI Oracle as user $dbUser: $DBI::errstr: 
+$!";
}
$dbh->{RaiseError} = 1;                                               
+         # So we don't have to check every DBI call we set RaiseError
+.

    [download]
    This connect string is a little more involved because of load balancing.
[reply]
[d/l]
[select]
      Try: 
      my $dbConnectString = 'dbi:Oracle:' .
  '(LOAD_BALANCE=on)'.
  '(ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521))'.
  '(ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521))'.
  '(CONNECT_DATA=(SERVICE_NAME=pcipJDBC.oracle.com))';

      [download]
      Or, you can put something like 
      SIMPLE= (DESCRIPTION=
   (LOAD_BALANCE=on)
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
                          (PORT=1521)
                          (HOST=al4.almg.uucp))
                 (ADDRESS=(PROTOCOL=TCP)
                          (PORT=1521)
                          (HOST=al4.almg.uucp))
   )
   (CONNECT_DATA= (SERVICE_NAME=pcipJDBC.oracle.com))
)

      [download]
      in your 'tnsnames.ora' file, and use 
        my $dbConnectString = 'dbi:Oracle:SIMPLE';

      [download]
      []s, HTH, Massa (κς,πμ,πλ)
[reply]
[d/l]
[select]
      The connect string that you pass to DBI->connect has to begin with dbi:Oracle, otherwise DBI can't know what driver to load.

      The DBD::Oracle has two sections on connecting to Oracle, maybe some of that helps you a bit.

      (I'd also recommend to start with simple cases, and once you have a connection, you can start adding a the load balancing stuff).

[reply]
[d/l]
[select]
      I'm in!!! Many thanks to all who helped with notable Perl wisdom. My root problem is what I call a Dhf (Dave head failure) (special case of uhf, user head failure). In my haste, I was mixing languages by trying to use || which is the string concatenation operator in PL/SQL. Duh! Also, I had not realized that DBI could make use of my existing Oracle tnsnames.ora file. That makes things much much simpler (and isolated from changes that are occasionally made to that file)! Wow, I'm elated about that.

      Once I got that all straightened out (much simpler), it turns out that I don't even need to specifically tell it to use lib 'c:/Perl/lib/DBI' either.

      I'll say a novena for everyone. Blessings on you all!

      The code that now works looks like this: 
      use DBI;
my $dbh;
my $dbConnectString = 'dbi:Oracle:ABCD'; # Connect to the ABCD databas
+e specified in tnsnames.ora
my $dbUser = 'myUsername';
my $dbPwd  = 'myPassword';

# Establish the Oracle database connections and initialize the databas
+e statement handle used later
unless ($dbh = DBI->connect($dbConnectString, $dbUser, $dbPwd, {RaiseE
+rror => 0, AutoCommit => 0 })) {
  die "Unable to connect to Oracle database ABCD as user $dbUser: $DBI
+::errstr: $!";
}
$dbh->{RaiseError} = 1; # So we don't have to check every DBI call we 
+set RaiseError.

print "Connected\n";

$dbh->disconnect;
undef $dbh;

      [download]
[reply]
[d/l]
      This may not be your problem, but it does look suspiciuous: 
      my $dbConnectString = '(LOAD_BALANCE=on)'|| 
                      '(ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521
+))'||
                      '(ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521
+))'||
                      '(CONNECT_DATA=(SERVICE_NAME=pcipJDBC.oracle.com
+))';
print "dbConnectString=$dbConnectString\n";

__END__
dbConnectString=(LOAD_BALANCE=on)

      [download]

      Does $dbConnectString really contain what you want? What are all those "||" operators for?

[reply]
[d/l]
[select]
      If you've already got the Oracle client installed and you're using TNSNAMES.ORA for name resolution (or Oracle Directory, for that matter) you should be able to use: $dbh = DBI->connect("dbi:Oracle:your_db_alias",$dbUser, $dbPwd); where your_db_alias is the same database name you'd type in on the sqlplus connect command. The Oracle drivers are wise enough to suss it out. In fact, you can specify the driver and get a list of what you can connect to: use DBI; my @datasources = DBI->data_sources( "Oracle" ); for my $dsn (@datasources){print "$dsn\n";} If your database appears in the list, snag it and go (if it doesn't well, you're on your own).
[reply]

In Section Seekers of Perl Wisdom