Calling a stored procedure from JDBC

Hi All,

The below example will demonstrate the steps of invoking stored procedure from Oracle using JDBC API’s.

Callable Statement: This interface is used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs.

Note: A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.


import java.sql.*;
Connection con=DBFactory.getPoolConnection();

CallableStatement cstmt=con.prepareCall({“call DB_PACKAGE.my_proc_name(?,?,?,?});

cstmt.setString(1,”xyz”);//set the input paramteres

cstmt.setString(2,”jkl”); //set the input paramteres

cstmt.registerOutParameter(3,OracleTypes.CUSRSOR); // Register the Outut parameter {CURSOR}

cstmt.registerOutParameter(4,OracleTypes.VARCHAR); //Register the Outut parameter {ERROR MESSAGE}

cstmt.execute(); // execute the procedure

if(cstmt.getString(4)!=null){ //check for any error message
throw SQLException(cstmt.getString(4));

ResultSet rs=cstmt.getObject(3) or cstmt.getResultSet(3); or cstmt.getCursor(3);

// Read the result set.

Hope this helps

R Vashi.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.