Oracle8i JDBC Developer's Guide and Reference
Release 8.1.5

A64685-01

Library

Product

Contents

Index

Prev  Chap Top Next

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

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.




Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index