CallableStatement.setObject() SQLException - Fail to construct descriptor: Invalid arguments

Oracle supports structured user-defined types or UDTs.  JDBC 2.0 provides an ability to custom map a Oracle UDT using a Java class. The class will have a field for each attribute in the UDT. The class has to implement java.sql.SQLData  & Serializable interfaces. There are other techniques available such as the ORAData and ORADataFactory interfaces provided by Oracle but this post deals with SQLData.

Let's say, you have a UDT  'OBJ_USER' that has the following attributes - userid, username and corresponding Java mapper class called UserObj.java

CREATE OR REPLACE TYPE OBJ_USER
  (
    USERID NUMBER,
    USERNAME VARCHAR(40)    
  );

public class UserObj implements SQLData,Serializable {

     private long userID;
     private String userName;

    private String sql_type;

}


Now we can persist & read data using UDT and its corresponding Java mapper object. The 'SQLData' interface provides two methods for this

@Override

public void writeSQL(SQLOutput stream) throws SQLException 

{

    stream.writeLong(userId);            

    stream.writeString(userName);

             

 }

@Override

public void readSQL(SQLInput stream,String type) throws SQLException 

{

    sql_type= type;

    userId=stream.readLong();            

    userName=stream.readString();

             

 }

The following code calls a StoredProcedure using a JDBC CallableStatement instance that will persist the UDT data to a table ( We don't need to worry about that since  focus here is the JDBC approach to map data)

conn =  createConnection();

String schname = <SCHEMA>;

             

System.out.println("conn::" + conn);

Map map = conn.getTypeMap();

System.out.println("SchemaName:" + conn.getMetaData().getUserName());

map.put(schname + ".OBJ USER", Class.forName("UserObj"));

                                 

cstmtForCMValidation = conn.prepareCall("{call <package_name>.<procedure_name>(?,?,?)}");

cstmtForCMValidation.setObject(1, userObj);                                                                                                          

However, 'setObject(1, userObj)' call can throw the following exception

java.sql.SQLException: Fail to construct descriptor: Invalid arguments

       at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)

       at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)

       at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:161)

       at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:137)

       at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:477)

       at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7954)

       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7547)

       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8037)

       at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4132)

       at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:238)

To fix this exception, the 'UserObj.java'  field - sqltype  will have to be set with UDT name either in the constructor or its setter e.g

 public UserObj (){

   sql_type=SCHEMA+".OBJ_USER";

}

Note: this field has to be set with the fully qualified name of the UDT i.e. with DB schema name

This is because while writing data from the mapper object to the UDT, CallableStatement instance will not look for mapping relation in the 'Type' Map of JDBC Connection object. It will check the value of sqltype field of mapper object.