Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Remote Oracle database access from windows

by my600080 (Novice)
on Jul 24, 2009 at 14:54 UTC ( #783005=perlquestion: print w/replies, xml ) Need Help??

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

    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"); }
      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
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.

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://783005]
Approved by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2019-07-21 00:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If you were the first to set foot on the Moon, what would be your epigram?






    Results (7 votes). Check out past polls.

    Notices?