Any way to load data to remote oracle db using perl ???

Is there any perl module or any method that can support loading data to a remote oracle db WITHOUT oracle client installed ???
I'd love to load data to an oracle db on a server from many hosts that can be unix or windows that not all of them have oracle client installed.
So please help.
nanoterabetaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mjcoyneCommented:
Why does DBI not fit the bill?

use strict;
use DBI;

my $dbh = DBI->connect( 'dbi:Oracle:db_name', 'username', 'password', {  RaiseError => 1, AutoCommit => 0 }
                      ) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT * FROM tablename blah, blah WHERE blah... };

my $sth = $dbh->prepare( $sql );

$sth->execute();

$dbh->disconnect();
dbkrugerCommented:
absolutely! The only reason you need the client is if you want oracles suite of tools, like their command line, etc.
nanoterabetaAuthor Commented:
thank you in advance,
so what exact perl modules version needed to run MJCOYNE's script ? (including dbi and dbd ???)
Using DBI, when I need to load data from a file, I will have to insert every line or I can load the whole file like using sqlloader of oracle client?
It's very good if you give me some examples of loading a data file using DBI.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

mjcoyneCommented:
The latest DBI package is version 1.5 (see http://search.cpan.org/~timb/DBI-1.50/DBI.pm), and the latest Oracle driver is 1.16 (see http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm)...
nanoterabetaAuthor Commented:
look at your example:
... my $dbh = DBI->connect( 'dbi:Oracle:db_name', 'username', ...
you use db_name means that you expect a tnsnames.ora
file along with oracle installed on the host machine??
Do they really work without oracle???
mjcoyneCommented:
The connect string expects three arguments: a data source name (DNS), a username, and a password.  The DNS can take several forms:

  dbi:DriverName:database_name
  dbi:DriverName:database_name@hostname:port
  dbi:DriverName:database=database_name;host=hostname;port=port

In your case the DriverName would be Oracle.  How you connect to Oracle and whatever database you want to access depends on your setup.  But, no -- Oracle does not have to be running on the host (the machine running the Perl script), it can be on a remote server, as indicated by the example DNS strings above.
nanoterabetaAuthor Commented:
After installing DBI 1.5 successfully, I tried to install
DBD-Oracle 1.16 there is an error:
**********************************
root@INSecver # perl Makefile.PL
Using DBI 1.50 (for perl 5.008005 on sun4-solaris) installed in /usr/local/lib/perl5/site_perl/5.8.5/sun4-solaris/auto/DBI/

 Configuring DBD::Oracle ...

>>>     Remember to actually *READ* the README file!
        Especially if you have any problems.

  The ORACLE_HOME environment variable must be set.
  It must be set to hold the path to an Oracle installation directory
  on this machine (or a machine with a compatible architecture).
  See the README.clients file for more information.
  ABORTED!
************************************

and then I read README file, some extract here:

************************************
*** *BEFORE* BUILDING, TESTING AND INSTALLING DBD::Oracle:

    Build, test and install Perl 5 (at least 5.6.1)
    It is very important to TEST it and INSTALL it!

    Build, test and install the DBI module (at least DBI 1.21).
    It is very important to TEST it and INSTALL it!

    Remember to *read* the DBI README file and this one CAREFULLY!

    Install enough Oracle software to enable DBD::Oracle to build.
    That usually includes Pro*C and SQL*Net. That's not very specific
    because it varies so much between Oracle releases.

    Except under MS Windows, ORACLE_HOME must point to the Oracle Home
    used to create DBD::Oracle.  Even under MS Windows, it doesn't hurt.

    Make sure Oracle is working and you can use Oracle commands
    like sqlplus to talk to the database *from the machine* you
    want to build DBD::Oracle on. (This often involves setting
    environment variables like PATH, LD_LIBRARY_PATH, TWO_TASK etc.)

*** BUILDING:
************************************
so there must be a version of Oracle installation
on my host ???

mjcoyneCommented:
Just to try it out, I successfully installed DBD-Oracle version 1.16 in ActivePerl 5.8.4.810 using PPM and the ActiveState PPM2 Repository.  During the install, I was presented with a EULA, and after I agreed, oci.dll (184 Kb) and oraociei10.dll (78 Mb) were downloaded.  After the Oracle driver was installed, these files remained in my C:\Perl\bin directory.  Since I don't own or use Oracle, I don't know whether they have to remain, but they are the only Oracle files to ever be on my machine, so you definitely don't need a full Oracle installation...:)

Again, since I don't use Oracle, I haven't tried installing the DBD::Oracle driver on my Linux server (I don't want to mess with this machine, it's doing a lot of stuff for me 24/7).  Have you tried installing it with:

# perl -MCPAN -e shell
cpan> install DBD::Oracle

Looking briefly around on the web, in Linux I think you need to install the Oracle Instant Client (available at http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html).  I'm not sure, but I think the Instant Client packages (I'd grab the Basic and SDK packages) supply the header files the compiler needs for the build, and you likely need to set the ORACLE_HOME environmental variable to point at them.

There are instructions on doing a Linux install at http://www.codecomments.com/archive237-2004-4-172861.html (in the same post, there are also instructions for Solaris; don't confuse the two).

Again, I don't know what has to remain on the host machine once the compilation and installation of the DBD::Oracle driver is completed.

Boy, I'm glad I use MySQL...:)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.