Writing Oracle Stored Procedures in Perl

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

I've been tasked with exploring alternatives for developing stored procedures for an Oracle database. Googling a bit, I found some references to a project called extproc_perl that's ostensibly been under development since 2001 (last updated 2006), and I thought "Great!!". However, Google hits on the project (at least the first 100 or so) are all self-referential or CPAN. It came up on SoPW back in 2002, but that thread had no real responses. Following some CB discussion (thanks Limbic~Region, erix et al.), native support for PL/SQL, Java and .NET was raised but no Perl solutions were immediately obvious.

While any Perl solution will have an obvious performance hit, I'd like to explore all options before committing to Java (and optimizing to PL/SQL after benchmarking). Anyone here have good/bad experiences with extproc_perl to share? Anyone try tying Perl into a DB through Inline::Java::PerlInterpreter? Are there Perl-based options that I've missed?

Comment on Writing Oracle Stored Procedures in Perl

Replies are listed 'Best First'.
Re: Writing Oracle Stored Procedures in Perl by smithers (Friar) on Apr 11, 2009 at 17:25 UTC
What is your goal of finding a replacement language for Oracle PL/SQL? Explaining that may help. Combining Perl and PL/SQL is very powerful as both are excellent languages and both are highly portable. Where there is Oracle (most platforms) there is PL/SQL. Why not use both languages and keep your procedural database code compiled/self-contained in the database as PL/SQL packages? Are you trying to make your procedural database code vendor agnostic?	[reply]
Re^2: Writing Oracle Stored Procedures in Perl by kennethk (Abbot) on Apr 12, 2009 at 05:27 UTC
Our primary motivation is leveraging an existing code-base and experience for internal functions. For example, we would like to migrate some data logic from our loaders, written in Perl, into triggers. Given some of the complexity in our data, using Perl in the data retrieval would be a blessing, but I am cynical about performance (but not prematurely optimizing). This is all underscored by a recent security policy handed down severely restricting what we can use as a front end. In particular, all code running between the database and the outside world must be Java or .NET and all custom code is strongly discouraged. I would like to emphasize we are not munging up display and data logic - I'm talking about ranking/grouping, complex constraints, ...	[reply]