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

Has anyone had experience of remote access to Oracle database from Windows platform? By doing some research, I found that here are the steps:

1. install active perl
2. install DBI module
3. install DBD::ODBC module
4. install Oracle client on windows.
5. set environment variable (ORACLE_HOME)

Is there anything I've missed? If not, I've done step 1 through 3 but not sure about step4. Should I install Oracle INSTANT client? http://www.oracle.com/technology/tech/oci/instantclient/index.html

But on the installation instruction, it says: All installations REQUIRE the Basic or Basic Lite package.

Do you know what it means? What is Basic or Basic Lite package?

If I finish the installation of Oracle client on my windows, the java connection url for the oracle database is like this:
jdbc:oracle:thin:@10.2.3.0:1521:ORADB

Using perl, is this what I should do? (can hostname contain port number too?)

my $driver = "ODBC";
my $database = "ORADB";
my $hostname = "10.2.3.0:1521";
my $dsn = "DBI:$driver:database=$database;host=$hostname";
my $dbuser = "oradmin";
my $dbpassword = "oradminpasswd";

my $dbh = DBI->connect($dsn, "$dbuser", "$dbpasswd");

Many thanks to you all!
  • Comment on Remote Oracle database access from windows

Replies are listed 'Best First'.
Re: Remote Oracle database access from windows
by tokpela (Chaplain) on Jul 24, 2009 at 15:36 UTC

    I use Oracle remotely from Windows all of the time. I don't use ODBC anymore and just connect directly using the Oracle client libraries.

    1. Install and configure Oracle client on Windows
    2. Install ActivePerl
    3. Install DBI module
    4. Install DBD::Oracle module

    If you are having trouble connecting, you might have to rename the oci.dll that is installed when installing DBD::Oracle in the Perl bin directory. I have had conflicts with previously installed clients before and having this oci.dll and the Oracle client both installed caused an issue.

    Use the following connection string syntax in the DBI connection: 

    my $dsn = 'dbi:Oracle:host=10.1.1.100;sid=XX;port=1521';

    [download]

    I also use an eval to trap any errors when trying to connect to the database 

    my $dbh;

eval {

    $dbh = DBI->connect( $dsn, $username, $password) ||
      die "Cannot connect to database - [$dsn]: $DBI::errstr";
};

if ($@) {
   log("[Error] COULD NOT CONNECT TO DATABASE: [$@]\n");
}

    [download]
[reply]
[d/l]
[select]
      I'm using DBD::ODBC module. When I execute my connecting string, I kept getting this error:
      Data source name not found and no default driver specified.

      I tried to use the DBD::ODBC's connecting without DSN:
      my $DSN = 'driver={Oracle ODBC Driver};Server=10.2.3.0:1521;database=ORADB;uid=oraadmin;pwd=oraadminpassword;';
      my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

      But the same error message occurs. I think I might have put the wrong driver= because I cannot find an example for oracle database so I just used the syntax for mysql:
      http://search.cpan.org/~mjevans/DBD-ODBC-1.22/FAQ#How_do_I_connect_without_DSN
[reply]
Re: Remote Oracle database access from windows
by jhourcle (Prior) on Jul 24, 2009 at 15:18 UTC

    I've never done it from Windows, but in various unix machines, I've done:

    1. (perl was already installed)
    2. Install Oracle client
    3. Configure Oracle client (tnsnames.ora; set TWO_TASK, ORACLE_HOME, etc.)
    4. Verify I can connect via sqlplus
    5. Install DBI
    6. Install DBD::Oracle

    If you're using ODBC, I'm not even sure that you need to have the Oracle Client installed -- and I've never used Instant Client, so can't give advice on that one.

    See the 'Connecting to Oracle' section of the DBD::Oracle docs for more information -- I assume it'd be the same for Windows.

[reply]
Re: Remote Oracle database access from windows
by Anonymous Monk on Jul 24, 2009 at 15:23 UTC
[reply]

Back to Seekers of Perl Wisdom