How call a Oracle Function using Microsoft .Net Oracle Client (System.Data.OracleClient)? RRS feed

  • Question

  • User-1215548214 posted
    I try to call Oracle Function in .net C# by using System.Data.OracleClient. But i am not successful. Here is my code FUNCTION TTHAO_MONTT_EXISTS ( vten varchar2) RETURN boolean IS ret boolean; montt_id integer; BEGIN ret:=false; montt_id:= -1; select monthethao_id into montt_id from TTHAO_MONTHETHAO where ten = vten; if montt_id!=-1 then ret:=true; end if; return ret; Exception when others then return false; END; I do not know, when I call function TTHAO_MONTT_EXISTS, do I must declare all two param "ret" and "montt_id"? And how I declare it in .net? Please help me!
    Monday, September 27, 2004 11:58 PM

All replies

  • User-144800190 posted
    Not sure if you can call an Oracle function directly from ASP.NET. You might have to put you function in a Package and call the function from a stored procedure. You should be able to call your function from a SQL query. You might want to post your calling code for more help.
    Tuesday, September 28, 2004 7:46 AM
  • User-1215548214 posted
    For example I have a very simple table person(id (number), name (varchar)) and my oracle function simple return true or false if have or not have a persion with name is specified in vname parameter. My oracle function is FUNCTION PERSON_EXISTS ( vname varchar2) RETURN boolean IS ret boolean; rid integer; BEGIN ret:=false; rid:= -1; select id into rid from PERSON where name=vname; if rid!=-1 then ret:=true; end if; return ret; Exception when others then return false; END; and here is my .NET CODE OracleConnection oracnn = new OracleConnection(ConfigurationSettings.AppSettings ["ConnectionString"]); oracnn.Open(); OracleCommand oracmd = new OracleCommand(); oracmd.CommandText="PERSION_EXISTS"; oracmd.CommandType = CommandType.StoredProcedure; oracmd.Connection = oracnn; OracleParameter retval = new OracleParameter("ret",OracleType.VarChar,2); retval.Direction = ParameterDirection.ReturnValue; oracmd.Parameters.Add(retval); OracleParameter id = new OracleParameter("rid",OracleType.Int32); id.Direction = ParameterDirection.ReturnValue; oracmd.Parameters.Add(id); oracmd.Parameters.Add(new OracleParameter("vname",OracleType.VarChar)); oracmd.Parameters["vname"].Value=ten; oracmd.ExecuteNonQuery(); I used function oracle with one parameter in "return datatype is (parameter datatype) begin " and I was successful, but when I use more than one, for example in my function I have two "ret" and "rid" parameters. And the code does not run well. Please tell me why? I do not want to use store procedure with ref cursor to replace for function. Thank you for support. Good luck to you!
    Tuesday, September 28, 2004 9:41 AM
  • User1349123926 posted
    Hi, You can call standalone functions, but you wont be able to call one that has BOOLEAN datatype, as boolean is specific to pl/sql and the underlying Oracle client doesnt support it. You may want to change it to number and use 1 and 0. Here's an example of calling a function using Oracle's Data provider, you should be able to convert it to MS ODP without much trouble. Hope it helps, Greg /* create or replace function myfunc( myInVarchar in varchar2, myInNumber in number, myInOutVarchar in out varchar2) return varchar2 is retval varchar2(50); begin retval := myInVarchar || myInNumber || myInOutVarchar; myInOutVarchar := myInVarchar || ' from the stored func'; return retval; end; / */ using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; public class odpparams { public static void Main() { OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl"); con.Open(); OracleCommand cmd = new OracleCommand(); cmd.CommandText = "myfunc"; cmd.CommandType=CommandType.StoredProcedure; cmd.Connection = con; OracleParameter retval = new OracleParameter("myretval",OracleDbType.Varchar2,50); retval.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retval); cmd.Parameters.Add(new OracleParameter("myfirstparam",OracleDbType.Varchar2,50)).Value="MyValue"; cmd.Parameters.Add(new OracleParameter("mysecondparam",OracleDbType.Decimal)).Value=1; OracleParameter inoutval = new OracleParameter("inoutval",OracleDbType.Varchar2,50); inoutval.Direction = ParameterDirection.InputOutput; inoutval.Value = "Hello"; cmd.Parameters.Add(inoutval); cmd.ExecuteNonQuery(); Console.WriteLine("Return value is {0}",retval.Value); Console.WriteLine("InOut value is {0}",inoutval.Value); con.Close(); } }
    Tuesday, September 28, 2004 9:49 AM
  • User-1215548214 posted
    Thank you very much! I had changed and I was successful. Thank again. Good luck to you!
    Tuesday, September 28, 2004 2:13 PM
  • User1580694047 posted





    Wednesday, July 13, 2011 5:45 AM