Chapter 8. Embedding Perl into PL/SQL

PL/SQL is Oracle's own programming language. It©'s a powerful procedural language that is tightly integrated with SQL ("PL/SQL" stands for "Procedure Language extension to Structured Query Language"). It offers a full range of datatypes, conditional and sequential control, looping, exception handling, modular code constructs, user-defined datatypes (such as objects), and a lot more. It's likely that you have a whole raft of PL/SQL programs that you use daily to perform Oracle database administration.

With all of its advantages, PL/SQL has a few limitations as well. There are things PL/SQL programmers want to do that turn out to be impossible, or at least inefficient, to do with PL/SQL alone. As you might suspect, we think Perl is the ideal supplement to PL/SQL. By calling Perl modules from your PL.SQL programs (more accurately, by embedding Perl within PL/SQL), your PL/SQL programs acquire many additional capabilities:

Mailing and the Internet

It is sometimes difficult to email or connect to the Internet from within PL/SQL, but there are hundreds of helpful connectivity options available through Perl.

Encryption

PL/SQL offers very little built-in security, but Perl provides access to many different types of encrypted security systems. (Later in this chapter we'll show how we use the MD5 message digest algorithm from within PL/SQL via Digest::MD5.)

Operating system commands

Every now and again — perhaps while running complex backup maneuvers — we run into situations where we'd like to be able to run system commands directly via the operating system. Perl provides this ability (though, as we'll see, we will have to be careful how we use it).

Access to C libraries

Perl has literally hundreds of modules providing API access to the world's most popular C libraries. Allowing PL/SQL to access these via Perl extends PL/SQL's functional horizons.

Regular expressions

Although Oracle is catching up with regular expressions, particularly with some of the PL/SQL Web Toolkit packages, such as OWA_PATTERN and OWA_TEXT, Perl's regular expression engine is generally held to be the world's most powerful. It would be nice to get access to it via PL/SQL.

Overall performance

Before external procedures arrived on the scene, it was a laborious process (usually necessitating wrestling with the DBMS_PIPE built-in package), to get PL/SQL to talk to the outside world. It involved complex listener setups, excellent 3GL language skills, plus a good sense of humor and a large Simpsons video collection for post-installation de-stress relaxation.^[1]

^[1] It actually was possible to involve Perl in these older solutions, as we did at the following web page, but we believe that extproc_perl is a much better solution: http://www.cybcon.com/~jkstill/util/debug_pipe/debug_pipe.html.

With external procedures now capable of linking to Perl, we save a lot of effort, and our programs run much faster.