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.

Answer 2

create or replace PACKAGE             PACK_CHM_CR
IS
   TYPE fichier_CR_TYPE IS TABLE OF CHMCR.JOURNAL_OPERATION_CHANGE%ROWTYPE index by binary_integer;

You can't for three reasons:

1. TYPE x IS TABLE OF y INDEX BY z is an associative array. This is solely a PL/SQL data type and JDBC does not support passing associative arrays, only collections or VARRAYs (both non-associative array) data types (unlike in C# which only support associative arrays and not collections/VARRAYs).
2. It is defined in a PACKAGE which is a PL/SQL scope. If you want to pass a type it needs to be defined in an SQL scope using a CREATE TYPE statement.
3. The JDBC driver doesn't know how to convert from your JournalBean class to a fichier_CR_TYPE database type. You either need to pass to a simple array (there are built-in types that can be used like SYS.ODCIVARCHAR2LIST) or you need to tell Java how to map between the class and the database type (SQLData is one method of doing this).

You need to fix all 3 of these issues.

---

Update:

The JournalBean class does not map to FICHIER_CR_TYPE as that is an array; they type should be of the object type CR_TYPE:

public class JournalBean implements  Serializable, SQLData {
  // ...
  public static final String SQL_TYPE = "CR_TYPE";

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

You also need to register the type with the connection:

conn = ConnectionManager.getConnection();

Map<String,Class<?>> typeMap = conn.getTypeMap();
typeMap.put( JournalBean.SQL_TYPE, JournalBean.class );