Hi I am trying to execute stored procedures using Spring JDBC.
Here's the SP class
I am calling this class from this method.Code:class IncrementExtraBalanceStoredProcedure extends StoredProcedure { /** * @param jdbcTemplate * @param procedureName */ public IncrementExtraBalanceStoredProcedure(JdbcTemplate jdbcTemplate, String procedureName) { super(jdbcTemplate, procedureName); declareParameter(new SqlOutParameter(O_RETURN_CODE, Types.INTEGER)); declareParameter(new SqlParameter(P_NUMEC, Types.INTEGER)); declareParameter(new SqlParameter(P_GBYTES, Types.INTEGER)); compile(); } /** * @param inputBean * @return resultObjects */ public Map<String, Object> execute(RateLimitLogBean inputBean) { Map<String,Object> sqlMap = new HashMap<String,Object>(); sqlMap.put(P_NUMEC, inputBean.getNumec()); sqlMap.put(P_GBYTES, inputBean.getGb()); return super.execute(sqlMap); } }
But I am getting null value as O_RETURN_CODE. It's supposed to return 0Code:public int incrementExtraBalance(RateLimitLogBean inputBean) { IncrementExtraBalanceStoredProcedure procedure = new IncrementExtraBalanceStoredProcedure(this.jdbcTemplate, "RATELIMIT_OWN.increment_extra_balance"); Map<String, Object> resultMap = procedure.execute(inputBean); if (!StringUtils.isEmpty(resultMap)) { return ((Integer) resultMap.get(O_RETURN_CODE)).intValue(); } return -1; }
The execution of this function from Toad - Oracle Db
>var z number
>exec RATELIMIT_OWN.unlimit_contract (0123,:z)
>print z
*I got 0 as output in Toad.*
Why I am getting null value as return from Java code (no sql exceptions).
Is there anything wrong with code?
Update:
native calls returning proper output
SQL SPCode:public void unlimitContract(RateLimitLogBean inputBean, boolean load) throws SQLException { String sql = "{call RATELIMIT_OWN.unlimit_contract (?,?)}"; CallableStatement callableStatement = this.dataSource.getConnection().prepareCall(sql); callableStatement.setInt(1, 0123); callableStatement.registerOutParameter(2, java.sql.Types.INTEGER); callableStatement.executeUpdate(); int resultCode = callableStatement.getInt(2); }
Code:CREATE OR REPLACE PROCEDURE RATELIMIT_OWN.increment_extra_balance (p_numec IN NUMBER, p_gbytes IN NUMBER, o_return_code OUT NUMBER) AS message logs.errormsg%TYPE; BEGIN update balance set extrabalance=extrabalance+(p_gbytes*1073741824),limited=0 WHERE numec = p_numec; IF SQL%ROWCOUNT = 0 THEN o_return_code:=1; ELSE o_return_code:=0; message := 'Cops added ' || p_gbytes || ' gb extra volume'; INSERT INTO logs (logid, eventid, origin, numec, VALUE, errormsg) VALUES (seq_log.NEXTVAL, 'NEXTRAROV', 'increment_extra_balance', p_numec, p_gbytes, message); END IF; commit; EXCEPTION WHEN OTHERS THEN o_return_code := SQLCODE; ROLLBACK; END; /


Reply With Quote
