java - 接收java.sql.SQLException : Invalid column index error while invoking database function

Question

我正在尝试使用可调用语句调用数据库函数。存储过程的签名如下；

 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) 

返回varchar2;

调用的JAVA代码如下:

//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);

}
}

运行java程序时出现以下异常，不确定我哪里做错了

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)

谢谢

Answer 1

该函数有 8 个参数，但调用只有 6 个(?, ?, ?, ?, ?, ?):

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

用途:

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

然后 - 位置参数 ? 从左到右编号。
:

• {? = - 这是第一个参数，索引为1，
  使用cs.registerOutParameter(1, java.sql.Types.SomeType)
• (?, ?, ?, ?, ?, ?, ?, ?) 连续有 2,3,4,....8,9 个参数

用途:

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

最后两个参数的索引:8,9 是 OUT 参数 - 使用以下方式注册它们:

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