NRE when executing stored procedure with array type param

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

NRE when executing stored procedure with array type param

Post by peledkfir » Mon 26 Dec 2011 17:57

When I execute storedprocedure, NRE is thrown from the provider after upgrading from version 6.50.228
The following code reproduces the exception:

DDL:

PROCEDURE WITH_ARRAY_PARAM ( p_array guid_array_t)
IS
BEGIN
null;
END;

TYPE guid_array_t is table of raw(16)

configuration connection string:


code:

Code: Select all

 var conn = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["conn"].ProviderName).CreateConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            using (conn)
            {
                conn.Open();
                using (IDbCommand command = conn.CreateCommand())
                {
                    command.CommandText = "WITH_ARRAY_PARAM";
                    command.CommandType = CommandType.StoredProcedure;
                    var param = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["conn"].ProviderName).CreateParameter();
                    param.ParameterName = "p_array";
                    IList paramVal = new OracleArray(OracleType.GetObjectType("guid_array_t", (OracleConnection)conn));
                    param.Value = paramVal;
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery(); // NRE
                }
            }
Regards,
Kfir

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Thu 29 Dec 2011 14:07

We have reproduced the issue. We will investigate it and notify you as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Wed 04 Jan 2012 14:32

We have fixed the bug with NullReferenceException when executing a query with OracleObject, OracleArray, OracleTable parameters with Null value. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Fri 13 Jan 2012 08:30

The new build of dotConnect for Oracle 6.60.283 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23129

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » Wed 08 Feb 2012 15:55

Hi,
I have problem with the fix when sending empty arrays
Now, I get ORA-06531: Reference to uninitialized collection

I iterate over the array in the stored procedure as follows

FORALL i IN p_array.FIRST. . p_array.LAST
INSERT INTO TMP VALUES(p_array(i));

This problem never occured to me before with dotconnect
Plz help
I'm using version 6.70
Thanks, kfir

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Post by Pinturiccio » Fri 10 Feb 2012 13:12

There are two ways to solve this issue:
1. To perform a check for the null value in the procedure:

Code: Select all

CREATE OR REPLACE PROCEDURE SCOTT.WITH_ARRAY_PARAM ( p_array guid_array_t)
IS
BEGIN
  IF p_array IS NOT NULL THEN 
FORALL i IN p_array.FIRST .. p_array.LAST
INSERT INTO BYTERAW VALUES(p_array(i));
    END IF;
END;
2. Make some change in your C# code. Replace IList with OracleArray:

Code: Select all

IList paramVal = new OracleArray(OracleType.GetObjectType("guid_array_t", (OracleConnection)conn));
->
OracleArray paramVal = new OracleArray(OracleType.GetObjectType("guid_array_t", (OracleConnection)conn));
And add the following row to your code:

Code: Select all

paramVal.IsNull = false;

peledkfir
Posts: 22
Joined: Sat 15 Jan 2011 11:06

Post by peledkfir » Thu 16 Feb 2012 21:48

It works, 10x

Post Reply