java与oracle通过数组交互,在p0lsql中创建了一个type is varray(1000) or varchar2(200); 在java中创建ArrayDescriptor来进行字符集转换。现在出现一个很诡异的现象:我将type 中的长度变成300,结果出现novalid datatype 无效数据类型 这个错误,将tomcat重启下就ok了,请问这是为什么?在改变type之后,我已经把那些无效的Object编译了,大家可以看下:http://topic.csdn.net/u/20110330/15/ffc9e6f6-3406-49ff-ba10-f005c120b228.html?seed=274246412&r=72477450#r_72477450多谢!!!
public class GetConnection {
public static Connection getConn() {
String URL = "jdbc:oracle:thin:@localhost:1521:XXX";
String user = "XXX";//
String password = "XXX";//
Connection connection = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("instatiate success");
connection = DriverManager.getConnection(URL, user, password);
System.out.println("connect success:"+connection);
System.out.println("metaConnection:"+connection.getMetaData().getConnection());
} catch (Exception err) {
err.printStackTrace();
return null;
}
return connection;
}//测试代码
private void init6() throws SQLException{
String[] strArr1 = new String[]{"1","2","3"};
String[] strArr2 = new String[]{"4","5","6"};
String[] strArr3 = new String[]{"7","8","9"};
String UTIL_DATAARRAY = "UTIL_DATAARRAY";
CharacterSet charset = CharacterSet.make(CharacterSet.UTF8_CHARSET);
Connection conn = GetConnection.getConn();
ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor(UTIL_DATAARRAY,conn);
String sql = "{?= call HiwayTest.test5(?,?,?)}";
CallableStatement stmt = conn.prepareCall(sql);
stmt.registerOutParameter(1, OracleTypes.ARRAY,UTIL_DATAARRAY);
oracle.sql.ARRAY oracleArr1 = new oracle.sql.ARRAY(arrDesc,conn,strArr1);
oracle.sql.ARRAY oracleArr2 = new oracle.sql.ARRAY(arrDesc,conn,strArr2);
oracle.sql.ARRAY oracleArr3 = new oracle.sql.ARRAY(arrDesc,conn,strArr3);
stmt.setArray(2, oracleArr1);
stmt.setArray(3, oracleArr2);
stmt.setArray(4, oracleArr3);
stmt.execute();
Array result = stmt.getArray(1);
String[] resultArr = (String[]) result.getArray();
for(int i=0;i<resultArr.length;i++){
resultArr[i] = String.valueOf(new oracle.sql.CHAR(resultArr[i],charset));
}
System.out.println("java invoke oracle return:");
for(int i=0;i<resultArr.length;i++){
System.out.print(resultArr[i]+"\t");
}
}
create or replace type util_dataarray is varray(200000) of varchar2(300); function test5(vArr1 in util_dataarray,vArr2 in util_dataarray,vArr3 in util_dataarray) return util_dataarray
is
result util_dataarray;
begin
result := util_dataarray();
for i in 1..vArr1.Count
loop
result.extend;
result(result.count) := to_number(vArr1(i))+to_number(vArr2(i))+to_number(vArr3(i));
end loop;
return result;
end;
在eclipse中可以直接得到结果,即使不编译关联的function都可以。而部署到tomcat中的话则要去重启tomcat才可以,不重启报novalid datatype错误。请高手帮我下!!多谢
instatiate success
connect success:oracle.jdbc.driver.T4CConnection@158b649
metaConnection:oracle.jdbc.driver.T4CConnection@158b649
java invoke oracle return:
12 15 18很好奇:为什么要重启tomcat
多谢提醒,不过应该不是conn没有close引起的错误,因为部署到tomcat中后通过JNDI调用数据库,每次执行后不管是否成功都会释放conn的