Null values in array returned from PL/SQL procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
marc.vanhoecke
Posts: 11
Joined: Mon 27 Aug 2007 08:24

Null values in array returned from PL/SQL procedure

Post by marc.vanhoecke » Wed 15 Apr 2009 14:23

Hi,

I'm having a problem with an integer array returned as OUT parameter from a PL/SQL procedure.

The array is define in Oracle as
TYPE t_integer
IS
TABLE OF INTEGER
INDEX BY PLS_INTEGER;

On the Oracle side the array filled with integers and NULL values.

On the client side (.NET CF) the parameter is declared as

cmd.Parameters.Add("sk", OracleDbType.Integer);
cmd.Parameters["sk"].Direction = ParameterDirection.Output;
cmd.Parameters["sk"].ArrayLength = 100;

It seems that NULL values are converted to 0 values.

How can I solve this without having to change the type to for instance varchar?

Best Regards,

Marc

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 17 Apr 2009 06:46

Back to the times when there were no Nullable types, it was considered that OracleParameter.Value has to return int[] for OracleDbType.Integer. That's why you don't see the null value. You can check if the value is null with the help of the OracleParameter[int index] property. In the next version of dotConnect for Oracle we will implement the behaviour when the type of array element is defined by the type of element that will be assigned to OracleParameter.Value before the command execution.

Post Reply