How to return a cursor in oracle stored procedure using JDBC Callable Statement :

Steps :

#1 : Create a stored procedure :

CREATE OR REPLACE PROCEDURE getCursor(

         			  curr OUT SYS_REFCURSOR)
		IS
		BEGIN

  			OPEN curr FOR
  			SELECT * FROM STUDENT WHERE SNAME;

		END;
		/

#2 : JDBC piece of code :

package com.helicaltech.jdbc;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

public class CURSOR_Example {

        private static final String driver = "oracle.jdbc.driver.OracleDriver";
        private static final String DB_conn = "jdbc:oracle:thin:@localhost:1521:XE";
        private static final String user = "user";
        private static final String pwd = "password";

        public static void main(String[] argv) {

                Connection connection = null;
                CallableStatement callableStatement = null;
                ResultSet rs = null;

                String getCursor = "{call getCursor(?,?)}";

                try {
                        dbConnection = getDBConnection();
                        callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);

                     
                        callableStatement.registerOutParameter(1, OracleTypes.CURSOR);

                        // execute getDBUSERCursor store procedure
                        callableStatement.executeUpdate();

                        // get cursor and cast it to ResultSet
                        rs = (ResultSet) callableStatement.getObject(1);

                        while (rs.next()) {
                                String sid = rs.getString("sid");
                                String SNAME = rs.getString("SNAME");
                                
                                System.out.println("Student ID : " + sid);
                                System.out.println("Student Name : " + SNAME);
                                
                        }

                } catch (SQLException e) {

                        System.out.println(e.getMessage());

                } finally {

                        if (rs != null) {
                                rs.close();
                        }

                        if (callableStatement != null) {
                                callableStatement.close();
                        }

                        if (dbConnection != null) {
                                dbConnection.close();
                        }

                }

        }

        private static Connection getDBConnection() {

                Connection dbConnection = null;

                try {

                        Class.forName(driver);

                } catch (ClassNotFoundException e) {

                        System.out.println(e.getMessage());

                }

                try {

                        dbConnection = DriverManager.getConnection(
                                conn, user,pwd);
                        return dbConnection;

                } catch (SQLException e) {

                        System.out.println(e.getMessage());

                }

                return dbConnection;

        }

}



Thanks,
Rupam Bhardwaj

Leave a Reply