ODP.NET

Hello all,

I'm trying to execute a stored procedure and I have no control over it's creation. Now, I was able to do it just fine before, but now I have been informed of a new array parameter.

Here is the proc parameters:

create or replace PROCEDURE GERAR_PJ_SAP ( 
CD_CGC_P PESSOA_JURIDICA.CD_CGC%TYPE, 
DS_RAZAO_SOCIAL_P PESSOA_JURIDICA.DS_RAZAO_SOCIAL%TYPE, 
NM_FANTASIA_P PESSOA_JURIDICA.NM_FANTASIA%TYPE, 
CD_CEP_P PESSOA_JURIDICA.CD_CEP%TYPE, 
DS_ENDERECO_P PESSOA_JURIDICA.DS_ENDERECO%TYPE, 
DS_BAIRRO_P PESSOA_JURIDICA.DS_BAIRRO%TYPE, 
DS_MUNICIPIO_P PESSOA_JURIDICA.DS_MUNICIPIO%TYPE, 
SG_ESTADO_P PESSOA_JURIDICA.SG_ESTADO%TYPE, 
NM_USUARIO_P PESSOA_JURIDICA.NM_USUARIO%TYPE, 
DS_COMPLEMENTO_P PESSOA_JURIDICA.DS_COMPLEMENTO%TYPE, 
NR_DDD_TELEFONE_P PESSOA_JURIDICA.NR_DDD_TELEFONE%TYPE, 
NR_TELEFONE_P PESSOA_JURIDICA.NR_TELEFONE%TYPE, 
NR_ENDERECO_P PESSOA_JURIDICA.NR_ENDERECO%TYPE, 
DS_EMAIL_P PESSOA_JURIDICA.DS_EMAIL%TYPE, 
CD_TIPO_PESSOA_P PESSOA_JURIDICA.CD_TIPO_PESSOA%TYPE, 
IE_SITUACAO_P PESSOA_JURIDICA.IE_SITUACAO%TYPE, 
TIPO_LOGRADOURO_P VARCHAR2, 
NR_SEQ_PAIS_P VARCHAR2, 
CD_ESTABELECIMENTO_ARRAY_P TRIBUTO_ARRAY, 
CD_ISS_ARRAY_P TRIBUTO_ARRAY, 
ALIQ_ISS_ARRAY_P TRIBUTO_ARRAY, 
DT_ISS_ARRAY_P DATE_ARRAY, 
CD_PIS_ARRAY_P TRIBUTO_ARRAY, 
ALIQ_PIS_ARRAY_P TRIBUTO_ARRAY, 
DT_PIS_ARRAY_P DATE_ARRAY, 
CD_COFINS_ARRAY_P TRIBUTO_ARRAY, 
ALIQ_COFINS_ARRAY_P TRIBUTO_ARRAY, 
DT_COFINS_ARRAY_P DATE_ARRAY, 
CD_CSLL_ARRAY_P TRIBUTO_ARRAY, 
ALIQ_CSLL_ARRAY_P TRIBUTO_ARRAY, 
DT_CSLL_ARRAY_P DATE_ARRAY, 
CD_IR_ARRAY_P TRIBUTO_ARRAY, 
ALIQ_IR_ARRAY_P TRIBUTO_ARRAY, 
DT_IR_ARRAY_P DATE_ARRAY 
) 

Now, I feel like I must be passing the data wrong and asked the DBA for an exemple. He gave me this

v_CD_ESTABELECIMENTO_ARRAY  TRIBUTO_ARRAY := TRIBUTO_ARRAY(17, 15, 14);
v_CD_ISS_ARRAY              TRIBUTO_ARRAY := TRIBUTO_ARRAY(10,10,10);
v_ALIQ_ISS_ARRAY            TRIBUTO_ARRAY := TRIBUTO_ARRAY(5, 7, 10);
v_DT_ISS_ARRAY              DATE_ARRAY := DATE_ARRAY(TO_DATE('2024-01-01', 'YYYY-MM-DD'), TO_DATE('2024-02-01', 'YYYY-MM-DD'), TO_DATE('2024-03-01', 'YYYY-MM-DD'));

I'll also post a snippet of my code.

var type = typeof(T);
using (OracleConnection conn = this.GetConnectionTasy())
{
    using (OracleCommand cmd = new OracleCommand(pProcName, conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (var prop in type.GetProperties())
        {
            if (typeof(IEnumerable).IsAssignableFrom(prop.PropertyType) && prop.PropertyType != typeof(string))
            {
                //Create the array param
            }
            if (Attribute.IsDefined(prop, typeof(ProcedureAttribute)))
            {
                cmd.Parameters.Add(prop.GetCustomAttribute<ProcedureAttribute>().Parameter,
                    prop.GetValue(pObject) == null ? (object)DBNull.Value : prop.GetValue(pObject));
            }
        }
        result = cmd.ExecuteNonQuery();
    }
}

ProcedureAttribute is a custom attribute that stores the name of the parameter. Now, I've looked everywhere in the internet and tried a lot of approaches to get this working and still can't figure it out. It must be the way I'm creating the array parameters that causes the error. Can you guys help me out here? How should I pass the data?

Thanks and sorry for grammar, not a native speaker.