Oracle: PLSQLAssociativeArray

[Guest]

Hello,

I'm trying to use a PLSQLAssociateiveArray as output parameter using the
examples from the ODP.NET client distribution, but when i call
ExecuteNonQuery(), the webserver hangs. Does anyone have a clue?

Regards,
Joost Evertse

Here is the code:

-----
VB.NET code
-----
Try
con.Open()

cmd = New OracleCommand
cmd.CommandText = proc
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con

'create the IN parameters using the array given
For i = 0 To paramsin.GetUpperBound(0)
fl.write("parameter: " & paramsin(i, 0) & " waarde: " & paramsin(i, 1))
cmd.Parameters.Add(New OracleParameter(paramsin(i, 0),
OracleDbType.Varchar2, ParameterDirection.Input))
cmd.Parameters(paramsin(i, 0)).Value = paramsin(i, 1)
Next

'create the OUT parameter
Dim oracleparam As OracleParameter = cmd.Parameters.Add("oracleparam",
OracleDbType.Varchar2)

'let's make it a PSQLAssociative Array
oracleparam.CollectionType = OracleCollectionType.PLSQLAssociativeArray

'size the number of array elements, let's say 50
oracleparam.Size() = 50

'create an array of integers, only one 'column', set the size of the field
Dim n(0) As Int32
n(0) = 255
oracleparam.ArrayBindSize = n

'set succes?
Dim m(0) As OracleParameterStatus
m(0) = OracleParameterStatus.Success
oracleparam.ArrayBindStatus = m

'print the parameters that are bound
fl.write("number of parameters : " & cmd.Parameters.Count)

'execute the procedure
cmd.ExecuteNonQuery() 'code hangs here
.....exception handling etc..

---
PLSQL package/procedure---
TYPE gegevensLijst is table of VARCHAR(255) index by binary_integer;
---
procedure getStamGegevens(p_pers_nr IN varchar2, tab_id IN varchar2,
resultaten OUT mutatiejobfiller.gegevensLijst)

is

begin
return;
end getStamGegevens;

---




Expand AllCollapse All


Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter


--
'It is common sense to take a method and try it. If it fails, admit it
frankly and try another. But above all, try something. '
Franklin D. Roosevelt (1882 - 1945)

BLOG: http://www.joustie.nl