Hi,
I'm trying to put together some code in Java to replace a C++ program and have run into a problem which seems insurmountable :-(
My code reads*:
-------------------------------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
/* declare class, get connection information; connect; pass the connection to a method which then does: */
Connection connection = ...;
CallableStatement statement = null;
try
{
statement = new connection.prepareCall("BEGIN :1 := :2; :3 := 789; END;");
ParameterMetaData parameterInformation = statement.getParameterMetaData();
int parameterCount = parameterInformation.getParameterCount();
for (int parameterIndex = 1; parameterIndex <= parameterCount; parameterIndex++)
{
String bindValue = /* get the value for bind #parameterIndex */
statement.setString(parameterIndex, bindValue);
statement.registerOutParameter(parameterIndex, Types.VARCHAR);
}
statement.execute();
for (int parameterIndex = 1; parameterIndex <= parameterCount; parameterIndex++)
{
String parameterResult = statement.getString(parameterIndex);
if (parameterResult != null)
{
/* process the resulting parameterResult */
}
}
}
catch (SQLException sqlException)
{
/* report error */
}
finally
{
closeAll(statement);
}
-------------------------------------------------------------------------
[ with apologies for incomplete code; indentation; ... ]
My problem is that when I do this, I end up erroneously clearing the value for the second bind variable because it is only an "in" parameter and has no "out" element. If I try limiting the parameterResult handling or the registerOutParameter handling to parameters by testing the parameter mode thus:
if (parameterInformation.getParameterMode(parameterIndex) != ParameterMetaData.parameterModeIn)
then I get: ORA-17023: Unsupported feature
Limiting by the test for "!= null" is not enough and the getString() is returning "". Getting an empty answer is a valid result of the call and I do not wish to ignore those.
With considerable effort and room for error, I could parse the PL/SQL being passed in to determine which parameters are in, out or in/out. That is a lot of effort with much scope for error. Additionally, if it's a call to a procedure I'd need to identify the procedure call and walk through all_arguments.in_out to ascertain which of the parameters for that procedure is an in, out or in/out. If the PL/SQL block calls more than one procedure ...
I have tried querying for various other solutions, but have not yet found anything approaching a question like this. Most seemed to be about knowing the parameter type to pass in, but we've been coding such that "VARCHAR2" works fine for years, so that's not a problem for me.
Can anyone suggest any ways to work around the lack of insight into the parameter mode, please?
Thanks,
Tim
* OK - so there's a bit more to my code. If I've not included enough please ask for further details. I am using Java 8 and ojdbc7.jar which comes with 12.1.0.2 and connecting to a 12.1.0.2 database, but I also need to support Oracle 11.2.0.3 databases.