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.
Can we pass null values to array parameter of stored proc
I have tried the following code. It works with dotConnect for Oracle. Is that what you mean?
script
C# code
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;
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;
}