Help with: java.sql.SQLException: ORA-01008: not all variables bound

[Jose Araya]

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.

[Fatih Keles]

Hi Jose,

You are calling a procedure, so it does not return a value, it has out parameters. So should call it like this

 String query = "{ call UPL_spInsertarForecast(?,?,?,?,?,?,?,?,?) }";  

and register 9.th parameter for output.

Regards,

Fatih.

[Jose Araya]

Well I changed the line and I'm getting the same error message again...

[Jan Cumps]

Jose Araya wrote:Well I changed the line and I'm getting the same error message again...

And did you do this : and register 9.th parameter for output. ?

[Jan Cumps]

... and Welcome to JavaRanch !

[Jose Araya]

Yes I did.

This is my method now:

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);



// set the in param (s)
cs.setString(1, country_id);
cs.setInt(2, cyear);
cs.setInt(3, cmonth);
cs.setInt(4, yearForecast);
cs.setInt(5, monthForecast);
cs.setString(6, salesType);
cs.setString(7, skuCode);
cs.setDouble(8, volume);

cs.registerOutParameter(9, OracleTypes.NUMBER);

// execute and retrieve the result set
cs.execute(query);


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;
}




Thank you.

[Jose Araya]

The stored procedure is the same.

[Fatih Keles]

Hi Jose,

You registered 9.th parameter for output. So you should use it

 int resultado = cs.getInt(9);  

Regards,

Fatih.

[Jose Araya]

Hi Fatih,

Thaks for helping me...

I read your post, so I changed the line and guess what? I get the error message again...

The error message appears in this line:

cs.execute(query);


Regards and thank you again,

Jose.

[Craig Jackson]

Try this:

   
cs.execute();
 

Instead of this:

   
cs.execute(query);
 

[Jose Araya]

Craig Jackson wrote:Try this:

   
cs.execute();
 

Instead of this:

   
cs.execute(query);
 

Thank you very much!!!

It works!!!

Right now I'm doing this --> jajaja....