Oracle PL/SQL Programming, 5th 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 Oracle8i Database, I had the following options.

• In Oracle7 Database (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 Oracle8 Database and later, 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 Oracle8 Database 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, you don’t 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 ...