How to access an Oracle database from Perl?

Question

I'm converting some shell scripts to perl. All the database access is done using sqlplus. With perl is that a better way to access an Oracle database or should I just stick to sqlplus.

Solution 1

Here is a short example of usage of DBI:

use DBI;

$user = 'donny';
$password = 'ppp';
$dbconnectstring = 'basetest';
$dbh = DBI->connect('dbi:Oracle:',$user.'@'.$password,$dbconnectstring);

Also, note you can access sqlplus - or any command line - within a perl script. Just use backticks:

`cd dasd`

For example. Not sure if you'd want to do this, but just an idea, since you said you're converting shell to perl.

Solution 2

DBI is the standard Perl database interface (unsurprisingly, it has an Oracle driver). DBIx::Class wraps it with a nice ORM interface.

SQL Plus appears to be a command line interface to Oracle. To use it from Perl you would have to construct your queries by mashing strings together (a great way to introduce SQL injection problems), shell out to the command line client, then parse the text output. That is madness. Use an interface that gives you Perl data structures to work with.

Comments

[DVK]

If you want a good intro to connecting to oracle (on top of already-decent DBI's POD documentation) the following article has good beginning examples: dba-oracle.com/t_dbi_interface1.htm

[Bill]

I'm getting the error, "install_driver(Oracle) failed: Can't locate DBD/Oracle.pm in @INC". Do I need to install a BDI package?

[PinkElephantsOnParade]

Yes, that's right. Depending on your perl distro you are going to need to do cpan DBD::Oracle or do... whatever you do if you have ppm (-=. You type that in the command line and if perl is properly installed it will start doing things for you.