Pure Perl DBI for Oracle database

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

Is there the possibility of a Pure Perl DBI connection of some kind to an Oracle database? I'm trying to get away from worrying about the specific server architecture (e.g. Linux or AIX or Solaris), matching the specific installation location for the Perl modules on every server (the location of my HOME directory varies on different servers), the need to install an Oracle Instant Client on every server, and especially the need to do a source install on each server.

I'm imagining something like the following:

Build and install DBI on a server (with an installed compiler) inside a local::lib environment

Build and install some kind of pure perl DBD driver (or maybe use one of the built-in DBD drivers?)

Copy only the directory structure and the .pm files to another server

Run queries against the Oracle database using DBI_PUREPERL=2 and local::lib

What kind of DBD driver would work in this scenario (ODBC maybe)? Any other tips?

Comment on Pure Perl DBI for Oracle database

Replies are listed 'Best First'.
Re: Pure Perl DBI for Oracle database by afoken (Chancellor) on Sep 25, 2018 at 22:11 UTC
The Oracle OCI is a propritary protocol (or a collection of protocols). To be excact: OCI is a library that completely hides whatever protocol is used between client and database. In theory, you could ask nicely if you can get a wire protocol spec, but it is unlikely that you will get anything like that. You could trace the protocol at the wire level and try to revert a spec from that. From there, you could re-implement a client in puere Perl, in C/XS, or any other language you like. But I think Oracle would be "not amused". For ODBC, the situation is similar. You have to make calls (at the C library level) to an ODBC manager that loads one of several ODBC drivers. That drivers are again binaries that work on top of OCI or similar. You need C/XS to talk to an ODBC manager. Plus, ODBC adds a lot of overhead. (And on Windos, bundled ODBC drivers are sometimes really, really old.) The only sane way to talk with Oracle is to install Instant Client everywhere you need it. You could use a single machine as a gateway to oracle, using DBD::Proxy. But I would expect a significant slow-down if you go that way. Plus, all machines have to be similar and run similar versions of Perl, because DBD::Proxy is based on Storable, and the latter is not universally compatible (see Re: Perl Storable problem (i Think)). Also, DBD::Proxy offers absolutely no security. Quoting its documentation: RPC::PlClient used underneath is not secure due to serializing and deserializing data with Storable module. Use the proxy driver only in trusted environment. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]

Replies are listed 'Best First'.

Re: Pure Perl DBI for Oracle database
by afoken (Chancellor) on Sep 25, 2018 at 22:11 UTC

The Oracle OCI is a propritary protocol (or a collection of protocols). To be excact: OCI is a library that completely hides whatever protocol is used between client and database. In theory, you could ask nicely if you can get a wire protocol spec, but it is unlikely that you will get anything like that. You could trace the protocol at the wire level and try to revert a spec from that. From there, you could re-implement a client in puere Perl, in C/XS, or any other language you like. But I think Oracle would be "not amused".

For ODBC, the situation is similar. You have to make calls (at the C library level) to an ODBC manager that loads one of several ODBC drivers. That drivers are again binaries that work on top of OCI or similar. You need C/XS to talk to an ODBC manager. Plus, ODBC adds a lot of overhead. (And on Windos, bundled ODBC drivers are sometimes really, really old.)

The only sane way to talk with Oracle is to install Instant Client everywhere you need it.

You could use a single machine as a gateway to oracle, using DBD::Proxy. But I would expect a significant slow-down if you go that way. Plus, all machines have to be similar and run similar versions of Perl, because DBD::Proxy is based on Storable, and the latter is not universally compatible (see Re: Perl Storable problem (i Think)). Also, DBD::Proxy offers absolutely no security. Quoting its documentation:

RPC::PlClient used underneath is not secure due to serializing and deserializing data with Storable module. Use the proxy driver only in trusted environment.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

[reply]