Mule-4数据库插入时出现Invalid column type错误求助

Mule 4中使用数据库连接器向Oracle数据库插入数据，多次测试均失败，报错Invalid column type。

插入SQL语句 #

INSERT INTO TABLE
(
    GUID, 
    TIME_GENERATED_ON, 
    ENVELOPE_ID,
    SUBJECT, 
    USER_NAME,
    EMAIL, STATUS, 
    CREATED_ON, VOID_ON, DECLINED_ON, SENT_ON, DELIVERED_ON, 
    AC_STATUS, AC_STATUS_DATE, AC_HOLDER,
    AC_HOLDER_EMAIL, AC_HOLDER_LOCATION, SIGNING_LOCATION, SENDER_IP_ADDRESS,
    ENVELOPE_ID_STAMPING, AUTHORITATIVE_COPY, RAW_XML_DATA,WORK_OUI,UPI
)
VALUES
(
    :guid, 
    TO_TIMESTAMP(:timeGenerated, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    :EnvelopeID, :Subject, :UserName,
    :Email, 
    :Status, 
    TO_TIMESTAMP (:Created, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    TO_TIMESTAMP (:Void, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    TO_TIMESTAMP (:Declined, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    TO_TIMESTAMP (:Sent, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    TO_TIMESTAMP (:Delivered, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    :ACStatus,
    TO_TIMESTAMP (:ACStatusDate, 'YYYY-MM-DD"T"HH24:MI:SS.ff'),
    :ACHolder,
    :ACHolderEmail, :ACHolderLocation, :SigningLocation, :SenderIPAddress,
    :EnvelopeIdStamping, :AuthoritativeCopy, :originalPayload,  :WorkOui, :upi
)

动态输入参数 #

{
    "guid": payload.Guid,
    "timeGenerated": payload.TimeGenerated,
    "EnvelopeID": payload.EnvelopeID,
    "Subject": payload.Subject,
    "UserName": payload.UserName,
    "Email": payload.Eamil,
    "Status": payload.Status,
    "Created": payload.Created,
    "Void": payload.Void,
    "Declined": payload.Declined,
    "Sent": payload.Sent,
    "Delivered": payload.Delivered,
    "ACStatus": payload.ACStatus,
    "ACStatusDate": payload.ACStatusDate,
    "ACHolder": payload.ACHolder,
    "ACHolderEmail": payload.ACHolderEmail,
    "ACHolderLocation": payload.ACHolderLocation,
    "SigningLocation": payload.SigningLocation,
    "SenderIPAddress": payload.SenderIPAddress,
    "EnvelopeIdStamping": payload.EnvelopeIdStamping,
    "AuthoritativeCopy": payload.AuthoritativeCopy,
    "originalPayload": vars.originalPayload,
    "WorkOui": payload..WorkOui,
    "upi": payload.upi
}

错误堆栈信息 #

org.mule.runtime.api.exception.MuleRuntimeException: java.sql.SQLException: Invalid column type
Caused by: org.mule.db.commons.shaded.api.exception.connection.QueryExecutionException: Invalid column type
Caused by: java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8921) ~[?:?]

排查与解决方法 #

• 修正参数拼写错误：输入参数里存在两处明显错误：
  • Email: payload.Eamil拼写错误，应为payload.Email；
  • WorkOui: payload..WorkOui多了一个点，应为payload.WorkOui。这类错误会导致参数值为null或无法正确获取，触发类型不匹配。
• 检查RAW_XML_DATA列类型匹配：originalPayload是XML数据，若数据库RAW_XML_DATA列类型为CLOB/BLOB：
  • 确保传递的是XML字符串，而非Mule的XML对象；
  • 可在SQL中用TO_CLOB(:originalPayload)显式转换，或在数据库连接器配置中启用Use Streaming选项。
• 处理时间参数的空值与格式：
  • 若时间字段（如Void、Declined）可能为空，需修改SQL避免对空值执行转换，例如：CASE WHEN :Void IS NOT NULL THEN TO_TIMESTAMP(:Void, 'YYYY-MM-DD"T"HH24:MI:SS.ff') ELSE NULL END；
  • 确认输入的时间字符串完全匹配指定格式YYYY-MM-DD"T"HH24:MI:SS.ff，格式不匹配会导致转换失败。
• 验证Oracle驱动兼容性：确保使用的Oracle JDBC驱动版本与数据库版本一致，旧驱动可能对某些数据类型支持存在缺陷。

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