Jeff Horwitz, TargetRx, Inc.
Track: Perl
Date: Thursday, July 10
Time: 4:30pm - 5:15pm
Location: Portland
Oracle offers programmers and database administrators two languages for writing stored procedures: PL/SQL and Java. While there are pros and cons to using each language for internal database applications, neither offers the combination of flexibility and rapid development that Perl programmers take for granted.
extproc_perl extends the capabilities of an Oracle database by allowing PL/SQL functions and procedures to be written in Perl. Rather than using inefficient piping methods (e.g. DBMS_PIPE to a perl process), extproc_perl actually embeds a Perl interpreter in an Oracle external procedure, allowing for per-session interpreter persistence, and tight integration with the calling database. In addition, DBI has been extended to allow queries back to the calling database through the existing connection, making for lightning-quick responses without the overhead of establishing a new session over SQL*Net.
This talk will focus on the following topics:
- The design of extproc_perl and how it integrates into a proprietary database
- Features and limitations
- Usage examples and best practices
- Real world uses of extproc_perl
- Future directions
How did Horwitz get involved with this project? "A coworker commented on how nice it would be to use Perl regular expressions in his Oracle SQL queries. That night the first version of extproc_perl was written, though it was in the 'cute hack' stage at that point. It has since been cleaned up and documented for public consumption.
"What is most interesting about extproc_perl is that it extends a
traditionally closed-source proprietary system with open source software
in a way that is completely supported by the vendor (Oracle). Programmers
and database administrators frustrated with the limitations of Java or
PL/SQL can augment their queries with Perl, and additionally take
advantage of the massive amount of functionality in the CPAN module
archive. It demonstrates the power of a publicly exposed API.
"Attendees will learn how Perl can augment the functionality of existing
SQL queries, and how to embed functionality that is traditionally at the
application layer into the database layer."
Download presentation file
