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