Return a Cursor in Oracle Stored Procedure Using JDBC

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

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download