Click here to Skip to main content
13,198,465 members (58,642 online)
Rate this:
 
Please Sign up or sign in to vote.
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 ---
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); // *** 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 5-Jan-16 10:19am
TNCaver2.9K
Updated 5-Jan-16 11:03am
v2
Comments
   
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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
ppolymorphe 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.171020.1 | Last Updated 15 Jan 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100