Java : Calling a Stored Procedure in a Database

Simple program for calling a stored procedure in a oracle database

In this example we are using both IN and OUT parameters, Simply passing two IN parameters and getting Addition as OUT parameter ie a = b + c.

//Here is complete tested code for Procedure.java

import java.sql.*;
public class Procedure
{
public static void main(String[] args) throws Exception
{
Connection con;

CallableStatement cs;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:odbc:oracledsn","scott","tiger");//Step 2: Making Connection

cs=con.prepareCall("{call AddNumbers(?,?,?)}");//Calling procedure

cs.registerOutParameter(1,Types.INTEGER);//Registering 1st parameter
// Register the type of the OUT parameter

//2nd parameter
cs.setInt(2,15);//here values can also be taken as command line argument

//3rd parameter
cs.setInt(3,20);

// Execute the stored procedure and retrieve the OUT value
cs.execute();

System.out.println(cs.getInt(1));//output as 35 ie 15+20
}
}
//end




This is How Procedure is created in oracle


create or replace procedure AddNumbers(a out number, b in number, c in number)
as
begin
a:=b+c;
end AddNumbers;