Oracle UDF problem - object passes to stored procedure but does insert NVARCHAR2,no insert for DECIMAL

Question

We have a table as:

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;

And objects as:

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;
/

And a stored procedure to handle objects sent by a .NET application to insert rows:

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;
/

We have created UDT classes in .NET 4.0 using the Visual Studio 2010 and the ODP.NET plugin. We are at a point that values sent by a .NET application that are in NVARCHAR2 data type are inserted. Data in NUMBER or DATE data types cannot be inserted. Any ideas?

Answer 1

Your problem is probably due to type conversion from native .NET types (int, double, DateTime, etc.) in your calling C# code to the Oracle types (NUMBER, DATE) used to store the values in the database. Ensure the UDT classes are using Oracle types, not .NET types.

Also, if all you're doing is iterating over your collection to insert records, you should consider using Oracle's bulk binding capabilities as it'll be much more efficient. See http://dotnetslackers.com/articles/ado_net/BulkOperationsUsingOracleDataProviderForNETODPNET.aspx for a good example.

EDIT: Per your commment: "We were looking for a direct SP example. This was good for using bind variables."

Either way, you need to be properly converting the types from .NET to Oracle. It sounds like you're doing exactly what's in this document: http://download.oracle.com/docs/html/E15167_01/featUDTs.htm#CJAGFHBA

EDIT2: Based on the code in the answer you posted yourself, it looks like there's a (slightly) better way to fix that.

You commented out some lines like this:

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

The purpose of m_AMOUNTIsNull (or the property AMOUNTIsNull which uses that field) is to allow the code to differentiate an actual m_AMOUNT value that's set and one that's set because the default .NET value (0 for a decimal) is used.

That's why the value is checked before the UDT is populated with the m_AMOUNT value:

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

When the UDT is fetched from the database, AMOUNTIsNull is set based on whether the database field is null:

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

But when you're using your .NET object and setting the AMOUNT property, AMOUNTIsNull doesn't get altered:

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

If you change it so it is properly set (and undo the commented-out fix you've already made), you'll be better off since you're able to both store a value AND represent a null value as well:

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