Monday, March 30, 2009

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

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

// Execute the stored procedure and retrieve the OUT value

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

This is How Procedure is created in oracle

create or replace procedure AddNumbers(a out number, b in number, c in number)
end AddNumbers;