- 3,716,133 Users
- 2,242,960 Discussions
- 7,845,840 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 479 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 5 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 417 SQLcl
- 42 SQL Developer Data Modeler
- 184.9K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 9 DevOps
- 3K QA/Testing
- 259 Java
- 6 Java Learning Subscription
- 11 Database Connectivity
- 67 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 31 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 11 Español
- 1.9K Japanese
- 2 Portuguese
Is supported by Oracle.ManagedDataAccess.Core executing a stored procedure?

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
-
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. -
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();
-
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.