I need better eyes on this, I've been beating mine over it for days now.
I have a lot of classes built to pass Oracle UDTs to a procedure in a package. They all work, including several that are nearly identical to the one giving me fits. But this one returns the "Index is out of bounds.." error in calling OracleUdt.SetValue().
The absolute minimum code is below, and it's a mouth-full. Apologies for the length.
--- Oracle Types ---
create or replace type DMA_NUM_Varray IS VARRAY(250) OF NUMBER;
--- In an Oracle Package ---
PROCEDURE Create_commercials_Owr(f_dma_num_tab IN DMA_NUM_Varray) IS ...
This procedure actually has 4 other parameters, including 2 UDTs, all defined before this one on the parameter list. One of these is another VArray(50), and no error is returned on it, but only on the DMANumberArray.
--- C# .NET ---
public class DMANumberArray : INullable, IOracleCustomType {
[OracleArrayMapping()]
public OracleDecimal[] Array;
private bool isNull;
private OracleUdtStatus[] statusArray;
public OracleUdtStatus[] StatusArray {
get {
return this.statusArray;
}
set {
this.statusArray = value;
}
}
public virtual bool IsNull {
get {
return isNull;
}
}
public static DMANumberArray NULL {
get {
DMANumberArray did = new DMANumberArray();
did.isNull = true;
return did;
}
}
public virtual void FromCustomObject(OracleConnection oracleConn, IntPtr udt) {
OracleUdt.SetValue(oracleConn, udt, 0, Array, statusArray);
}
public virtual void ToCustomObject(OracleConnection oracleConn, IntPtr udt) {
object objectStatusArray = null;
Array = (OracleDecimal[])OracleUdt.GetValue(oracleConn, udt, 0, out objectStatusArray);
statusArray = (OracleUdtStatus[])objectStatusArray;
}
}
[OracleCustomTypeMapping("APCTS.DMA_NUM_VARRAY")]
public class DMANumberArrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory {
public IOracleCustomType CreateObject() {
return new DMANumberArray();
}
public Array CreateArray(int elementCount) {
return new OracleDecimal[elementCount];
}
public Array CreateStatusArray(int elementCount) {
return new OracleUdtStatus[elementCount];
}
}
DataTable dmaTable = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(query, sql)) {
da.Fill(dmaTable);
}
DMANumberArray dma = new DMANumberArray();
idCount = dmaTable.Rows.Count;
if (idCount > 250) idCount = 250;
dma.Array = new OracleDecimal[idCount];
for (int i = 0; i < idCount; i++) {
dma.Array[i] = OracleDecimal.Parse(dmaTable.Rows[i]["DMA_Number"].ToString());
}
dma.StatusArray = new OracleUdtStatus[] { OracleUdtStatus.NotNull, OracleUdtStatus.Null, OracleUdtStatus.NotNull, OracleUdtStatus.NotNull };
string query = "APCTS.OWR_APIS.Create_commercials_Owr";
com.Connection = oracle;
using (OracleCommand cmd = new OracleCommand(query, oracle)) {
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter paramDMAArrayObject = new OracleParameter();
paramDMAArrayObject.OracleDbType = OracleDbType.Array;
paramDMAArrayObject.Direction = ParameterDirection.Input;
paramDMAArrayObject.UdtTypeName = "APCTS.DMA_NUM_VARRAY";
paramDMAArrayObject.Value = dma;
cmd.Parameters.Add(paramDMAArrayObject);
cmd.ExecuteNonQuery();
}
I cannot for the life of me see where anything is indexing something larger than the array as it was sized, and limited to 250 elements. The one odd thing I do see is in class DMANumberArrayFactory, specifically in CreateArray. When I break here, the value of elementCount is always zero, even when the UDT object's Array was created with values greater than zero.
What have I missed?