Click here to Skip to main content
15,900,906 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 ---
JavaScript
create or replace type DMA_NUM_Varray IS VARRAY(250) OF NUMBER;


--- In an Oracle Package ---

JavaScript
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 ---

C#
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); // *** Error happens here ***
	}

	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; // The error occurs even if I change this value to 5, or 1 *** Correction: it works for values up to 4, but not >= 5
dma.Array = new OracleDecimal[idCount]; //limit 250
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?
Posted
Updated 5-Jan-16 11:03am
v2
Comments
Sergey Alexandrovich Kryukov 5-Jan-16 17:20pm    
I did not find SetValue with this profile, 5 arguments. Will you provide a link to the function you are using?
—SA
TNCaver 6-Jan-16 16:59pm    
Now I understand. Here's the link: http://docs.oracle.com/html/E15167_01/OracleUdtClass.htm#BABJGHDD
Jörgen Andersson 7-Jan-16 7:43am    
Can we get the full exception?
TNCaver 15-Jan-16 15:51pm    
That is the full exception.

1 solution

Finally figured this out. I was focusing on the wrong array. Turns out it was the status array that needed to be re-sized to match the size of the VArray. Dumb, incomplete, vague (non)documentation from Oracle, doesn't really explain what this is for or how to use it.

Not having such explanation, I just used the same thing as in Oraale's sample code, which was passing four elements.

dma.StatusArray = new OracleUdtStatus[] { OracleUdtStatus.NotNull, OracleUdtStatus.Null, OracleUdtStatus.NotNull, OracleUdtStatus.NotNull };


But you use it to flag each and every element in your VArray as either null or not-null. If your VArray has 250 elements, you define the status array with 250 elements, and then you populate each element with OracleUdtStatus.Null or OracleUdtStatus.NotNull. That seems crazy-useless to me. Seems like the converter should be able to figure out itself whether an element you're sending is null or not. But then, the documentation and samples do not tell you why it needs this.
 
Share this answer
 
Comments
Patrice T 15-Jan-16 23:07pm    
If you solved the question, you should say it is.
There is a button in the page to say the question is solved.
TNCaver 17-Jan-16 17:03pm    
Thought I'd done that. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900