Oracle PL/SQL Programming, 4th Edition

Chapter 27. External Procedures

In the early days of PL/SQL, it was common to hear the question “Can I call whatever from within Oracle?” Typically, whatever had something to do with sending email, running operating-system commands, or using some non-PL/SQL feature. Although email has pretty much been a nonissue since Oracle began shipping the built-in UTL_SMTP and UTL_MAIL packages, there are by now quite a handful of alternatives to calling “whatever.” Here are the most common approaches:

• Write the program as a Java stored procedure and call the Java from PL/SQL.

• Use a database table or queue as a place to store the requests, and create a separate process to read and respond to those requests .

• Use a database pipe and write a daemon that responds to requests on the pipe.

• Write the program in C and call it as an external procedure.

Java may work well, and it can be fast enough for many applications. Queueing is a very interesting technology, but even if you are simply using plain tables, this approach requires two Oracle sessions: one to write to the queue and one to read from it. Moreover, two sessions means two different transaction spaces, and that might be a problem for your application. Database pipe-based approaches also have the two-session problem, not to mention the challenge of packing and unpacking the contents of the pipe. In addition, handling many simultaneous requests using any of these approaches might require you to create your own listener and process-dispatching system. ...