Stored procedure in oracle problem

[Yanine Arellano]

Hi everyone, i have a problem with my java code wich calls a store proc. from oracle. I check it and everything seems fine although i get a SQLException Invalid column index.
I don�t know what else to do, please help me out.
The following is my code:

************************************************** *************
public class OracleConn {

public static void main(String args[]){
try{
OracleConn oc = new OracleConn();
Connection conn = oc.getConnection();

//IN OUT VARIABLES

String codigo_unico_bme = "";
String tipo_fondo = "BSP";
int cuenta = 84188;
int subcta = 1;
//Date fechahora_proceso = null;
String moneda_deposito = "USD";
int valor_deposito = 1000;

CallableStatement ct = conn.prepareCall("{call fon.pck_mt3.db_mt3_deptran(?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

ct.registerOutParameter(1, Types.VARCHAR);
ct.setString(1, codigo_unico_bme);

ct.setString(2,"");
ct.setString(3,"LPZ");
ct.setString(4,"");
ct.setString(5,"9999");
ct.setString(6,""); /

ct.registerOutParameter(7, Types.VARCHAR);
ct.setString(7,tipo_fondo);
ct.registerOutParameter(8, Types.INTEGER);
ct.setInt(8,cuenta);
ct.registerOutParameter(9, Types.INTEGER);
ct.setInt(9,subcta);

//ct.setDate(10,new Date(System.currentTimeMillis()));
ct.setDate(10,null);
ct.setDate(11,null);
ct.registerOutParameter(11, Types.DATE);
ct.registerOutParameter(12, Types.VARCHAR);
ct.setString(12, moneda_deposito);
ct.registerOutParameter(13, Types.INTEGER);
ct.setInt(13,valor_deposito);
ct.setString(14,"DEP");
ct.setString(15,"");
ct.setString(16,"");
ct.setString(17,"");
ct.setString(18,"");
ct.registerOutParameter(19, Types.INTEGER);
ct.registerOutParameter(20, Types.VARCHAR);
ct.registerOutParameter(21, Types.DATE);
ct.registerOutParameter(22, Types.VARCHAR);
ct.registerOutParameter(23, Types.VARCHAR);
ct.registerOutParameter(24, Types.VARCHAR);
ct.registerOutParameter(25, Types.INTEGER);
ct.registerOutParameter(26, Types.INTEGER);
ct.registerOutParameter(27, Types.INTEGER);
ct.registerOutParameter(28, Types.INTEGER);
ct.registerOutParameter(29, Types.INTEGER);
ct.registerOutParameter(30, Types.VARCHAR);
ct.registerOutParameter(31, Types.VARCHAR);
ct.registerOutParameter(32, Types.INTEGER);
ct.setString(33,"");

boolean estado = ct.execute();

if (estado) {
System.out.println("Succes");
} else {
System.out.println("Wrong!");

}

System.out.println("Error Code: "+ct.getString(10));
System.out.println("Message : "+ct.getString(11));

ct.close();
conn.close();
} catch (Exception e){
System.out.println("Error:"+e.getMessage());
e.printStackTrace();
}
}
}
************************************************** *************

I get this error mesages:

************************************************** *************
Wrong!
Error:�ndice de columna no v�lido
java.sql.SQLException: �ndice de columna no v�lido
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:269)
at oracle.jdbc.driver.OracleStatement.prepare_for_new _get(OracleStatement.java:3491)
at oracle.jdbc.driver.OracleStatement.getStringValue( OracleStatement.java:3728)
at oracle.jdbc.driver.OracleCallableStatement.getStri ng(OracleCallableStatement.java:493)
at com.oracle.conn.OracleConn.main(OracleConn.java:14 4)
************************************************** *************


Please help me out, i�ll appreciate it.

[Jeanne Boyarsky]

Yanine,
Welcome to JavaRanch!

For in/out parameters, I'm not sure if order matters. I usually see it written as:

ct.setString(1, codigo_unico_bme);
ct.registerOutParameter(1, Types.VARCHAR);

rather than the other way around.

Often with a big problem, simplifying it helps making the solution more obvious. For example, what happens if you make a copy of the stored proc with only 5 parameters and call that. Does it still exhibit the problem? Then you can add/remove parameters to narrow down which part of the code is causing the problem.

Finding a typo or the like in 33 parameters is a lot harder. Also, if you post the simplified example here, people will be more likely to be able to determine what is wrong.

[Yanine Arellano]

Ok, Thanks a lot.
For anyone who is interested, the problem was that i was declaring some date variables, but then at the end of the code i was calling them as string.
Have a nice day, bye bye