Oracle UDF 问题 - 对象传递到存储过程但插入 NVARCHAR2，没有插入 DECIMAL

Question

我们有一个表：

CREATE TABLE ESIPARIS.T_ORDER_LINE

(
  NO           NUMBER(18),
  ORDER_NO     NUMBER(18),
  ITEM_NO      NVARCHAR2(15),
  AMOUNT       NUMBER(18,3),
  INSERT_DATE  DATE,
  INSERT_USER  NVARCHAR2(20),
  UPDATE_DATE  DATE,
  UPDATE_USER  NVARCHAR2(20),
  FLEXFIELD_1  NVARCHAR2(100),
  FLEXFIELD_2  NVARCHAR2(100),
  FLEXFIELD_3  NVARCHAR2(100),
  FLEXFIELD_4  NVARCHAR2(100)
)
TABLESPACE DEVEL
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

对象：

DROP TYPE ESIPARIS.ORDER_LINE_ROW;

CREATE OR REPLACE TYPE ESIPARIS.ORDER_LINE_ROW AS OBJECT
(
NO           NUMBER(18),
ORDER_NO     NUMBER(18),
ITEM_NO      NVARCHAR2(15),
AMOUNT       NUMBER(18,3),
INSERT_DATE  DATE,
INSERT_USER  NVARCHAR2(20),
UPDATE_DATE  DATE,
UPDATE_USER  NVARCHAR2(20),
FLEXFIELD_1  NVARCHAR2(100),
FLEXFIELD_2  NVARCHAR2(100),
FLEXFIELD_3  NVARCHAR2(100),
FLEXFIELD_4  NVARCHAR2(100)
)
/

CREATE OR REPLACE TYPE ESIPARIS.ORDER_LINE_TABLE as table of ESIPARIS.ORDER_LINE_ROW;
/

以及一个存储过程，用于处理 .NET 应用程序发送的对象以插入行：

CREATE OR REPLACE PROCEDURE ESIPARIS.pr_getorder_line (
   deneme   IN   order_line_table
)
IS
   err_code   NVARCHAR2 (500) := '';
   err_msg    NVARCHAR2 (500) := '';
BEGIN
   FOR i IN 1 .. deneme.COUNT
   LOOP
      INSERT INTO t_order_line
                  (NO, order_no, item_no,
                   amount, insert_date,
                   insert_user, update_date,
                   update_user, flexfield_1,
                   flexfield_2, flexfield_3,
                   flexfield_4
                  )
           VALUES (deneme (i).NO, deneme (i).order_no, nvl(deneme (i).item_no,'null geldi'),
                   nvl(deneme (i).amount,5363377869), deneme (i).insert_date,
                   deneme (i).insert_user, deneme (i).update_date,
                   deneme (i).update_user, deneme (i).flexfield_1,
                   deneme (i).flexfield_2, deneme (i).flexfield_3,
                   deneme (i).flexfield_4
                  );
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      err_code := SQLCODE;
      err_msg := SUBSTR (SQLERRM, 1, 200);

      INSERT INTO esiparis.t_error_log
                  (event_date, event_object, MESSAGE
                  )
           VALUES (SYSDATE, err_code, err_msg
                  );
END;
/

我们创建了 UDT .NET 4.0 中使用 Visual Studio 2010 和 ODP.NET 插件。我们正处于插入由 .NET 应用程序发送的 NVARCHAR2 数据类型的值的时刻。无法插入 NUMBER 或 DATE 数据类型的数据。有什么想法吗？

Answer 1

您的问题可能是由于调用 C# 代码中的本机 .NET 类型（int、double、DateTime 等）到用于在数据库中存储值的 Oracle 类型（NUMBER、DATE）的类型转换所致。确保 UDT 类使用 Oracle 类型，而不是 .NET 类型。

另外，如果您所做的只是迭代集合以插入记录，那么您应该考虑使用 Oracle 的批量绑定功能，因为它会更加高效。请参阅 http://dotnetslackers.com/articles/ado_net/BulkOperationsUsingOracleDataProviderForNETODPNET.aspx 了解更多信息例子。

