Re^2: Oracle 10g Database Connection Trouble

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.

Comment on Re^2: Oracle 10g Database Connection Trouble Select or Download Code

Replies are listed 'Best First'.
Re^3: Oracle 10g Database Connection Trouble by massa (Hermit) on Aug 25, 2008 at 17:19 UTC
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]
Re^3: Oracle 10g Database Connection Trouble by moritz (Cardinal) on Aug 25, 2008 at 17:10 UTC
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]
Re^3: Oracle 10g Database Connection Trouble by Photius (Sexton) on Aug 25, 2008 at 18:54 UTC
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]
Re^3: Oracle 10g Database Connection Trouble by toolic (Bishop) on Aug 25, 2008 at 17:09 UTC
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]
Re^3: Oracle 10g Database Connection Trouble by Anonymous Monk on Aug 25, 2008 at 17:22 UTC
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]