Invoke Oracle stored procedure with param input varying array from C#

Question

I have a stored procedure in Oracle which receives an input parameter of type varchar2 varying array. The procedure works and if you invoke it from SQL, what I need is called from C#.

My script is this:

CREATE OR REPLACE PROCEDURE INTEGRATOR.PRC_TEST_PARAM_ARRAY  (p_nros_moviles integrator.NROMOVIL_ARRAY) IS
BEGIN
  FOR i IN 1..p_nros_moviles.count LOOP
    IF p_nros_moviles(i) IS NOT NULL THEN                               
      INSERT INTO INTEGRATOR.TEST_PARAM_ARRAY VALUES (p_nros_moviles(i));
    END IF;
  END LOOP;
END;
/

My user type:

CREATE OR REPLACE TYPE INTEGRATOR.NROMOVIL_ARRAY AS
    VARYING ARRAY(100) OF VARCHAR2(15);
/

My invoke from PLSQL

DECLARE
  v_array integrator.NROMOVIL_ARRAY;
BEGIN
  v_array := integrator.NROMOVIL_ARRAY('9999999', '66666666');

  integrator.prc_test_param_array(v_array);

END;

And I try this way from c#

try
{
    using (OracleConnection connection = new OracleConnection())
    {
        connection.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=10.10.10.10)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)" +
                                      "(SID=PORTANODE)));User Id=user;Password=*****;";

        using (OracleCommand cmd = new OracleCommand("INTEGRATOR.PRC_TEST_PARAM_ARRAY", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter p = new OracleParameter();
            p.ParameterName = "P_NROS_MOVILES";
            p.OracleDbType = OracleDbType.Array;
            p.Direction = ParameterDirection.Input;

            p.UdtTypeName = "INTEGRATOR.NROMOVIL_ARRAY";
            //p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            p.Value = new string[] { "XXXX", "YYYY" };
            cmd.Parameters.Add(p);


            connection.Open();

            cmd.ExecuteNonQuery();

            MessageBox.Show("Ejecutado");
        }
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Someone could guide me I need to change to make it work

Answer 1

Be patient, Wait and wait.. it takes hell of a long time .. that is my experience

Answer 2

I'm not sure but I think that System.Data.OracleClient doesn't really support user defined arrays.

I'd try to write a helping stored function, which takes for example a comma separated string (these will be the values of your varray type), and splits it to values using WHILE LOOP and SUBSTR. Then in each iteration it adds the actual VARCHAR2 to a temporary integrator.NROMOVIL_ARRAY type variable using the EXTEND(1) to make place for the new value.

In the end the function returns the temporary integrator.NROMOVIL_ARRAY, and this value could be used in the stored procedure.