oracle - Passing an array of Object from Java to PL/SQL function

Question

I am trying to create a Java array (java.sql.Array) object using an array of objects. I need to pass this Java Array to PLSQL stored procedure.

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();

however when I try to create this Array using the method (connection.createArrayOf), I am getting the following exception:

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

this is my JournalBean is:

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

this is my new DDL (creating the type in database using CREATE TYPE):

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

then creating the table type:

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

the procedure has became:

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;

I'm using Oracle 11g, java 6 and I had tried ojdbc14 and ojdbc6.

Answer 1

According to documentation of createArrayOf()

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

If the resulting JDBC type is not the appropriate type for the given typeName then it is implementation defined whether an SQLException is thrown or the driver supports the resulting conversion.

Your typeName CHMCR.PACK_CHM_CR.FICHIER_CR_TYPE is custom. Try to use defined type and check if exception will disapear.

Please, find additional details here.