Announcement Announcement Module
Collapse
No announcement yet.
Oracle StoredProcedure is returning null values Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle StoredProcedure is returning null values

    Hi I am trying to execute stored procedures using Spring JDBC.
    Here's the SP class
    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);
    		}
    
    	}
    I am calling this class from this method.
    Code:
         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;
    	}
    But I am getting null value as O_RETURN_CODE. It's supposed to return 0
    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
    Code:
        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);
    	}
    SQL SP
    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;
    /
Working...
X