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.

Example:

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


Thanks
R Vashi.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s