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

I have some CGI scripts that should connect to an Oracle database on another server. I don't think I can set ORACLE_HOME in my CGI scripts because there's no local instance of Oracle. I looked through Oracle.pm and saw something called TWO_TASK that looked helpful but I couldn't get it to work. Do I have to set something else that says where tnsnames.ora is located? Any thoughts would be appreciated. Thanks.
  • Comment on Connecting to a remote Oracle database from CGI scripts

Replies are listed 'Best First'.
Re: Connecting to a remote Oracle database from CGI scripts
by runrig (Abbot) on May 25, 2001 at 23:26 UTC
    You need to install SOME Oracle files to install DBD::Oracle. There was a post recently on the DBI mailing list about this. If its not in the README's then that's a good place to look/ask. Another alternative is DBD::ODBC (you'd need an ODBC driver, of course) or the DBI::Proxy and DBD::ProxyServer. Here was the post on the DBI list for installing DBD::Oracle for Linux:

    Update:Removed the post since it was long and should be in the list archives anyway.

    Another update:Looking at the other answers, I guess the big question is whether or not Oracle is even installed on the client. The other answers seem to assume it is installed, I assumed it isn't.

[reply]
Re: Connecting to a remote Oracle database from CGI scripts
by tinman (Curate) on May 25, 2001 at 23:33 UTC

    I try the client test.. if I can get a SQL plus client to connect successfully to the remote database from the webserver machine, then, most of my connection worries are solved. IIRC, DBD::Oracle requires that the Oracle client libraries be installed. The easiest way to do this is to actually install the SQL-plus client on the server machine..

    Make sure that any user privileges/permissions issues are sorted out, and thereafter, 

    use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=machine_name;sid=ORCL;port=152
+1', 
  'scott', 'tiger', { RaiseError => 1, AutoCommit => 0 });

    [download]
    or even the conventional connection method (using the TNS entry) will work.. try these links for some further information... http://www.med.univ-rennes1.fr/~poulique/cours/perl/dbi_oracle.html
    http://thomas.eibner.dk/oracle/dbi/

    HTH
[reply]
[d/l]
Re: Connecting to a remote Oracle database from CGI scripts
by BigJoe (Curate) on May 26, 2001 at 00:11 UTC
    To connect to a different Oracle Database (if you already connect to one) all you need to do is go into you Oracle_Home directory. Then go to network\admin\ in this directory you will see a file called tnsnames.ora you need to edit this. Copy the record that is in there. Edit the second one by changing the machine name and sid. If you have an Oracle DBA on site they should be able to assist in doing this. 
    *SID*.WORLD =
    ( DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = *HOSTNAME*)(PORT = 1521)
+)
        )
      (CONNECT_DATA =
        (SERVICE_NAME =  *SID*.world)
      )
  )

    [download]
    The things with stars are the only things you should need to change.

    --BigJoe

    Learn patience, you must.
    Young PerlMonk, craves Not these things.
    Use the source Luke.
[reply]
[d/l]
Re: Connecting to a remote Oracle database from CGI scripts
by passalack (Initiate) on May 26, 2001 at 00:36 UTC
    This is all great advice. To clarify, I don't want to have to install anything beyond the tnsnames.ora file on the web server. I want this to be enough to get me to the server that holds the database. So if possible, anything regarding ORACLE_HOME is kind of out of the question. I've found, thanks to your suggestions, that if i specify the host in my connection call that I am able to connect. But my tnsnames.ora file actually specifies the host, so I shouldn't have to also specify it in my script.
[reply]
      I know this is an old post, but I think that it requires another response (for the archives). To connect to a remote Oracle database you must install the Oracle Client Software. It's pretty easy, just pop in the CD and follow the prompts. The installer will only copy and configure the minimum amount of software required. There are plenty of resources out on the net WRT to installing Oracle on Linux if that's what you require (try "oracle-on-linux" on groups.yahoo.com for a start). Next, install the latest versions of DBI & DBD::Oracle. You'll have to set up a couple of env vars before running the tests but they will work 100% if you set ORACLE_HOME to the appropriate directory on the client and ORACLE_SID to the appropriate entry in tnsnames.ora (and set the username/password if the DBD::Oracle tests complain). If you're using Apache, always install Apache::DBI otherwise your webserver will spend all day connecting & disconnecting from Oracle and doing hardly any "real" work.

      rdfield

[reply]

Back to Seekers of Perl Wisdom