How to Call Oracle Procedure (With Return Query) in JAVA

My Oracle Procedure :

CREATE OR REPLACE PROCEDURE USPS_GET_DATA (PRC OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN PRC FOR SELECT * FROM TBLM_DATA;
END;

Don’t forget to grant your oracle user to execute SP:

GRANT EXECUTE ON USPS_GET_DATA TO USERORACLE;

Sample execution in SQL Developer:

variable rc2 refcursor;
EXEC USPS_GET_DATA(:rc2);
print rc2;

My JAVA Code:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import oracle.jdbc.OracleTypes;
 
public class SampleSPOracle
{
	private Connection con;
 
	public void openConnection(String module) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String connectionString = "jdbc:oracle:thin:@localhost:1521:xe";
		String username = "username";
		String password = "password";		
 
		con = DriverManager.getConnection(connectionString, username, password);
	}
 
	public void execSPQuery(String module, String spName, List<Map<String, Object>> spParameter) throws Exception {
		// open connection
		openConnection("");
 
		CallableStatement cs = con.prepareCall(spName);
		int paramCount = 1;
		if (spParameter.size() > 0) {
			for (Map<String, Object> param : spParameter) {
				switch (param.get("DATA_TYPE").toString()) {
					case "VARCHAR":{
						cs.setString(paramCount, param.get("DATA_VALUE").toString());
						break;
					}
					default:{break;}
				}
				paramCount++;
			}
		}
 
		// set param for CURSOR position, i always put in the end of parameter
		cs.registerOutParameter(paramCount, OracleTypes.CURSOR);
 
		// exec sp
		cs.execute();
 
		// get resultset or data
		ResultSet rs = (ResultSet) cs.getObject(1);
		ResultSetMetaData rsmd = rs.getMetaData();
 
		while(rs.next()) {
			StringBuilder sb = new StringBuilder();
			for (int i=1; i <= rsmd.getColumnCount(); i++) {
				sb.append(rs.getString(rsmd.getColumnLabel(i))).append("#");
			}
			System.out.println(sb);
		}	
 
		// close connection
		rs.close();
		con.close();
	}
 
	public void closeConnection() throws Exception {
		con.close();
	}
 
	public static void main(String[] args) throws Exception {
		SampleSPOracle u = new SampleSPOracle();
 
		List<Map<String, Object>> spParam = new ArrayList<>();
 
		u.execSPQuery("module", "{call USPS_GET_DATA(?)}", spParam);		
	}
}

CMIIW :).

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro