Call Oracle Stored Procedure using JDBC CallableStatement

Calling stored procedure is same as calling stored function as mentioned in previous post. In this example we will perform INSERT, UPDATE, DELETE on Account table. Code snippets to show you how to call a Oracle stored procedure via JDBC CallableStatement, and how to pass IN parameters from Java to stored procedure.

1) Stored Procedure

A stored procedure in Oracle database. Later, calls it via JDBC.



create or replace procedure spAccount(p_acode number,p_aname varchar,
p_obalance number,p_descflag varchar)
IS
begin

--INSERTING METHOD
  IF p_descflag = 'I' THEN
    insert into account values (p_acode,p_aname,p_obalance);
  END IF;
  --UPDATING METHOD
  IF p_descflag = 'U' THEN
    update account aa set aa.acct_name = p_aname,
    aa.obalance = p_obalance
    where aa.acct_code = p_acode;
  END IF;
    --UPDATING METHOD
  IF p_descflag = 'D' THEN
     delete from account aa where aa.acct_code = p_acode;
  END IF;

  commit;
end spAccount; 




2) Calls Stored Procedure via CallableStatement

The Account class contains:-
i)   Oracle JDBC Connection method
ii)  Insert, Update, Delete methods
iii) Setter and Getter methods
iv)  Perform Insert, Update, Delete method through stored procedure using CallableStatement 


import oracle.jdbc.pool.*;
import java.sql.*;

public class Account{

private OracleDataSource ODS;
private Connection CN;

private int Acode;
private String Aname;
private double Obalance;
private String descflag;

 public Account(){
 
 try{
 ODS = new OracleDataSource();
 ODS.setUser("adf");
 ODS.setPassword("adf");
 ODS.setURL("jdbc:oracle:thin://@127.0.0.1:1521:XE");
 CN = ODS.getConnection();
 }
 catch (Exception ex)
 {
  System.out.println("oops!"+ex.getMessage());
 }
}

 public void setAcode( int a1){
  Acode = a1;
 }

 public int getAcode(){
  return Acode;
 }

 public void setAname( String a1){
  Aname = a1;
 }

 public String getAname(){
  return Aname;
 }

 public void setObalance( double a1){
  Obalance = a1;
 }

 public double getObalance(){
  return Obalance;
 }

 public void setdescflag( String a1){
  descflag = a1;
 }

 public String getdescflag(){
  return descflag;
 }

 public void addAccount(){
 
 if (descflag == "I"){
 try{
 CallableStatement SQLPROC = 
                        CN.prepareCall("{ call spAccount(?,?,?,?)}");
 SQLPROC.setInt(1,Acode);
 SQLPROC.setString(2,Aname);
 SQLPROC.setDouble(3,Obalance);
 SQLPROC.setString(4,descflag);
 SQLPROC.executeUpdate();
 }
 catch (Exception ex){
 System.out.println("Oops! "+ ex.getMessage());
 }
 }
 }
 
 public void editAccount(){
 if (descflag == "U") {
 try{
 CallableStatement SQLPROC = 
                        CN.prepareCall("{ call spAccount(?,?,?,?)}");
 SQLPROC.setInt(1,Acode);
 SQLPROC.setString(2,Aname);
 SQLPROC.setDouble(3,Obalance);
 SQLPROC.setString(4,descflag);
 SQLPROC.executeUpdate();
 }
 catch (Exception ex){
 System.out.println("Oops! "+ ex.getMessage());
 }
 }
 }

 public void delAccount(){
 if (descflag == "D"){
 try{
 CallableStatement SQLPROC = 
                        CN.prepareCall("{ call spAccount(?,?,?,?)}");
 SQLPROC.setInt(1,Acode);
 SQLPROC.setString(2,Aname);
 SQLPROC.setDouble(3,Obalance);
 SQLPROC.setString(4,descflag);
 SQLPROC.executeUpdate();
 }
 catch (Exception ex){
 System.out.println("Oops! "+ ex.getMessage());
 }
 }
 }

}

3) Drive Class


public class driveclass{


 public static void main( String expsp[] ){ new driveclass();}

 driveclass(){
 Account act = new Account();
 act.setAcode(3);
 act.setAname("Multan Branch Account");
 act.setObalance(15000.12);
 act.setdescflag("I");

 act.addAccount();
 act.editAccount();
 act.delAccount();
 }
}

Done. When above JDBC example is executed, a new record will be inserted into database via stored procedure "spAccount". Change the Flag "U" to update and "D" to delete the records from the database.

Comments

Popular posts from this blog

Create Tables in Oracle Database using TOAD

How to create Oracle stored Procedures using TOAD for Oracle

How to Create Packages in Oracle Database using TOAD for Oracle