我将oracle
和ojdbc
版本从11.1.0.2
升级到12.1.0.2
。我想将预言机对象类型的array
作为预言机procedure
的参数。此过程在预言机端需要 2 个varchar2
参数。例如,我将 3 个对象作为键值对数组发送,Oracle 接受这些对象。我的问题是调用我的过程时(varchar2
类型的(参数为空。
这是我尝试过的代码。
OracleConnection oraconn = conn.unwrap(OracleConnection.class);
Struct[] paramStructArray = new Struct[3];
paramStructArray[0] = oraconn.createStruct("KEY_VALUE_ENTRY",new Object[]{"key1","value1"});
paramStructArray[1] = oraconn.createStruct("KEY_VALUE_ENTRY",new Object[]{"key2","value2"});
paramStructArray[2] = oraconn.createStruct("KEY_VALUE_ENTRY",new Object[]{"key3","value3"});
Array array = oraconn.createOracleArray("KEY_VALUE_MAP", paramStructArray);
CallableStatement cstmt = getStatement(Statement.REGISTER_REQUEST);
cstmt.setString(1, requestId);
cstmt.setArray(2, array);
cstmt.execute();
这是我的java代码,这也是我的oracle objects
CREATE OR REPLACE TYPE "KEY_VALUE_ENTRY" AS
OBJECT (
key VARCHAR2(32),
val VARCHAR2(2048)
);
CREATE OR REPLACE TYPE "KEY_VALUE_MAP" AS
TABLE OF key_value_entry;
和我的程序
PROCEDURE register_request_(p_request_id IN varchar2
,p_params IN key_value_map) AS
BEGIN
IF p_params IS NOT NULL THEN
INSERT INTO test_table
(request, NAME, VALUE)
SELECT test_seq.nextval
,t.key
,t.val
FROM TABLE(CAST(p_params AS key_value_map)) t;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
我在 oracle 端获取 3 个KEY_VALUE_ENTRY
类型对象,但此对象的 key
和val
字段均为空。我无法弄清楚我做错了什么。
存在Encoding
问题,java
给出并oracle
接受此值,在我的classpath
中添加orai18n.jar
后,一切正常
我尝试了这段代码,它按预期工作,无需将 orai18n.jar 添加到类路径。我保持数据库方法不变。
public void insertTable(List<KeyValueObj> list, Connection conn)
throws SQLException {
String str = "{call Test_schema.Test_pkg.Register_Request(?,?)}";
OracleCallableStatement statement = null;
OracleConnection oraConn = null;
String requestId = "111";
try{
oraConn = conn.unwrap(OracleConnection.class);
statement = (OracleCallableStatement) oraConn.prepareCall(str);
statement.setObject(2, getOracleRowObjectList(list, oraConn));
statement.setString(1,requestId);
statement.execute();
}finally {
try {
statement.close();
} catch (Exception e) {
}
}
}
private Array getOracleRowObjectList(List<KeyValueObj> list, OracleConnection conn) throws SQLException{
String type = "Test_schema.KEY_VALUE_MAP";
Struct[] structList = new Struct[list.size()];
for(int i=0;i<list.size();i++){
structList[i] = getOracleRowObject(conn, list.get(i));
}
return conn.createOracleArray(type, structList);
}
private Struct getOracleRowObject(OracleConnection conn, KeyValueObj obj) throws SQLException{
String typeRow = "Test_schema.KEY_VALUE_ENTRY";
return conn.createStruct(typeRow,
new Object[]{ obj.getKey(),
obj.getValue(),
});
}
键值Obj类
public class KeyValueObj {
private String key;
private String value;
public KeyValueObj(String key, String value) {
super();
this.key = key;
this.value = value;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
希望这能帮助面临同样问题的人。