Using Stored Procedures in JDBC Programs

This section describes how the Oracle JDBC drivers support stored procedures and includes these subsections:

• 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 escape syntax. The following PL/SQL calls are all available from any Oracle JDBC driver:

// SQL92 Syntax
CallableStatement cs1 = conn.prepareCall
     
                       ( "{call proc (?,?)}" ) ;

     CallableStatement cs2 = conn.prepareCall
     
                       ( "{? = call func (?,?)}" ) ;

     // Oracle Syntax
CallableStatement cs3 = conn.prepareCall
     
                       ( "begin proc (:1, :2); end;" ) ;

     CallableStatement cs4 = conn.prepareCall
     
                       ( "begin :1 := func(:2,:3); end;" ) ;

     
    

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 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 = proc.getString(1);
    

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. See the Oracle8i Java Stored Procedures Developer's Guide for more information on using Java stored procedures.

---