How to call an Oracle function with C# (Asp.Net)

Question

Hi,
I need your help how to call a Oracle function with C# (Asp.Net). Oracle function requires input parameters. Return value will be of type Number.

Any kind of advice/help are very much appreciated.

Thanks in advance!

jesbon · Answer

Hi, thanks so far. I have not get it to work yet because it will not pass my compilation, I got the following error message for all parameters: 'The name 'OracleDbType' does not exist in the current context' I guess because I have not imported using statement 'using Oracle.DataAccess.Client'. I can't find it and import that reference, where can I find it? Question no 2 - how do I save the ansver as a parameter (int)? Is it as simple as: int answer = cmd.Parameters["dv"].Value;

Roei Bar · Answer

here is a sample: using Oracle.DataAccess.Client; Then, we create a connection object of type OracleConnection, open the connection, and declare an OracleCommand object using the stored procedure name as an input argument. One of the properties we want to set in the OracleCommand object cmd is the CommandType which will be CommandType.StoredProcedure. OracleConnection conn = new OracleConnection( "Persist Security Info=False;User ID=SCOTT;Password=TIGER;Data Source=MYSERVER;"); conn.Open(); OracleCommand cmd = new OracleCommand("ASSIGNDV",conn); cmd.CommandType = CommandType.StoredProcedure; Now we want to declare the input and output parameters to and from the stored procedure. We use a class of type OracleParameter to do this. The arguments to the constructor for OracleParameter are the parameter name and the Oracle database type (OracleDbType). As a property to our parameter objects, we give the direction of input or output (ParameterDirection.Input, ParameterDirection.Output). Finally, we execute the stored procedure through the ExecuteNonQuery method on the cmd object and close the connection. The return value from the stored procedure can be found in the "dv" parameter of the cmd object prm3 if all is successful. OracleParameter prm1 = new OracleParameter("Code1",OracleDbType.Varchar2); prm1.Direction = ParameterDirection.Input; prm1.Value = sCode1; cmd.Parameters.Add(prm1); OracleParameter prm2 = new OracleParameter("Code2",OracleDbType.Varchar2); prm2.Direction = ParameterDirection.Input; prm2.Value = sCode2; cmd.Parameters.Add(prm2); OracleParameter prm3 = new OracleParameter("dv",OracleDbType.Varchar2,10); prm3.Direction = ParameterDirection.Output; cmd.Parameters.Add(prm3); cmd.ExecuteNonQuery(); conn.Close(); Console.WriteLine("Division is: " + cmd.Parameters["dv"].Value);

How to call an Oracle function with C# (Asp.Net)

Answers (2)