Is it possible to get the parameter names in a stored procedure call ?

[salvin francis]

We are developing a database front end application with limited functionality as a web based application.
We want to give the user the flexibility to allow to execute stored procedures.

Now, As I understand, they can be called like this:

Quoted from http://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html :

cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
cs.setString(1, coffeeNameArg);
cs.setFloat(2, maximumPercentageArg);
cs.registerOutParameter(3, Types.NUMERIC);
cs.setFloat(3, newPriceArg);
cs.execute();

What I am interested to know is is there any way I come to know information about parameters to be passed. For example, in the above code, the RAISE_PRICE requires 3 parameters
2 incoming 1 outgoing with types String, Numeric, Numeric


I tried something like:

CallableStatement callable = conn.prepareCall("{call SOME_PROCEDURE()}");
ParameterMetaData parameterMetaData = callable.getParameterMetaData();
System.out.println("checking : "+parameterMetaData.getParameterCount());//TODO: remove sysout statement

whats interesting is that the parameterMetaData.getParameterCount() shows 0.
if I change statement to:

CallableStatement callable = conn.prepareCall("{call SOME_PROCEDURE(?,?)}");

The parameterMetaData.getParameterCount() shows 2

Is there any way for me to come to know the Parameter information given that i know the procedure name ? (Am using Oracle at the moment, but this may change)

[Martin Vajsar]

I don't have any experience with that, but according to the Connection documentation, the getProcedureColumns method should provide this information.

[salvin francis]

Martin Vajsar wrote:I don't have any experience with that, but according to the Connection documentation, the getProcedureColumns method should provide this information.

Found this out the Hard way ... The catalog/schema/procedure names are all Case sensitive !!!

Works like a miracle !!!
Thanks mate.