The main aim of this example is to show a means of calling Oracle stored procedures from Java, passing and returning a mix of input and output parameters of various types, and handling the main output of the stored procedure as a resultset.

The following shows a Java program calling a stored procedure contained within an Oracle PL/SQL package. The code uses Oracle's jdbc driver to connect to the database. This component is included in the Oracle installation and is installed in a subdirectory of the Oracle installation directory. For Oracle v8.1.5 the file is \Oracle\Ora81\jdbc\lib\classes111.zip, but the name and installation path may be different for later versions.

The example was created and run with the JavaTM 2 Software Development Kit (SDK), Standard Edition
Version 1.3.1 (available for free from http://java.sun.com/j2se/1.3/) and Oracle V8.1.5. The Java code was compiled and run using the command line tools provided with the SDK.

Cut and paste the text from each of the text areas below. An easy way to do this is to click in the text area, use Ctrl-a to highlight all the text, then Ctrl-c to copy it to the clipboard.

First .. create some test data in the database

Next ... create the PL/SQL package

If necessary use this script to test the package from SQL*Plus

Compile and run the following java program