编辑：
根据您的评论：“我们正在寻找直接的 SP 示例。这对于使用绑定变量很有好处。”

无论哪种方式，您都需要将类型从 .NET 正确转换为 Oracle。听起来您正在执行本文档中的操作：
http://download.oracle.com/docs/html/E15167_01/featUDTs .htm#CJAGFHBA

编辑2：
根据您自己发布的答案中的代码，看起来有一个（稍微）更好的方法来解决这个问题。

您注释掉了如下几行：

//[07.12.2010]ISMAILH : ISNULLS ARE COMMENTED OUT FOR PROPER WORKING!
// TODO : Add code to initialise the object
//this.m_AMOUNTIsNull = true;

m_AMOUNTIsNull（或使用该字段的属性AMOUNTIsNull）的目的是允许代码区分实际的m_AMOUNT< /code> 设置的值和由于使用默认 .NET 值（0 表示小数）而设置的值。

这就是为什么在使用 m_AMOUNT 值填充 UDT 之前检查该值的原因：

if ((AMOUNTIsNull == false)) {
    Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "AMOUNT", this.AMOUNT);

从数据库获取 UDT 时，会根据数据库字段是否为空来设置 AMOUNTIsNull ：

this.AMOUNTIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "AMOUNT");
if ((AMOUNTIsNull == false)) {
    this.AMOUNT = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "AMOUNT")));
}

但是当您使用 .NET 对象并设置 AMOUNT 属性时，AMOUNTIsNull 不会改变：

[OracleObjectMappingAttribute("AMOUNT")]
public decimal AMOUNT {
    get {
        return this.m_AMOUNT;
    }
    set {
        this.m_AMOUNT = value;
    }
}

如果您更改它，则它已正确设置（并撤消你已经做了注释掉的修复），你会更好，因为你既可以存储一个值，也可以代表一个空值：

[OracleObjectMappingAttribute("AMOUNT")]
public decimal AMOUNT {
    get {
        return this.m_AMOUNT;
    }
    set {
        this.AMOUNTIsNull = false;
        this.m_AMOUNT = value;
    }
}

Answer 2

检查 Oracle ODP.NET Visual Studio 插件自动生成的类后，
我们的.NET 开发人员发现，在注释掉自动生成的代码中的一些行（从

“//[07.12.2010]ISMAILH : ISNULLS ARE COMMENTED OUT FOR PROPER WORKING!”之后开始的 4 行）后，它可以正常工作。

