Can we pass null values to array parameter of stored proc

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mkashyap
Posts: 11
Joined: Mon 09 Nov 2009 21:26

Can we pass null values to array parameter of stored proc

Post by mkashyap » Fri 19 Mar 2010 21:40

Hi,

I have a stored procedure that accepts a string array as an input parameter. Will I be able to pass a null value or an empty array to the parameter?
I found that passing empty array or null value do not work with ODP. I wanted to know if Devart can handle this or is it a function of the Oracle database itself that it does not accept such values for an array type parameter?

Thanks.

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

Post by Shalex » Mon 22 Mar 2010 14:28

I have tried the following code. It works with dotConnect for Oracle. Is that what you mean?
script

Code: Select all

CREATE OR REPLACE TYPE EMPARRAY is VARRAY(3) OF VARCHAR2(20);
CREATE OR REPLACE PROCEDURE myprocinout(x IN EMPARRAY, y out varchar2) IS
BEGIN
y := 'hello!';
END;
C# code

Code: Select all

using (OracleConnection conn = new OracleConnection("data source=xxx;user id=xxx;password=xxx;")) {
    conn.Open();
    OracleCommand command = conn.CreateCommand();
    command.CommandText = "myprocinout";
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add("x", OracleDbType.Array).Value = null;//string.Empty;
    command.Parameters["x"].ObjectTypeName = "EMPARRAY";
    command.Parameters.Add("y", OracleDbType.VarChar).Direction = System.Data.ParameterDirection.Output;
    command.ExecuteNonQuery();
    string temp = (string)command.Parameters["y"].Value;
}

Post Reply