Oracle PL/SQL Programming, Third Edition

Chapter 23. External Procedures

Backin the Oracle7 days, 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 language feature. Although email has pretty much been a non-issue since Oracle began shipping the built-in UTL_SMTP package, 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

Let’s look at each of these approaches in turn. Java may work well, if it’s fast enough for your application. 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 ...