Oracle and Perl

notsoevil has asked for the wisdom of the Perl Monks concerning the following question:

I am developing an article archive cgi script for a local university. Their web server is on NT and the database is on Oracle. Today, Perl was installed on NT from the most recent version at ActiveState.

That is the basics of what exists, now my code:

use DBI;
use DBD::Oracle;
use strict;

my $dbh = DBI->connect("dbi:Oracle:host=myhost;sid=mysid", 'foo', 'bar
+') or die "I died: $DBI::errstr";

print "Content-type: text/html\n\n";
print "hey, i made it this far";
[download]

Which results in:


install_driver(Oracle) failed: Can't load 
'D:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' 
for module DBD::Oracle: load_file:Access is denied 
at D:/Perl/lib/DynaLoader.pm line 200.
 at (eval 1) line 3
Compilation failed in require at (eval 1) line 3.
Perhaps a required shared library or 
dll isn't installed where expected
 at d:\currmktg\test.cgi line 14
[download]

Note, my die statement "I died: $DBI::errstr" did not show up (because it 'die'd before the or?).

The 'Access Denied' part looks promising, but there are no odd permissions (as far as 'permissions' go on NT) on the file or any of its folders.

Maybe the module isn't installed? Nope, I had them checked. It is sitting precisely where it should be. DBI is where it should be too.

A correct and full installation of SQL*Net is on the machine as well. Not that may or may not have been brought up, I just want to cover some possible bases.

The project was going to be done in ColdFusion, but I convinced them to use Perl for two reasons: I know more Perl than ColdFusion, and Perl is the right tool for this job.

Now, before they installed Perl, they had set up a DSN on the machine so ColdFusion could connect to the database via ODBC. That DSN wouldn't have been able to been set up without the proper SQL*Net tools available.

Should I, for the sake of moving forward, have them install the Perl ODBC module and try connecting that way? If I did, what would be the performance loss/gain over a fixed installation of DBD::Oracle?

Thanks for any help.

--
notsoevil
--
Jeremiah 49:32 - And their camels shall be a booty. . .

Comment on Oracle and Perl Select or Download Code

Replies are listed 'Best First'.
Re: Oracle and Perl by arturo (Vicar) on Mar 28, 2001 at 21:53 UTC
You don't need to `use DBD::Oracle` in most cases. I've only ever used it when I want to use the constants it defines for oracle data types (as in `use DBD::Oracle qw(:ora_types)`). But generally, DBI will handle the loading and use of the correct DBD based on the connect string you feed it. So one thing is to try eliminating that line and see what happens. Also: make sure you set `$ENV{ORACLE_HOME}`, because the script may not be running in an environment in which that variable is set. Since the DBI stuff is loaded at compile time, I set that variable in a BEGIN block. `#!/usr/bin/perl -w # please pardon the nix-isms! BEGIN { $ENV{ORACLE_HOME} = '/path/to/oracle/install'; } use DBI; use strict; my $db = DBI->connect('dbi:Oracle:host="oracle.host.com";sid="whatever +"', $user, $pass) or die "Can't connect to server: $DBI::errstr\n"; print "Content-type: text/plain\n\n"; print "Hey, I got this far!";` [download] HTH Philosophy can be made out of anything. Or less* -- Jerry A. Fodor	[reply] [d/l] [select]
Re: Re: Oracle and Perl by notsoevil (Pilgrim) on Mar 29, 2001 at 01:02 UTC
Okay .. further development. They have now installed `DBD::ODBC`. Using a DSN (LWCWeb which does connect to Oracle and work from there), I did the following: `my $dbh = DBI->connect('dbi:ODBC:LWCWeb', $user, $pass);` [download] Which returns: `DBI->connect(LWCWeb) failed: Specified driver could not be loaded due +to system error 5 (Oracle73 Ver 2.5).` [download] Does this seem to be close to what the last error message said? That the driver (I am assuming the DBD module, though I may be wrong) could not be loaded? Is this an installation (of Perl, Modules or otherwise) problem do you think? Thanks for any help .. -- notsoevil -- Jeremiah 49:32 - And their camels shall be a booty. . .	[reply] [d/l] [select]
(tye)Re: Oracle and Perl by tye (Sage) on Mar 29, 2001 at 01:04 UTC
`C:\> net helpmsg 5 Access is denied. C:\>` [download] Yep, same error. - tye (but my friends call me "Tye")	[reply] [d/l]
Re: (tye)Re: Oracle and Perl by notsoevil (Pilgrim) on Mar 29, 2001 at 01:08 UTC
(tye)Re2: Oracle and Perl by tye (Sage) on Mar 29, 2001 at 01:18 UTC
Re: Oracle and Perl by tame1 (Pilgrim) on Mar 28, 2001 at 23:27 UTC
I would have to agree with arturo on this one - I have never had to `use DBD` in my stuff, and I do extensive Database connection scripting. In general, as he said, doing `my $dbh = DBI->connect("dbi:dbtype:dbname", "user","auth");` should work fine. DBI will read the "dbi:Oracle:dbname" and know to use the Oracle DBD. What does this little button do . .`<Click>`; "USER HAS SIGNED OFF FOR THE DAY"	[reply] [d/l] [select]
Re: Oracle and Perl by notsoevil (Pilgrim) on Mar 28, 2001 at 23:38 UTC
I msg'd arturo about this, so I'll mention it here as well: It was a mistake putting `use DBD::Oracle` in there, as I was just typing my example up, not a direct copy n' paste. My actual code wasn't doing that, but when I was typing here it just snuck in there. I am awaiting information ... --insert pause-- ... ah, it just arrived. Well I plugged in the ORACLE_HOME bit and the error has not changed. So I don't mistype this time, I'll copy and paste my script below: `BEGIN { $ENV{ORACLE_HOME} = 'g:\orant'; } use DBI; use strict; my $host = 'myhost'; my $sid = 'PROD'; my $user = 'foo'; my $pass = 'bar'; my $db = DBI->connect('dbi:Oracle:host="$host";sid="PROD"', $user, $pa +ss) or die "Can't connect to server: $DBI::errstr\n"; print "Content-type: text/plain\n\n"; print "Hey, I got this far!";` [download] Any further suggestions? -- notsoevil -- Jeremiah 49:32 - And their camels shall be a booty. . .	[reply] [d/l]
Re: Re: Oracle and Perl by agoth (Chaplain) on Mar 29, 2001 at 14:42 UTC
A few things, I've trawled through my back catalog of code and seem to have lost examples of Oracle connections across a network from windows, oops. This is possible and even easy when your settings are correct so don't give up. I've never had to set ORACLE_SID like that in the connect string. If it is not in the environment of the user that is running the script, then SID is settable in the begin block. Try using DBI's installed_drivers method to loop through the hash of drivers before attempting your connect Keep plugging away, change vars / permissions / I would stake 200xp theres a simple solution to this one :)	[reply]
Re: Oracle and Perl by notsoevil (Pilgrim) on Mar 30, 2001 at 09:13 UTC
I am almost positive there is a permissions problem. I had the guy down there run the script from the DOS prompt, and no error occured. So, on recap seeing that both errors said 'Access Denied' I am thinking: The 'user' the script is running under (inherited from webserver) is not able to access the database (via Oracle module or ODBC). .. or .. The 'user' the script is running under is not able to access any module, due in part perhaps to restrictive permissions on the Perl installation directories. I would have checked other modules (say, CGI for instance) today had I not taken the day off. I'll try tomorrow. .. or .. Something else along these lines. My problem is my sys admin knowledge does not stretch across to the NT platform this is sitting on. I'll have to remotely try to figure this out with the guy over the phone. If anyone has suggestions along these 'permissions' lines, why it would be so, and such forth, please advise. -- notsoevil -- Jeremiah 49:32 - And their camels shall be a booty. . .	[reply]