calling Oracle Function, Type conversion

[Pavan Kumar]

Friends,
I have to invoke an Oracle Funtion that has 37 parameters. I have carefully set the parameters for my Callable Statement Object, but I was getting a SQLException Not all variables are bound.

I have tried to cross check the data types and made sure (or so I thought) the types are appropriate, after a very long exercise I was still greeted with the same Exception.

I have come up with a work around, after discussing with one of collegues. I would like to know your opinions and suggestion. I am hopeful that somebody will point me in the right direction that is easier and cleaner.

My solution looks like following

 CallableStatement stmt = conn.prepareCall(myFunction);
stmt.registerOutParameter(1, Types.INTEGER);
OracleDatabaseMetaData dbMetaData = (OracleDatabaseMetaData) conn.getMetaData();

ResultSet procsRes = dbMetaData.getProcedures(null,null,"%");
ResultSetMetaData rsM = procsRes.getMetaData();
while(procsRes.next()){
if( procsRes.getString("PROCEDURE_NAME").equalsIgnoreCase(myFunctionName) ){
ResultSet rs = dbMetaData.getProcedureColumns(null,null,myFunctionName,"%");
ResultSetMetaData meta = rs.getMetaData();
while(rs.next()){
columnTypeMapping.put(rs.getString("COLUMN_NAME"),rs.getString("TYPE_NAME"));
}
}
}
 

columnTypeMappings is a LinkedHashMap, I iterate through the map and based on the type name I invoke
stmt.setxxxx(index, value);

is there any better way of doing this?

Thanks very much for your attention and time.

Regards,
Cnu
[ June 14, 2005: Message edited by: cnu sri ]