Is supported by Oracle.ManagedDataAccess.Core executing a stored procedure? — oracle-tech

    Forum Stats

  • 3,716,133 Users
  • 2,242,960 Discussions
  • 7,845,840 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

user13478299
user13478299 Member Posts: 2
edited March 2020 in ODP.NET

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!

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited March 2020
    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
    user13478299 Member Posts: 2
    edited March 2020

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited March 2020

    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.

Sign In or Register to comment.