将对象数组从Java传递到PL/SQL函数

Question

conn = ConnectionManager.getConnection();
JournalBean[] journal = listJournal.toArray(new JournalBean[listJournal.size()]); 
final Array sqlArray =   conn.createArrayOf("CHMCR.PACK_CHM_CR.FICHIER_CR_TYPE", journal);
cstmt = conn.prepareCall("{call CHMCR.PACK_CHM_CR.CHARGEMENT_CR(?,?,?,?,?)}");
cstmt.setArray(1,sqlArray);
cstmt.setString(2, fileName);
cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.VARCHAR);

cstmt.executeUpdate();
conn.commit();

java.sql.SQLException: Fonction non prise en charge
    at oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:9283)

public class JournalBean implements  Serializable, SQLData {

    private static final long serialVersionUID = 2199201954799483472L;

    private int idCr;
    private int idChargement;
    private String numAutorisation;
    private String version;

    @Override
    public String getSQLTypeName() throws SQLException {
        return "FICHIER_CR_TYPE";
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        idCr = stream.readInt();
        ...

    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {

        stream.writeInt(idCr);
        ...
    }
}

create or replace TYPE            CR_TYPE AS OBJECT
( ID_CR NUMBER  ,               
ID_CHARGEMENT   NUMBER ,
NUM_AUTORISATION    VARCHAR2(24 BYTE),
VERSION NUMBER 
) ;

CREATE OR REPLACE TYPE FICHIER_CR_TYPE 
AS VARRAY(1000) OF CR_TYPE;

create or replace PACKAGE             PACK_CHM_CR
IS
   PROCEDURE CHARGEMENT_CR(
                                                              P_FICHIER_CR                       IN OUT FICHIER_CR_TYPE,
                                                              p_nom_fichier                        IN        VARCHAR2,                                         
                                                              p_id_chargement                       OUT NUMBER,
                                                              NMES                                        OUT NUMBER,
                                                              LMES                                        OUT VARCHAR2);
END PACK_CHM_CR;

Answer 1

根据createArrayOf（）的文档

Array createArrayOf(String typeName, Object[] elements) throws SQLException

如果生成的JDBC类型不是给定typeName的适当类型，那么无论抛出SQLException还是驱动程序支持生成的转换，都将由实现定义。

您的类型名chmcr.pack_chm_cr.fichier_cr_type是自定义的。尝试使用定义的类型，并检查异常是否会令人失望。