Oracle PL/SQL Programming, 6th Edition

Chapter 28. 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.

• Expose the program as a web service.

• 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. Queuing is a very interesting technology, but even if you are simply using plain tables, this approach requires two Oracle database 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 ...