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

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!
The life is beautiful!
0
nqpham
9/28/2004 3:58:00 AM
asp.net.oracle 2269 articles. 0 followers. Follow

4 Replies
2046 Views

Similar Articles

[PageSpeed] 40

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.
--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
9/28/2004 11:46:05 AM
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!

The life is beautiful!
0
nqpham
9/28/2004 1:41:42 PM
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();

}
}
0
greg
9/28/2004 1:49:14 PM
Thank you very much! 

I had changed and I was successful.
Thank again.
Good luck to you!
The life is beautiful!
0
nqpham
9/28/2004 6:13:26 PM
Reply:

Web resources about - How call a Oracle Function using Microsoft .Net Oracle Client (System.Data.OracleClient)? - asp.net.oracle

Resources last updated: 11/24/2015 9:32:47 PM