O'Reilly Open Source Convention.
Books Safari Bookshelf Conferences O'Reilly Network

Arrow Home
Arrow Registration
Arrow Speakers
Arrow Keynotes
Arrow Tutorials
Arrow Sessions
Arrow At-a-Glance
Arrow BOFs
Arrow Events
Arrow Community
Arrow Exhibitors
Arrow Sponsors
Arrow Hotel/Travel
Arrow Venue Map
Arrow See & Do
Arrow Press
Arrow Mail List

O'Reilly Open Source Convention


extproc_perl: Embedding Perl in Oracle
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

O'Reilly Home | Privacy Policy

© 2003, O'Reilly Media, Inc.