sqoop oracle导数异常-----java.sql.SQLException: Missing IN or OUT parameter at index:: 2

  工作中同事使用sqoop 导入mysql表没问题，换成oracle就报错，让我帮忙看下，抽取oracle数据出现如下异常：

Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 2
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1821)
    at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:10058)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.addBatch(OraclePreparedStatementWrapper.java:1360)
    at org.apache.sqoop.mapreduce.UpdateOutputFormat$UpdateRecordWriter.getPreparedStatement(UpdateOutputFormat.java:174)
    at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.execUpdate(AsyncSqlRecordWriter.java:153)
    at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:194)
    ... 8 more   
 

sqoop 语句： 

sqoop export --connect jdbc:oracle:thin:@xxxx:1521:lakalasit --table test --username xxx--password xxx --export-dir /user/hive/warehouse/xxx.db/xx_test --update-key mobile --update-mode allowinsert --columns mobile,sign --fields-terminated-by \t --input-lines-terminated-by \n --input-null-string '\\N' --input-null-non-string '\\N' -m 1

 

开始也是没看出sqoop哪里有问题，没办法为了弄清原因只能去看源码了，源码如下:  

 

 

 

sqoop oracle导数获取表字段是根据表名从系统表ALL_TAB_COLUMNS 获取字段，而这张表的值都是大写，而sqoop语句中表名为小写导致查询不到字段，导致插入数据时出现如上异常。

如果--update-key ，--columns 中相同字段大小写不统一，会导致生成的sqoop 动态生成的java类异常编译失败。

下面是动态生成的java类部分,生成的字段属性中只会有一个mobile,所以下方代码会出现编译失败:

public int write(PreparedStatement __dbStmt, int __off) throws SQLException {
    JdbcWritableBridge.writeString(MOBILE, 1 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_SIGN, 2 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_DEADLINE, 3 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(mobile, 4 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_SIGN, 5 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_DEADLINE, 6 + __off, 12, __dbStmt);
    return 6;
  }
  public void write0(PreparedStatement __dbStmt, int __off) throws SQLException {
    JdbcWritableBridge.writeString(MOBILE, 1 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_SIGN, 2 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_DEADLINE, 3 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(mobile, 4 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_SIGN, 5 + __off, 12, __dbStmt);
    JdbcWritableBridge.writeString(REUSABLE_DEADLINE, 6 + __off, 12, __dbStmt);
  }

总结：sqoop 导入oracle 表名必须大写  --table 大写表名   --update-key  --columns 字段必须统一大小写