Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Oracle and Perl

by notsoevil (Pilgrim)
on Mar 28, 2001 at 21:40 UTC ( #67876=perlquestion: print w/replies, xml ) Need Help??

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";

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/ 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

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.

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

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="";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!";


    Philosophy can be made out of anything. Or less -- Jerry A. Fodor

      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);

      Which returns:

      DBI->connect(LWCWeb) failed: Specified driver could not be loaded due +to system error 5 (Oracle73 Ver 2.5).

      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 ..

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

        C:\> net helpmsg 5 Access is denied. C:\>
        Yep, same error.

                - tye (but my friends call me "Tye")
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"
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!";

    Any further suggestions?

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

      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 :)
        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.

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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://67876]
Approved by root
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2023-12-08 15:41 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (36 votes). Check out past polls.