Oracle Stored procedures:
CREATE OR REPLACE PROCEDURE getcity ( citycode in VARCHAR2, ref_cursor out sys_refcursor/ * This sys_refcursor type is in the Sys.standard package */) Asbegin OPEN ref_cursor for SELECT * from tb_city_code WHERE City_code = Citycode; end;/
Java calling code:
try { drivermanager.registerdriver (new Oracle.jdbc.OracleDriver ()); Connection conn = Drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:WJW", "SMS", "SMS"); CallableStatement stmt = Conn.preparecall ("BEGIN getcity (?,?); END; "); CallableStatement stmt = Conn.preparecall ("{Call getcity (?,?)}"); It is not possible to implement multi-line Syntax stmt.setstring (1, "021") with this calling method; Stmt.registeroutparameter (2, -10/* oracletypes.cursor = 10 */); REF CURSOR (oracletypes.cursor==-10) stmt.execute (); ResultSet rs = (ResultSet) stmt.getobject (2); while (Rs.next ()) { System.out.println (rs.getstring ("City_name")); } Rs.close (); rs = null; Stmt.close (); stmt = null; Conn.close (); conn = null; } catch (SQLException e) { System.out.println (e.getlocalizedmessage ()); }
How to make Oracle's stored procedure return a result set