Is supported by Oracle.ManagedDataAccess.Core executing a stored procedure?

user13478299

I am having problems using an oracle database stored procedure, with the Oracle.ManagedDataAccess.Core oracle drive.

Is supported by Oracle.ManagedDataAccess.Core executing a stored procedure?

I can't find examples that work.

Thanks if you can help me, thanks!

AndrewSayer

user13478299 wrote:I am having problems using an oracle database stored procedure, with the Oracle.ManagedDataAccess.Core oracle drive.Is supported by Oracle.ManagedDataAccess.Core executing a stored procedure?I can't find examples that work.Thanks if you can help me, thanks!

Yes, this is a very normal thing to do. You would use the ExecuteNonQuery method of the OracleCommand class with statement like “call myProc()”
What have you tried? it’s likely a small error.

user13478299

Hi AndrewSayer.

the problem is that the stored procedure is a query, it returns records.

I did it this way, I just didn't have any return of records (which I should have), and no errors at runtime:

using (OracleCommand cmd = new OracleCommand("COB_INTE_CERTIDAO_PKG.CONSULTAR", con))

            {

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                OracleParameter oparam = cmd.Parameters.Add("PCODORGAO", OracleDbType.Int16);

                oparam.Direction = System.Data.ParameterDirection.Input;

                oparam.Value = ent.CodOrgao;

                OracleParameter oparam2 = cmd.Parameters.Add("PDESCSERVVARA", OracleDbType.Varchar2);

                oparam2.Direction = System.Data.ParameterDirection.Input;

                oparam2.Value = ent.DescServVara;

                OracleParameter oparam3 = cmd.Parameters.Add("PPRAZOCERTIDAO", OracleDbType.Int64);

                oparam3.Direction = System.Data.ParameterDirection.Input;

                oparam3.Value = ent.PrazoCertidaoProcesso;

                OracleParameter oparam4 = cmd.Parameters.Add("PNUMCERTIDAO", OracleDbType.Varchar2);

                oparam4.Direction = System.Data.ParameterDirection.Input;

                oparam4.Value = ent.NumCertidao;

                OracleParameter oparam5 = cmd.Parameters.Add("PNUMCERTANTVINCULADA", OracleDbType.Varchar2);

                oparam5.Direction = System.Data.ParameterDirection.Input;

                oparam5.Value = ent.NumCertidaoAntVinculada;

                OracleParameter oparam6 = cmd.Parameters.Add("PNUMPROCESSOJUD", OracleDbType.Varchar2);

                oparam6.Direction = System.Data.ParameterDirection.Input;

                oparam6.Value = ent.NumProcessoJud;

                OracleParameter oparam7 = cmd.Parameters.Add("PMODELOCERTIDAO", OracleDbType.Int64);

                oparam7.Direction = System.Data.ParameterDirection.Input;

                oparam7.Value = ent.CodModeloCertidao;

                OracleParameter oparam8 = cmd.Parameters.Add("PFLSCERTIDAO", OracleDbType.Varchar2);

                oparam8.Direction = System.Data.ParameterDirection.Input;

                oparam8.Value = ent.FlsDaCertidao;

                OracleParameter oparam9 = cmd.Parameters.Add("PCODESTCERTIDAO", OracleDbType.Int64);

                oparam9.Direction = System.Data.ParameterDirection.Input;

                oparam9.Value = ent.CodEstagioCertidao;

                OracleParameter oparam10 = cmd.Parameters.Add("PDATACANC", OracleDbType.Date);

                oparam10.Direction = System.Data.ParameterDirection.Input;

                oparam10.Value = ent.DataCancelamento;

                OracleParameter oparam11 = cmd.Parameters.Add("PMOTIVOCANC", OracleDbType.Varchar2);

                oparam11.Direction = System.Data.ParameterDirection.Input;

                oparam11.Value = ent.MotivoCancelamento;

                OracleParameter oparam12 = cmd.Parameters.Add("PNOFICIO", OracleDbType.Varchar2);

                oparam12.Direction = System.Data.ParameterDirection.Input;

                oparam12.Value = ent.NOficio;

                OracleParameter oparam13 = cmd.Parameters.Add("PCODUSRCANC", OracleDbType.Varchar2);

                oparam13.Direction = System.Data.ParameterDirection.Input;

                oparam13.Value = ent.CodUsrCancelamento;

                OracleParameter oparam14 = cmd.Parameters.Add("PNOMEAUTOR", OracleDbType.Varchar2);

                oparam14.Direction = System.Data.ParameterDirection.Input;

                oparam14.Value = ent.NomeAutor;

                OracleParameter oparam15 = cmd.Parameters.Add("PNOMEREU", OracleDbType.Varchar2);

                oparam15.Direction = System.Data.ParameterDirection.Input;

                oparam15.Value = ent.NomeReu;

                OracleParameter oparam16 = cmd.Parameters.Add("PACAO", OracleDbType.Varchar2);

                oparam16.Direction = System.Data.ParameterDirection.Input;

                oparam16.Value = ent.Acao;

                OracleParameter oparam17 = cmd.Parameters.Add("PDATAENVIO", OracleDbType.Date);

                oparam17.Direction = System.Data.ParameterDirection.Input;

                oparam17.Value = ent.DataEnvio;

                OracleParameter oparam18 = cmd.Parameters.Add("PCODUSRENVIO", OracleDbType.Varchar2);

                oparam18.Direction = System.Data.ParameterDirection.Input;

                oparam18.Value = ent.CodUsrEnvio;

                OracleParameter oparam19 = cmd.Parameters.Add("PMATRUSRENVIO", OracleDbType.Varchar2);

                oparam19.Direction = System.Data.ParameterDirection.Input;

                oparam19.Value = ent.MatrUsrEnvio;

                OracleParameter oparam20 = cmd.Parameters.Add("PDATAIMPORTACAO", OracleDbType.Date);

                oparam20.Direction = System.Data.ParameterDirection.Input;

                oparam20.Value = ent.DataImportacao;

                OracleParameter oparam21 = cmd.Parameters.Add("PCODUSRIMPORTACAO", OracleDbType.Varchar2);

                oparam21.Direction = System.Data.ParameterDirection.Input;

                oparam21.Value = ent.CodUsrImportacao;

                OracleParameter oparam22 = cmd.Parameters.Add("PDATARETIF", OracleDbType.Date);

                oparam22.Direction = System.Data.ParameterDirection.Input;

                oparam22.Value = ent.DataRetificacao;

                OracleParameter oparam23 = cmd.Parameters.Add("PDATAULTRETIF", OracleDbType.Date);

                oparam23.Direction = System.Data.ParameterDirection.Input;

                oparam23.Value = ent.DataUltRetificacao;

                OracleParameter oparam24 = cmd.Parameters.Add("PCODUSRRETIF", OracleDbType.Varchar2);

                oparam24.Direction = System.Data.ParameterDirection.Input;

                oparam24.Value = ent.CodUsrRetificacao;

                OracleParameter oparam25 = cmd.Parameters.Add("PDATAULTVALATU", OracleDbType.Date);

                oparam25.Direction = System.Data.ParameterDirection.Input;

                oparam25.Value = ent.DataUltValAtu;

                OracleParameter oparam26 = cmd.Parameters.Add("PDATACDM", OracleDbType.Date);

                oparam26.Direction = System.Data.ParameterDirection.Input;

                oparam26.Value = ent.DataCdm;

                OracleParameter oparam27 = cmd.Parameters.Add("PCODUSRCDM", OracleDbType.Varchar2);

                oparam27.Direction = System.Data.ParameterDirection.Input;

                oparam27.Value = ent.CodUsrCdm;

                OracleParameter oparam28 = cmd.Parameters.Add("PCODTIPOINTNEGAT", OracleDbType.Int16);

                oparam28.Direction = System.Data.ParameterDirection.Input;

                oparam28.Value = ent.CodTipoIntNegativa;

                OracleParameter oparam29 = cmd.Parameters.Add("PCODTIPPROC", OracleDbType.Int16);

                oparam29.Direction = System.Data.ParameterDirection.Input;

                oparam29.Value = ent.CodTipProc;

                OracleParameter oparam30 = cmd.Parameters.Add("C", OracleDbType.RefCursor);

                oparam30.Direction = System.Data.ParameterDirection.Output;

                OracleDataReader reader;

                try

                {

                    reader = cmd.ExecuteReader();

                                       

                    reader.Read();

Alex Keh-Oracle

One common issue is that ODP.NET binds parameters by position by default. Other .NET providers generally bind by name. You can set ODP.NET to bind by name, by setting the OracleCommand BindByName property to true.

My recommendation is to try executing a stored procedure with many fewer parameters to start with. 30 parameters are a lot. There could be a problem with one of the parameters or something else more fundamental. Once you verify the simple case is working, then it will be easier to diagnose the problem with the more complex case.