执行存储过程的java

Question

我正在尝试执行一个oracle存储过程，该存储过程具有记录表的in out参数：

TYPE RECORD_TYP IS RECORD (
    CAT_CD                 VARCHAR2(4),
    MOD_ID       NUMBER(6)
);

我发现这个例子讲的是List<String>和List<Integer>： http://viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/

但是List<MyRecordDTO>呢

编辑：我在这里找到了一个答案，海报使用了甲骨文。sql。结构类型。 http://betteratoracle.com/posts/32-passing-arrays-of-record-types-between-oracle-and-java

使用这个例子，我发现了异常java.sql.SQLException: Internal Error: Inconsistent catalog view。通过谷歌搜索这个异常，我打电话给DBA，让我访问“RECORD_TYP”

Answer 1

我知道这是一个很老的问题。但我希望这能有所帮助。 这里我传递了一个自定义类型数组，作为回报，我希望得到一个自定义类型数组

        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]);
                }
            }
        }