Oracle PL/SQL Programming, Third Edition

A Simple Demonstration

Before diving into the details, let’s walk through all the steps needed to access Java from within PL/SQL. In the process, I’ll introduce the various pieces of technology you need to get the job done.

Say that I need to be able to delete a file from within PL/SQL. Prior to Oracle 8.1, I had the following options:

• In Oracle 7.3 (and above), I could send a message to a database pipe, and then have a C listener program grab the message (“Delete file X”) and do all the work.

• In Oracle 8.0, I could set up a library that pointed to a C DLL or shared library, and then from within PL/SQL call a program in that library to delete the file.

The pipe technique is handy, but it is a clumsy workaround. The external procedure implementation in Oracle 8.0 is a better solution, but it is also less than straightforward, especially if you don’t know the C language. So the Java solution looks as if it might be the best one all around. Although some basic knowledge of Java is required, one does not need the same level of skill that would be required to write the equivalent code in C. Java comes with prebuilt ( foundation) classes that offer clean, easy-to-use APIs to a wide array of functionality, including file I/O.

Here are the steps that I will perform in this demonstration:

1. Identify the Java functionality I need to access.

2. Build a class of my own to make the underlying Java feature callable through PL/SQL.

3. Compile the class and load it into the database.

4. Build a PL/SQL program ...