Calling Stored Procedures

Oracle Java Database Connectivity (JDBC) drivers support the processing of PL/SQL stored procedures and anonymous blocks. They support Oracle PL/SQL block syntax and most of JDBC escape syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

As an example of using the 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:

You can call this stored function in a Java program as follows:

The following sections describe how you can use stored procedures in the sample application in this guide:

Example 6-3 Calling Stored Procedures

// JDBC syntaxCallableStatement 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

Example 6-4 Creating a Stored Function

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

Example 6-5 Calling a Stored Function in Java

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@<hoststring>");
ods.setUser("hr");
ods.setPassword("hr");
Connection conn = ods.getConnection();
CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);