The DFLT_MSG column is defined as VARCHAR2(4000) not as an ADT. setNull(9, Types.VARCHAR) should work. If the column were defined as an ADT then you should use the appropriate ADT type, eg setNull(9, Types.STRUCT) or setNull(9, Types.ARRAY).
Sorry for all the confusion, my first post was in error. Basically I want to set the DFLT_MSG_SEC_DQI column to null when needed in my Java app. The DFLT_MSG_SEC_DQI column is defined as KPS_ADM.SEC_DQI_T, which is an Oracle ADT.
First I tried to use setNull method on the OraclePreparedStatement with the java.sql.Types value of NULL:
This produces a SQLException: ORA-00932: inconsistent datatypes: expected KPS_ADM.SEC_DQI_T got CHAR
Next, I tried to use the Types.STRUCT value:
This produces a SQLException on the setNull method: Invalid column type: sqlType=2002
I then tried to set a variable of the ADT type to null and use the variable in the setORAData method:
One option I see you haven't tried is setObject.
This is referenced in here:
Another is using the setNull with three parameters.
I've seen it used with OPAQUE, and supplying the type name, in your case "KPS_ADM.SEC_DQI_T".
search for setORAData and there's a setNull on the line before. It's generated, so take it with a pinch of salt.
(OPAQUE is 2007).
I would go with setObject if it works. Pulling stuff from generated code is not always wise.
It's quite possible one of the other Types should be used instead.
You never posted any info about the DB, Java or JDBC jar versions you are using but since 10i Release 1 you can use this version of 'setNull' where you specify the SQL type. The variant of 'setNull' you are using send a Java NULL, not a SQL type NULL.
void setNull(java.lang.String parameterName,
Sets the designated parameter to SQL NULL. This version of the method setNull should be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA_OBJECT, and named array types.
Note: To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type. If a JDBC driver does not need the type code or type name information, it may ignore it. Although it is intended for user-defined and Ref parameters, this method may be used to set a null parameter of any JDBC type. If the parameter does not have a user-defined or REF type, the given typeName is ignored.
setNull in interface java.sql.CallableStatement
parameterName - the name of the parameter
sqlType - a value from java.sql.Types
typeName - the fully-qualified name of an SQL user-defined type; ignored if the parameter is not a user-defined type or SQL REF value
java.sql.SQLException - if a database access error occurs