oracle 执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12393268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing a Stored procedure
提问by bouhmid_tun
I'm trying to execute an oracle stored procedure that has an in-out parameter of table of record:
我正在尝试执行一个具有记录表输入输出参数的 oracle 存储过程：
TYPE RECORD_TYP IS RECORD (
CAT_CD VARCHAR2(4),
MOD_ID NUMBER(6)
);
I found this example that talks about List<String>and List<Integer>:
http://viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/.
我发现这个例子谈论List<String>和List<Integer>：http:
//viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/。
But what about List<MyRecordDTO>?
但是呢List<MyRecordDTO>？
EDIT: I found an answer here where the poster used an oracle.sql.STRUCT type. http://betteratoracle.com/posts/32-passing-arrays-of-record-types-between-oracle-and-java
编辑：我在这里找到了一个答案，其中海报使用了 oracle.sql.STRUCT 类型。 http://betteratoracle.com/posts/32-passing-arrays-of-record-types-between-oracle-and-java
Using this example, I found the exception java.sql.SQLException: Internal Error: Inconsistent catalog view. Googling this exception, I called the DBA to grant me access to "RECORD_TYP"
使用这个例子，我发现了异常java.sql.SQLException: Internal Error: Inconsistent catalog view。谷歌搜索这个异常，我打电话给 DBA 授予我访问“ RECORD_TYP”的权限
回答by user3770241
I know this is a very old question. But I hope this helps. Here I am passing a Custom Type Array and in return expecting a Custom type Array.
我知道这是一个非常古老的问题。但我希望这会有所帮助。在这里，我传递了一个自定义类型数组，作为回报，我期待一个自定义类型数组。
myJavaRequest req = new myJavaRequest();
req.setEmpId("940006614");
myJavaReqArray[0] = req;
List<myJavaResp> myJavaRespLst = new ArrayList<myJavaResp>();
try {
//fetch connection (this should be a OracleConnection class).
OracleConnection oraConn = (OracleConnection) getConnectionFromDB();
//Set the mappings -- what is the SQL Object type to Java class mappings when it comes to response.
Map map = oraConn.getTypeMap();
map.put("MYSCHEMA.SQLRESPDTO", Class.forName("com.myhome.myJavaResp"));
//Create the Array descriptor for the input array
ArrayDescriptor inputArrayDescr = ArrayDescriptor.createDescriptor("MYSCHEMA.MYREQDTOLIST", oraConn);
ARRAY inputArray = new ARRAY(inputArrayDescr, oraConn, spgPrefReqArray); //This is an Oracle ARRAY
//Prepare the Stored procedure call
OracleCallableStatement stmt = (OracleCallableStatement)oraConn.prepareCall("{ ? = call MYSCHEMA.PKG.SOME_SP(?) }");
stmt.registerOutParameter(1, OracleTypes.ARRAY, "MYSCHEMA.SQLRESPDTOLIST");
stmt.setArray(2, inputArray);
//Lets execute
stmt.execute();
//Fetch the Array of Objects that will have the set of expecting response java objects.
ARRAY outArray = ((OracleCallableStatement)stmt).getARRAY(1);
Object[] objects = (Object[])outArray.getArray(map);
if(null != objects && objects.length > 0){
for(int iIndex=0; iIndex<objects.length; iIndex++){
myJavaRespLst.add((myJavaResp)objects[iIndex]);
}
}
}
