Java向OracleCallableStatement传递自定义数组类型输入参数报错的问题求助

Let's fix this issue step by step. The root problem here is two-fold: your custom PL/SQL types are likely defined inside a package (which JDBC can't access) and JDBC doesn't have a clear mapping between your Java objects and Oracle's custom types. Here's how to resolve this without using the deprecated StructDescriptor:

Step 1: Move Custom Types to SQL Level (Critical!) #

First, JDBC can only interact with SQL-level custom types, not types defined inside a PL/SQL package. So you need to redefine your IDType and XXX_TYPES as global SQL objects:

-- Create the OBJECT type (equivalent to your PL/SQL RECORD)
CREATE OR REPLACE TYPE IDType AS OBJECT (
  c_number c_table.c_number%TYPE -- Matches your original table column type
);
/

-- Create the TABLE type using the OBJECT above
CREATE OR REPLACE TYPE XXX_TYPES AS TABLE OF IDType;
/

Step 2: Option 1 - Use Standard JDBC with SQLData (Recommended) #

Define a Java class that implements SQLData to map the Oracle IDType object. This gives you type-safe mapping and works with standard JDBC:

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class IDType implements SQLData {
    private static final String SQL_TYPE_NAME = "IDTYPE"; // Oracle uses uppercase by default
    private String cNumber;

    // Required no-arg constructor for JDBC
    public IDType() {}

    public IDType(String cNumber) {
        this.cNumber = cNumber;
    }

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

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        this.cNumber = stream.readString(); // Adjust based on your actual column type (e.g., readLong() for NUMBER)
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeString(this.cNumber); // Match the readSQL() type
    }

    // Getters and Setters
    public String getcNumber() {
        return cNumber;
    }

    public void setcNumber(String cNumber) {
        this.cNumber = cNumber;
    }
}

Then adjust your Java code to use this class:

// Register the type mapping with your connection
Map<String, Class<?>> typeMap = con.getTypeMap();
typeMap.put("IDTYPE", IDType.class);
con.setTypeMap(typeMap);

// Create instances of your Java IDType class
IDType id1 = new IDType("1");
IDType id2 = new IDType("2");
IDType[] idArray = {id1, id2};

// Create the Oracle array using the global XXX_TYPES type
OracleArray oracleArray = ((OracleConnection) con).createOracleArray("XXX_TYPES", idArray);

// Set the parameter on your callable statement
cs.setArray(1, oracleArray);

Step 3: Option 2 - Use Oracle Struct API (Simpler, No Custom Class) #

If you don't want to create a SQLData class, you can directly use Struct objects—just ensure you reference the global SQL type name correctly:

// Create Struct instances for each IDType record (use uppercase type name)
Struct idStruct1 = con.createStruct("IDTYPE", new Object[]{"1"});
Struct idStruct2 = con.createStruct("IDTYPE", new Object[]{"2"});
Struct[] structArray = {idStruct1, idStruct2};

// Create the Oracle array for XXX_TYPES
OracleArray oracleArray = ((OracleConnection) con).createOracleArray("XXX_TYPES", structArray);

// Set the parameter
cs.setArray(1, oracleArray);

Why Your Original Code Failed #

• Package-level Types: If your IDType and XXX_TYPES were defined inside a PL/SQL package, JDBC cannot locate them—only global SQL-level types are accessible.
• Type Mapping Mismatch: JDBC didn't know how to map your Object[] to the Oracle IDType record, hence the "inconsistent java and sql object types" error. Registering the SQLData class or using the correct global type name fixes this.

内容的提问来源于stack exchange，提问作者lifeline2