Resilient Oracle PL/SQL

Chapter 5. Calling PL/SQL

Previous chapters have illustrated the ways in which PL/SQL can be:

• Created

• Refactored

• Debugged

• Saved to the database

• Executed using SQL Developer

In this chapter, I discuss two of the important PL/SQL integration (and execution) models.

Two PL/SQL Integration Models

It’s not difficult to invoke packaged PL/SQL code in the form of functions and procedures. There are two approaches I’ll look at:

• Running PL/SQL as a standalone database job

• Running PL/SQL using Java code

Both of these integration patterns are in common use. The standalone job approach has the merit of being independent of other integration languages, that is, it is pure PL/SQL. With the job-based model, you don’t have any of the headaches associated with PL/SQL invocation from high-level languages. All of the work can be done using SQL Developer and the Oracle Database.

The Java code approach is more complex but has its own advantages. For example, it allows for PL/SQL business logic to be used directly in Java application code. One example of this could be calling a PL/SQL function from Java to return some sales data (such as an invoice). The data can then be used in the subsequent Java code. Calling PL/SQL in this way provides us with a means of protecting the investment in PL/SQL and also using the latter in our Java code. The Java code then does not need to make any access to the database, other than calling the PL/SQL packaged code.

Both the standalone job and Java ...