之后一切正常，但我想知道其更深层次的原因。为什么插件首先生成这些行？ :

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.1
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ESiparis {
    using System;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    using System.Xml.Serialization;
    using System.Xml.Schema;

    public class ORDER_LINE_ROW : INullable, IOracleCustomType, IXmlSerializable,IDisposable {

        private bool m_IsNull;
        private decimal m_AMOUNT;
        private bool m_AMOUNTIsNull;
        private System.DateTime m_UPDATE_DATE;
        private bool m_UPDATE_DATEIsNull;
        private string m_FLEXFIELD_1;
        private System.DateTime m_INSERT_DATE;
        private bool m_INSERT_DATEIsNull;
        private string m_FLEXFIELD_3;
        private string m_UPDATE_USER;
        private string m_FLEXFIELD_2;
        private decimal m_ORDER_NO;
        private bool m_ORDER_NOIsNull;
        private string m_FLEXFIELD_4;
        private string m_INSERT_USER;
        private decimal m_NO;
        private bool m_NOIsNull;
        private string m_ITEM_NO;

        public ORDER_LINE_ROW() {
            //[07.12.2010]ISMAILH : ISNULLS ARE COMMENTED OUT FOR PROPER WORKING!
            // TODO : Add code to initialise the object
            //this.m_AMOUNTIsNull = true;
            //this.m_UPDATE_DATEIsNull = true;
            //this.m_INSERT_DATEIsNull = true;
            //this.m_ORDER_NOIsNull = true;
            Dispose(false);
            this.m_NOIsNull = true;
        }

        public ORDER_LINE_ROW(string str) {
            // TODO : Add code to initialise the object based on the given string
            Dispose(false);
        }

        public virtual bool IsNull {
            get {
                return this.m_IsNull;
            }
        }

        public static ORDER_LINE_ROW Null {
            get {
                ORDER_LINE_ROW obj = new ORDER_LINE_ROW();
                obj.m_IsNull = true;
                return obj;
            }
        }

        [OracleObjectMappingAttribute("AMOUNT")]
        public decimal AMOUNT {
            get {
                return this.m_AMOUNT;
            }
            set {
                this.m_AMOUNT = value;
            }
        }

        public bool AMOUNTIsNull {
            get {
                return this.m_AMOUNTIsNull;
            }
            set {
                this.m_AMOUNTIsNull = value;
            }
        }

        [OracleObjectMappingAttribute("UPDATE_DATE")]
        public System.DateTime UPDATE_DATE {
            get {
                return this.m_UPDATE_DATE;
            }
            set {
                this.m_UPDATE_DATE = value;
            }
        }

        public bool UPDATE_DATEIsNull {
            get {
                return this.m_UPDATE_DATEIsNull;
            }
            set {
                this.m_UPDATE_DATEIsNull = value;
            }
        }

        [OracleObjectMappingAttribute("FLEXFIELD_1")]
        public string FLEXFIELD_1 {
            get {
                return this.m_FLEXFIELD_1;
            }
            set {
                this.m_FLEXFIELD_1 = value;
            }
        }

        [OracleObjectMappingAttribute("INSERT_DATE")]
        public System.DateTime INSERT_DATE {
            get {
                return this.m_INSERT_DATE;
            }
            set {
                this.m_INSERT_DATE = value;
            }
        }

        public bool INSERT_DATEIsNull {
            get {
                return this.m_INSERT_DATEIsNull;
            }
            set {
                this.m_INSERT_DATEIsNull = value;
            }
        }

        [OracleObjectMappingAttribute("FLEXFIELD_3")]
        public string FLEXFIELD_3 {
            get {
                return this.m_FLEXFIELD_3;
            }
            set {
                this.m_FLEXFIELD_3 = value;
            }
        }

        [OracleObjectMappingAttribute("UPDATE_USER")]
        public string UPDATE_USER {
            get {
                return this.m_UPDATE_USER;
            }
            set {
                this.m_UPDATE_USER = value;
            }
        }

        [OracleObjectMappingAttribute("FLEXFIELD_2")]
        public string FLEXFIELD_2 {
            get {
                return this.m_FLEXFIELD_2;
            }
            set {
                this.m_FLEXFIELD_2 = value;
            }
        }

        [OracleObjectMappingAttribute("ORDER_NO")]
        public decimal ORDER_NO {
            get {
                return this.m_ORDER_NO;
            }
            set {
                this.m_ORDER_NO = value;
            }
        }

        public bool ORDER_NOIsNull {
            get {
                return this.m_ORDER_NOIsNull;
            }
            set {
                this.m_ORDER_NOIsNull = value;
            }
        }

        [OracleObjectMappingAttribute("FLEXFIELD_4")]
        public string FLEXFIELD_4 {
            get {
                return this.m_FLEXFIELD_4;
            }
            set {
                this.m_FLEXFIELD_4 = value;
            }
        }

        [OracleObjectMappingAttribute("INSERT_USER")]
        public string INSERT_USER {
            get {
                return this.m_INSERT_USER;
            }
            set {
                this.m_INSERT_USER = value;
            }
        }

        [OracleObjectMappingAttribute("NO")]
        public decimal NO {
            get {
                return this.m_NO;
            }
            set {
                this.m_NO = value;
            }
        }

        public bool NOIsNull {
            get {
                return this.m_NOIsNull;
            }
            set {
                this.m_NOIsNull = value;
            }
        }

        [OracleObjectMappingAttribute("ITEM_NO")]
        public string ITEM_NO {
            get {
                return this.m_ITEM_NO;
            }
            set {
                this.m_ITEM_NO = value;
            }
        }

        public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt) {
            if ((AMOUNTIsNull == false)) {
                Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "AMOUNT", this.AMOUNT);
            }
            if ((UPDATE_DATEIsNull == false)) {
                Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "UPDATE_DATE", this.UPDATE_DATE);
            }
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "FLEXFIELD_1", this.FLEXFIELD_1);
            if ((INSERT_DATEIsNull == false)) {
                Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "INSERT_DATE", this.INSERT_DATE);
            }
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "FLEXFIELD_3", this.FLEXFIELD_3);
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "UPDATE_USER", this.UPDATE_USER);
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "FLEXFIELD_2", this.FLEXFIELD_2);
            if ((ORDER_NOIsNull == false)) {
                Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ORDER_NO", this.ORDER_NO);
            }
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "FLEXFIELD_4", this.FLEXFIELD_4);
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "INSERT_USER", this.INSERT_USER);
            if ((NOIsNull == false)) {
                Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "NO", this.NO);
            }
            Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ITEM_NO", this.ITEM_NO);
        }

        public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt) {
            this.AMOUNTIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "AMOUNT");
            if ((AMOUNTIsNull == false)) {
                this.AMOUNT = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "AMOUNT")));
            }
            this.UPDATE_DATEIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "UPDATE_DATE");
            if ((UPDATE_DATEIsNull == false)) {
                this.UPDATE_DATE = ((System.DateTime)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "UPDATE_DATE")));
            }
            this.FLEXFIELD_1 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_1")));
            this.INSERT_DATEIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "INSERT_DATE");
            if ((INSERT_DATEIsNull == false)) {
                this.INSERT_DATE = ((System.DateTime)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "INSERT_DATE")));
            }
            this.FLEXFIELD_3 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_3")));
            this.UPDATE_USER = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "UPDATE_USER")));
            this.FLEXFIELD_2 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_2")));
            this.ORDER_NOIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "ORDER_NO");
            if ((ORDER_NOIsNull == false)) {
                this.ORDER_NO = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "ORDER_NO")));
            }
            this.FLEXFIELD_4 = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "FLEXFIELD_4")));
            this.INSERT_USER = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "INSERT_USER")));
            this.NOIsNull = Oracle.DataAccess.Types.OracleUdt.IsDBNull(con, pUdt, "NO");
            if ((NOIsNull == false)) {
                this.NO = ((decimal)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "NO")));
            }
            this.ITEM_NO = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "ITEM_NO")));
        }

        public virtual void ReadXml(System.Xml.XmlReader reader) {
            // TODO : Read Serialized Xml Data
        }

        public virtual void WriteXml(System.Xml.XmlWriter writer) {
            // TODO : Serialize object to xml data
        }

        public virtual XmlSchema GetSchema() {
            // TODO : Implement GetSchema
            return null;
        }

        public override string ToString() {
            // TODO : Return a string that represents the current object
            return "";
        }

        public static ORDER_LINE_ROW Parse(string str) {
            // TODO : Add code needed to parse the string and get the object represented by the string
            return new ORDER_LINE_ROW();
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool p)
        {
            if (p == true)
            {
                GC.SuppressFinalize(this);
            }
        }
    }

    // Factory to create an object for the above class
    [OracleCustomTypeMappingAttribute("ESIPARIS.ORDER_LINE_ROW")]
    public class ORDER_LINE_ROWFactory : IOracleCustomTypeFactory ,IDisposable {

        public virtual IOracleCustomType CreateObject() {
            ORDER_LINE_ROW obj = new ORDER_LINE_ROW();
            return obj;
        }

        public void Dispose()
        {

        }
    }