I'm calling an oracle stored procedure from
java:
This one:
CREATE OR REPLACE PROCEDURE upl_spInsertarForecast(in_COUNTRY_ID IN VARCHAR2,
in_CYEAR IN NUMBER,
in_CMONTH IN NUMBER,
in_YEAR_FORECAST IN NUMBER,
in_MONTH_FORECAST IN NUMBER,
in_SALES_TYPE IN VARCHAR2,
in_SKU_CODE IN VARCHAR2,
in_VOLUME IN NUMBER,
out_existeSKU OUT NUMBER)
IS
contador NUMBER; -- si existe el sku a buscar su valor será mayor que cero
BEGIN
-- out_existeSKU NUMBER Su valor sera 1 si existe, 0 si no, -1 si hubo algun error en la ejecucion
SELECT COUNT(SKU) INTO contador FROM SKU_MAPPING WHERE SKU = in_SKU_CODE AND COUNTRY = in_COUNTRY_ID;
IF CONTADOR > 0 THEN
INSERT INTO UPL_FORECAST (COUNTRY_ID, CYEAR, CMONTH, YEAR_FORECAST, MONTH_FORECAST, SALES_TYPE, SKU_CODE, VOLUME)
VALUES (in_COUNTRY_ID, in_CYEAR, in_CMONTH, in_YEAR_FORECAST, in_MONTH_FORECAST, in_SALES_TYPE, in_SKU_CODE, in_VOLUME);
COMMIT;
out_existeSKU := 1; -- el SKU existe y se hizo el insert
ELSE
out_existeSKU := 0; -- el SKU no existe, entonces no se hace el insert
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
out_existeSKU := -1; -- error en la insercion
END upl_spInsertarForecast;
And this is my Java method:
public boolean insertarForecast(
String country_id, int cyear, int cmonth, int yearForecast, int monthForecast, String salesType, String skuCode, double volume) {
boolean exito = false;
this.conexionBD.conectar();
String query = "{ call ? := UPL_spInsertarForecast(?,?,?,?,?,?,?,?,?) }";
try {
if (this.getConexionBD().getConnection() != null) {
this.getConexionBD().setStatement(this.getConexionBD().getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));
CallableStatement cs = this.getConexionBD().getConnection().prepareCall(query);
// register the type of the out param - an Oracle specific type
cs.registerOutParameter("out_existeSKU", OracleTypes.NUMBER);
cs.setString("in_COUNTRY_ID", country_id);
cs.setInt("in_CYEAR", cyear);
cs.setInt("in_CMONTH", cmonth);
cs.setInt("in_YEAR_FORECAST", yearForecast);
cs.setInt("in_MONTH_FORECAST", monthForecast);
cs.setString("in_SALES_TYPE", salesType);
cs.setString("in_SKU_CODE", skuCode);
cs.setDouble("in_VOLUME", volume);
// execute and retrieve the result set
cs.execute(query);
//this.getConexionBD().getStatement().executeUpdate(oracleQuery);
int resultado = cs.getInt(1);
if (resultado == 0){
System.out.println("Resultado = " + resultado);
exito = false;
} else if (resultado == 1 ){
System.out.println("Resultado = " + resultado);
exito = true;
} else if (resultado == -1) {
System.out.println("Resultado = " + resultado);
exito = false;
}
this.getConexionBD().cerrarConexion();
//this.getConexionBD().getStatement().execute("call.UPL_SPINSERTARFORECAST("+ country_id +", "+ cyear +", "+ cmonth +", "+ yearForecast +", "+ monthForecast +", "+ ")");
} else {
this.getConexionBD().cerrarConexion();
exito = false; // error en la operación
}
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("No se pudo accesar a la base da datos");
this.getConexionBD().cerrarConexion();
exito = false; // error en la operación
}
return exito;
}
I have checked the method and I think that it is ok, and also I've already tested the stored procedure and it is ok, too.
But I'm getting this exception:
java.sql.SQLException: ORA-01008: not all variables bound
What is the mistake?
Where am I wrong?
Who can help me?
Thank you.