user3368821 user3368821 - 6 months ago 39
SQL Question

Receiving java.sql.SQLException: Invalid column index error while invoking database function

I am trying to invoke a database function using callable statement. The signature of the Stored Procedure is given below;

function DUMMY_FUNCTION (PARAM1 in Number
,PARAM2 in Number
,PARAM3 in varchar2
,PARAM4 in Number
,PARAM5 in Date
,PARAM6 in Number
,PARAM7 out varchar2
,PARAM8 out varchar2)


return varchar2;

and the JAVA code for invoking is as below:



//Load SQl Binaries
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;

public class InvokeProcedure {

public static String callProcedure(int PARAM1,int PARAM2,String PARAM3,int PARAM4,String PARAM5,int PARAM6) throws Exception {
//Defining Parameters
String result = new String();
Connection conn = null;
CallableStatement cs = null;
String outParam = null;
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
try {
conn = getDBConnection();

String proc3Function =
"{? = call P1SCHEMA.DUMMY_PACKAGE.DUMMY_FUNCTION(?, ?, ?, ?, ?, ?) }";
conn.setAutoCommit(false);
cs = conn.prepareCall(proc3Function);
java.sql.Date param5date = new java.sql.Date( sdf.parse(PARAM5).getTime() );
//DefiningInput Parameters
cs.setInt(1, PARAM1);
cs.setInt(2, PARAM2);
cs.setString(3, PARAM3);
cs.setInt(4, PARAM4);
cs.setDate(5, param5date);
cs.setInt(6, PARAM6);
//Defining Output Parameters
cs.registerOutParameter(7, java.sql.Types.VARCHAR);
cs.registerOutParameter(8, java.sql.Types.VARCHAR);
cs.registerOutParameter(9, java.sql.Types.VARCHAR);
//Execute Statement
cs.execute();
//Capture Output
outParam = cs.getString(8);
String I = new String(outParam);
//Set Resultant Array
result = I.toString() + "###" + cs.getString(7) + "###" + cs.getString(8) + "###" + cs.getString(9);
//Check the Reason Code Returned from Stored Procedure
if (outParam == "S")
{
//Roll back Transaction
conn.rollback();
}
else
{
//Commit Transaction
conn.commit();
}

}
catch (Exception e)
{
e.printStackTrace();
}

finally
{
if (cs != null)
{
//Close Connection
cs.close();
}
if (conn != null)
{
//Close Connection
conn.close();
}
}
// Return Array to Client
return result;
}

public static Connection getDBConnection() throws Exception {
String driver = "oracle.jdbc.xa.client.OracleXADataSource";
String url = "jdbc:oracle:thin:@localhost:1539:DEV";
String username = "user";
String password = "password";

Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}


public static void main(String args[]) throws Exception{

InvokeProcedure INP = new InvokeProcedure();
String Args = INP.callProcedure(1,2,"A",3,"2016-01-23",4);

System.out.println(Args);

}
}





While running the java program I am getting the below exception, not sure where i am doing wrong

java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:122)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:288)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:379)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1568)


Thanks

Answer

The function has 8 parameters, but the call has only six (?, ?, ?, ?, ?, ?):

"{? = call P1SCHEMA.DUMMY_PACKAGE.DUMMY_FUNCTION(?, ?, ?, ?, ?, ?) }";

Use:

"{? = call P1SCHEMA.DUMMY_PACKAGE.DUMMY_FUNCTION(?, ?, ?, ?, ?, ?, ?, ?) }";

An then - the positional parameters ? are numbered from left to right.
:

  • {? = - this is the first parameter, it's index is 1,
    use cs.registerOutParameter( 1, java.sql.Types.SomeType)
  • (?, ?, ?, ?, ?, ?, ?, ?) there are consecutively 2,3,4,....8,9 parameters

Use:

cs.setInt(2, PARAM1);
cs.setInt(3, PARAM2);
cs.setString(4, PARAM3);
...

Two last parameters with indexes: 8,9 are OUT parameter - register them using:

cs.registerOutParameter(8, java.sql.Types.VARCHAR);
cs.registerOutParameter(9, java.sql.Types.VARCHAR);
Comments