Stored Procedure Calls in JDBC Programs

This section describes how the Oracle JDBC drivers support the following kinds of stored procedures:

• PL/SQL Stored Procedures

• Java Stored Procedures

PL/SQL Stored Procedures

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

// SQL92 syntax
CallableStatement cs1 = conn.prepareCall
                       ( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
                       ( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
                       ( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
                       ( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:

create or replace function foo (val1 char)
return char as
begin
   return val1 || 'suffix';
end;

Your invocation call in your JDBC program should look like:

Connection conn = DriverManager.getConnection 
                  ("jdbc:oracle:oci8:@<hoststring>", "scott", "tiger");

CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);

For complete sample applications that call PL/SQL stored procedures and functions in SQL92 syntax and Oracle PL/SQL block syntax, see "Calling PL/SQL Stored Procedures--PLSQLExample.java" and "Executing Procedures in PL/SQL Blocks--PLSQL.java".

Java Stored Procedures

You can use JDBC to invoke Java stored procedures through the SQL and PL/SQL engines. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly "published" (that is, have had call specifications written to publish them to the Oracle data dictionary). See the Oracle8i Java Stored Procedures Developer's Guide for more information on writing, publishing, and using Java stored procedures.

---