Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section describes how the Oracle JDBC drivers support stored procedures and includes these subsections:
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 beginreturn 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);
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.